<?php

namespace App\Http\Controllers\Owner\Analytic;

use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Http\Helpers\ResponseBuilder;
use App\Modules\Shared\Validator\Infraestructure\LaravelDTOGenerator;
use App\Http\Controllers\Owner\Analytic\Dto\AnalyticSaleListDayDTO;

USE App\Modules\Analytics\Sales\Application\Reports\AnalyticSaleReport;


class AnalyticSaleListDayController extends Controller
{
	private $oDomain;


	public function __construct
	(
		private LaravelDTOGenerator $oLaravelDTOGenerator
	)
	{
        $this->oDomain = "Analytic Sale List Day";
    }


	public function __invoke(Request $request)
    {
		//------------------------------------------------------------------------------
		//	VALIDATE
		//------------------------------------------------------------------------------
		if (is_string($request->Id)) {
			$request->merge([
				'Id' => json_decode($request->Id, true)
			]);
		}
		$oDTO = $this->oLaravelDTOGenerator->generate($request->all(), $this->oDomain, AnalyticSaleListDayDTO::class);


		//------------------------------------------------------------------------------
		//	VARIABLES
		//------------------------------------------------------------------------------
		$pDate_Start		= $oDTO->DateStart;
		$pDate_End			= $oDTO->DateEnd;
		$pDate_Start_YoY 	= date('Y-m-d', strtotime('-1 year', strtotime($pDate_Start)));
		$pDate_End_YoY		= date('Y-m-d', strtotime('-1 year', strtotime($pDate_End)));

		$pFilter		= $oDTO->Filter;	// DATE-BOOKING - DATE-TOUR
		$pGroup			= $oDTO->Group;		// NONE - CHANNEL - SELLER
		$pView			= $oDTO->View;		// RANKING - STACKED - SPECIFIC
		$pId			= $oDTO->Id;		// Array of IDS
		$pExport		= $oDTO->Export;


		//------------------------------------------------------------------------------
		//	FUNCTION
		//------------------------------------------------------------------------------

		if ($pGroup == "NONE")
		{
			$oBaseQuery		= $this->GetQueryBase($pDate_Start_YoY, $pDate_End, $pFilter);

			$oSubQuery 		= DB::query();

			$oSubQuery->fromSub($oBaseQuery, "b");
			$oSubQuery->selectRaw("
				Year,
				Month,
				Day,
				Date,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");
			$oSubQuery->groupBy("Year");
			$oSubQuery->groupBy("Month");
			$oSubQuery->groupBy("Day");


			$oQuery = DB::query();

			$oQuery->fromSub($oSubQuery, "sub");
			$oQuery->leftJoinSub(
				$oSubQuery,
				"pre",
				function ($oJoinQuery) {
					$oJoinQuery->on("pre.Day", "=", "sub.Day");
					$oJoinQuery->on("pre.Month", "=", "sub.Month");
					$oJoinQuery->on("pre.Year", "=", DB::raw("sub.Year - 1"));
				}
			);
			$oQuery->selectRaw("
				fn_day_name_get(sub.Date, 'SHORT') AS Label,
				fn_day_name_get(sub.Date, 'SHORT') AS Title,
				sub.Year,
				sub.Month,
				sub.Day,
				sub.Total_Sale,
				sub.Total_Booking,
				sub.Total_Passenger,
				CASE
					WHEN IFNULL(LAG(sub.Total_Sale) OVER (PARTITION BY Year ORDER BY Month), 0) = 0
					THEN 0
					ELSE
						(sub.Total_Sale - LAG(sub.Total_Sale) OVER (PARTITION BY Year ORDER BY Month) ) / LAG(sub.Total_Sale) OVER (PARTITION BY Year ORDER BY Month )
				END AS MoM_Sale,
				CASE
					WHEN IFNULL(LAG(sub.Total_Booking) OVER (PARTITION BY Year ORDER BY Month), 0) = 0
					THEN 0
					ELSE
						(sub.Total_Booking - LAG(sub.Total_Booking) OVER (PARTITION BY Year ORDER BY Month) ) / LAG(sub.Total_Booking) OVER (PARTITION BY Year ORDER BY Month )
				END AS MoM_Booking,
				CASE
					WHEN IFNULL(LAG(sub.Total_Passenger) OVER (PARTITION BY Year ORDER BY Month), 0) = 0
					THEN 0
					ELSE
						(sub.Total_Passenger - LAG(sub.Total_Passenger) OVER (PARTITION BY Year ORDER BY Month) ) / LAG(sub.Total_Passenger) OVER (PARTITION BY Year ORDER BY Month )
				END AS MoM_Passenger,

				IFNULL(pre.Total_Sale, 0) AS Previous_Total_Sale,
				IFNULL(pre.Total_Booking, 0) AS Previous_Total_Booking,
				IFNULL(pre.Total_Passenger, 0) AS Previous_Total_Passenger,

				CASE
					WHEN IFNULL(pre.Total_Sale, 0) = 0
					THEN 0
					ELSE (sub.Total_Sale - pre.Total_Sale) / pre.Total_Sale
				END AS YoY_Sale,
				CASE
					WHEN IFNULL(pre.Total_Booking, 0) = 0
					THEN 0
					ELSE (sub.Total_Booking - pre.Total_Booking) / pre.Total_Booking
				END AS YoY_Booking,
				CASE
					WHEN IFNULL(pre.Total_Passenger, 0) = 0
					THEN 0
					ELSE (sub.Total_Passenger - pre.Total_Passenger) / pre.Total_Passenger
				END AS YoY_Passenger
			");
			$oQuery->whereBetween(
				DB::raw("STR_TO_DATE(CONCAT(sub.Year,'-',sub.Month,'-',sub.Day), '%Y-%m-%d')"),
				[$pDate_Start, $pDate_End]
			);
			$oQuery->orderBy("Year", "ASC");
			$oQuery->orderBy("Month", "ASC");
			$oQuery->orderBy("Day", "ASC");

			$oData = $oQuery->get();

		}
		else if ($pGroup == "CHANNEL")
		{
			$oCurrentQuery 	= DB::query();
			$oCurrentQuery->fromSub($this->GetQueryBase($pDate_Start, $pDate_End, $pFilter), "b");
			$oCurrentQuery->selectRaw("
				Booking_Type,
				fn_booking_type_name_get(Booking_Type) as Name,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			$oPreviusQuery 	= DB::query();
			$oPreviusQuery->fromSub($this->GetQueryBase($pDate_Start_YoY, $pDate_End_YoY, $pFilter), "b");
			$oPreviusQuery->selectRaw("
				Booking_Type,
				fn_booking_type_name_get(Booking_Type) as Name,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			if ($pView == "RANKING")
			{
				$oCurrentQuery->groupBy("Booking_Type");
				$oPreviusQuery->groupBy("Booking_Type");


				$oParam		= "
					cur.Booking_Type AS Id,
					cur.Name as Label,
					cur.Name AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Booking_Type",
					"=",
					"cur.Booking_Type"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy("Rank_Current");

				$oData = $oQuery->get();

			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Booking_Type", "Year", "Month", "Day");
				$oPreviusQuery->groupBy("Booking_Type", "Year", "Month", "Day");


				$oParam		= "
					cur.Booking_Type AS Id,
					cur.Name as Label,
					cur.Name AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Booking_Type",
					"=",
					"cur.Booking_Type"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Booking_Type", $pId );
				$oQuery->groupBy("cur.Booking_Type", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Booking_Type", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();

			}

		}
		else if ($pGroup == "SELLER")
		{
			$oCurrentQuery 	= DB::query();
			$oCurrentQuery->fromSub($this->GetQueryBase($pDate_Start, $pDate_End, $pFilter), "b");
			$oCurrentQuery->selectRaw("
				Id_Admin,
				Year,
				Month,
				Day,
				IFNULL( SUM( CASE WHEN Booking_Type <> 1 AND Booking_Type <> 9 THEN Amount_Invoice_Total ELSE 0 END ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT CASE WHEN Booking_Type <> 1 AND Booking_Type <> 9 THEN Id_Booking END ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT CASE WHEN Booking_Type <> 1 AND Booking_Type <> 9 THEN Id_Passenger END ), 0 ) AS Total_Passenger
			");


			$oPreviusQuery 	= DB::query();
			$oPreviusQuery->fromSub($this->GetQueryBase($pDate_Start_YoY, $pDate_End_YoY, $pFilter), "b");
			$oPreviusQuery->selectRaw("
				Id_Admin,
				Year,
				Month,
				Day,
				IFNULL( SUM( CASE WHEN Booking_Type <> 1 AND Booking_Type <> 9 THEN Amount_Invoice_Total ELSE 0 END ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT CASE WHEN Booking_Type <> 1 AND Booking_Type <> 9 THEN Id_Booking END ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT CASE WHEN Booking_Type <> 1 AND Booking_Type <> 9 THEN Id_Passenger END ), 0 ) AS Total_Passenger
			");


			if ($pView == "RANKING")
			{

				$oCurrentQuery->groupBy("Id_Admin");
				$oPreviusQuery->groupBy("Id_Admin");


				$oParam		= "
					adm.Id_Admin AS Id,
					adm.Admin_Name as Label,
					CONCAT(adm.Admin_Name, ' ', adm.Admin_LastName) AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_admin AS adm", "adm.Id_Admin", "=", "cur.Id_Admin");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Admin",
					"=",
					"cur.Id_Admin"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy('Rank_Current');

				$oData = $oQuery->get();
			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Id_Admin", "Year", "Month", "Day");
				$oPreviusQuery->groupBy("Id_Admin", "Year", "Month", "Day");


				$oParam		= "
					adm.Id_Admin AS Id,
					adm.Admin_Name as Label,
					CONCAT(adm.Admin_Name, ' ', adm.Admin_LastName) AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_admin AS adm", "adm.Id_Admin", "=", "cur.Id_Admin");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Admin",
					"=",
					"cur.Id_Admin"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Id_Admin", $pId );
				$oQuery->groupBy("cur.Id_Admin", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Id_Admin", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();
			}

		}
		else if ($pGroup == "NATIONALITY")
		{
			$oCurrentQuery 	= DB::query();
			$oCurrentQuery->fromSub($this->GetQueryBase($pDate_Start, $pDate_End, $pFilter), "b");
			$oCurrentQuery->selectRaw("
				Id_UserCountry,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			$oPreviusQuery 	= DB::query();
			$oPreviusQuery->fromSub($this->GetQueryBase($pDate_Start_YoY, $pDate_End_YoY, $pFilter), "b");
			$oPreviusQuery->selectRaw("
				Id_UserCountry,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			if ($pView == "RANKING")
			{

				$oCurrentQuery->groupBy("Id_UserCountry");
				$oPreviusQuery->groupBy("Id_UserCountry");


				$oParam		= "
					uc.Id_UserCountry AS Id,
					uc.UserCountry_Name as Label,
					uc.UserCountry_Name AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, 'cur');
				$oQuery->join("t_user_country AS uc", "uc.Id_UserCountry", "=", "cur.Id_UserCountry");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_UserCountry",
					"=",
					"cur.Id_UserCountry"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy("Rank_Current");

				$oData = $oQuery->get();
			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Id_UserCountry", "Year", "Month", "Day");
				$oPreviusQuery->groupBy("Id_UserCountry", "Year", "Month", "Day");


				$oParam		= "
					uc.Id_UserCountry AS Id,
					uc.UserCountry_Name as Label,
					uc.UserCountry_Name AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_user_country AS uc", "uc.Id_UserCountry", "=", "cur.Id_UserCountry");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_UserCountry",
					"=",
					"cur.Id_UserCountry"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Id_UserCountry", $pId );
				$oQuery->groupBy("cur.Id_UserCountry", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Id_UserCountry", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();
			}

		}
		else if ($pGroup == "CATEGORY")
		{
			$oCurrentQuery 	= DB::query();
			$oCurrentQuery->fromSub($this->GetQueryBase($pDate_Start, $pDate_End, $pFilter), "b");
			$oCurrentQuery->selectRaw("
				Id_CategoryTour,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			$oPreviusQuery 	= DB::query();
			$oPreviusQuery->fromSub($this->GetQueryBase($pDate_Start_YoY, $pDate_End_YoY, $pFilter), "b");
			$oPreviusQuery->selectRaw("
				Id_CategoryTour,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			if ($pView == "RANKING")
			{
				$oCurrentQuery->groupBy("Id_CategoryTour");
				$oPreviusQuery->groupBy("Id_CategoryTour");


				$oParam		= "
					ct.Id_CategoryTour AS Id,
					ct.CategoryTour_Name AS Label,
					ct.CategoryTour_Name AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, 'cur');
				$oQuery->join("t_category_tour AS ct", "ct.Id_CategoryTour", "=", "cur.Id_CategoryTour");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_CategoryTour",
					"=",
					"cur.Id_CategoryTour"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy('Rank_Current');

				$oData = $oQuery->get();
			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Id_CategoryTour", "Year", "Month", "Day");
				$oPreviusQuery->groupBy("Id_CategoryTour", "Year", "Month", "Day");


				$oParam		= "
					ct.Id_CategoryTour AS Id,
					ct.CategoryTour_Name AS Label,
					ct.CategoryTour_Name AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_category_tour AS ct", "ct.Id_CategoryTour", "=", "cur.Id_CategoryTour");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_CategoryTour",
					"=",
					"cur.Id_CategoryTour"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Id_CategoryTour", $pId );
				$oQuery->groupBy("cur.Id_CategoryTour", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Id_CategoryTour", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();
			}

		}
		else if ($pGroup == "TOUR")
		{
			$oCurrentQuery 	= DB::query();
			$oCurrentQuery->fromSub($this->GetQueryBase($pDate_Start, $pDate_End, $pFilter), "b");
			$oCurrentQuery->selectRaw("
				Id_Tour,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			$oPreviusQuery 	= DB::query();
			$oPreviusQuery->fromSub($this->GetQueryBase($pDate_Start_YoY, $pDate_End_YoY, $pFilter), "b");
			$oPreviusQuery->selectRaw("
				Id_Tour,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			if ($pView == "RANKING")
			{
				$oCurrentQuery->groupBy("Id_Tour");
				$oPreviusQuery->groupBy("Id_Tour");


				$oParam		= "
					tr.Id_Tour AS Id,
					tr.Tour_Code AS Label,
					CONCAT('(', tr.Tour_Code, ') ', tr.Tour_Name) AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, 'cur');
				$oQuery->join("t_tour AS tr", "tr.Id_Tour", "=", "cur.Id_Tour");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Tour",
					"=",
					"cur.Id_Tour"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy('Rank_Current');

				$oData = $oQuery->get();
			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Id_Tour", "Year", "Month", "Day");
				$oPreviusQuery->groupBy("Id_Tour", "Year", "Month", "Day");


				$oParam		= "
					tr.Id_Tour AS Id,
					tr.Tour_Code AS Label,
					CONCAT('(', tr.Tour_Code, ') ', tr.Tour_Name) AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_tour AS tr", "tr.Id_Tour", "=", "cur.Id_Tour");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Tour",
					"=",
					"cur.Id_Tour"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Id_Tour", $pId );
				$oQuery->groupBy("cur.Id_Tour", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Id_Tour", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();
			}

		}
		else if ($pGroup == "PACKAGE")
		{
			$oCurrentQuery 	= DB::query();
			$oCurrentQuery->fromSub($this->GetQueryBase($pDate_Start, $pDate_End, $pFilter), "b");
			$oCurrentQuery->selectRaw("
				Id_Package,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			$oPreviusQuery 	= DB::query();
			$oPreviusQuery->fromSub($this->GetQueryBase($pDate_Start_YoY, $pDate_End_YoY, $pFilter), "b");
			$oPreviusQuery->selectRaw("
				Id_Package,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			if ($pView == "RANKING")
			{

				$oCurrentQuery->groupBy("Id_Package");
				$oPreviusQuery->groupBy("Id_Package");


				$oParam		= "
					pk.Id_Package AS Id,
					pk.Package_Code AS Label,
					CONCAT('(', pk.Package_Code, ') ', pk.Package_Name) AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, 'cur');
				$oQuery->join("t_package AS pk", "pk.Id_Package", "=", "cur.Id_Package");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Package",
					"=",
					"cur.Id_Package"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy('Rank_Current');

				$oData = $oQuery->get();
			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Id_Package", "Year", "Month", "Day");
				$oPreviusQuery->groupBy("Id_Package", "Year", "Month", "Day");


				$oParam		= "
					pk.Id_Package AS Id,
					pk.Package_Code AS Label,
					CONCAT('(', pk.Package_Code, ') ', pk.Package_Name) AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_package AS pk", "pk.Id_Package", "=", "cur.Id_Package");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Package",
					"=",
					"cur.Id_Package"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Id_Package", $pId );
				$oQuery->groupBy("cur.Id_Package", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Id_Package", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();
			}

		}
		else if ($pGroup == "AGE")
		{
			$oCurrentQuery 	= DB::query();
			$oCurrentQuery->fromSub($this->GetQueryBaseAge($pDate_Start, $pDate_End, $pFilter), "b");
			$oCurrentQuery->selectRaw("
				CASE
					WHEN Passenger_Age < 18 THEN '00-17'
					WHEN Passenger_Age BETWEEN 18 AND 20 THEN '18-20'
					WHEN Passenger_Age BETWEEN 21 AND 25 THEN '21-25'
					WHEN Passenger_Age BETWEEN 26 AND 30 THEN '26-30'
					WHEN Passenger_Age BETWEEN 31 AND 35 THEN '31-35'
					WHEN Passenger_Age BETWEEN 36 AND 40 THEN '36-40'
					WHEN Passenger_Age BETWEEN 41 AND 45 THEN '41-45'
					WHEN Passenger_Age BETWEEN 46 AND 50 THEN '46-50'
					WHEN Passenger_Age BETWEEN 51 AND 55 THEN '51-55'
					WHEN Passenger_Age BETWEEN 56 AND 60 THEN '56-60'
					ELSE '60+'
				END AS Range_Age,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			$oPreviusQuery 	= DB::query();
			$oPreviusQuery->fromSub($this->GetQueryBaseAge($pDate_Start_YoY, $pDate_End_YoY, $pFilter), "b");
			$oPreviusQuery->selectRaw("
				CASE
					WHEN Passenger_Age < 18 THEN '00-17'
					WHEN Passenger_Age BETWEEN 18 AND 20 THEN '18-20'
					WHEN Passenger_Age BETWEEN 21 AND 25 THEN '21-25'
					WHEN Passenger_Age BETWEEN 26 AND 30 THEN '26-30'
					WHEN Passenger_Age BETWEEN 31 AND 35 THEN '31-35'
					WHEN Passenger_Age BETWEEN 36 AND 40 THEN '36-40'
					WHEN Passenger_Age BETWEEN 41 AND 45 THEN '41-45'
					WHEN Passenger_Age BETWEEN 46 AND 50 THEN '46-50'
					WHEN Passenger_Age BETWEEN 51 AND 55 THEN '51-55'
					WHEN Passenger_Age BETWEEN 56 AND 60 THEN '56-60'
					ELSE '60+'
				END AS Range_Age,
				Year,
				Month,
				Day,
				IFNULL( SUM( Amount_Invoice_Total ), 0 ) AS Total_Sale,
				IFNULL( COUNT( DISTINCT Id_Booking ), 0 ) AS Total_Booking,
				IFNULL( COUNT( DISTINCT Id_Passenger ), 0 ) AS Total_Passenger
			");


			if ($pView == "RANKING")
			{
				$oCurrentQuery->groupBy("Range_Age");
				$oPreviusQuery->groupBy("Range_Age");


				$oParam		= "
					cur.Range_Age AS Id,
					cur.Range_Age as Label,
					CONCAT('EDADES ENTRE: ', cur.Range_Age) AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Range_Age",
					"=",
					"cur.Range_Age"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy("Rank_Current");

				$oData = $oQuery->get();
			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Range_Age", "Year", "Month", "Day");
				$oPreviusQuery->groupBy("Range_Age", "Year", "Month", "Day");


				$oParam		= "
					cur.Range_Age AS Id,
					cur.Range_Age as Label,
					CONCAT('EDADES ENTRE: ', cur.Range_Age) AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Range_Age",
					"=",
					"cur.Range_Age"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Range_Age", $pId );
				$oQuery->groupBy("cur.Range_Age", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Range_Age", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();
			}

		}


		//------------------------------------------------------------------------------
		//	MAP RESULT
		//------------------------------------------------------------------------------
		if ($pGroup != "NONE" && $pView == "SPECIFIC")
		{
			$oDays 		= [];
			$pCursor 	= strtotime(date('Y-m-d', strtotime($pDate_Start)));
			$pEnd 		= strtotime(date('Y-m-d', strtotime($pDate_End)));

			while ($pCursor <= $pEnd)
			{
				$oDays[] = [
					'Year'  => (int)date('Y', $pCursor),
					'Month' => (int)date('n', $pCursor),
					'Day' 	=> (int)date('d', $pCursor)
				];

				$pCursor = strtotime('+1 day', $pCursor);
			}


			$oData = $oData
					->groupBy("Id")
					->map(function ($oRows, $oId) use ($oDays)
						{
							$oMap = [];
							foreach ($oRows as $oItem) {
								$key = $oItem->Year . '-' . $oItem->Month . '-' . $oItem->Day;
								$oMap[$key] = [
									"Total_Sale"     			=> $oItem->Total_Sale,
									"Total_Booking"  			=> $oItem->Total_Booking,
									"Total_Passenger"			=> $oItem->Total_Passenger,
									"Previous_Total_Sale"		=> $oItem->Previous_Total_Sale,
									"Previous_Total_Booking"	=> $oItem->Previous_Total_Booking,
									"Previous_Total_Passenger"	=> $oItem->Previous_Total_Passenger,
									"YoY_Sale"					=> $oItem->YoY_Sale,
									"YoY_Booking"				=> $oItem->YoY_Booking,
									"YoY_Passenger"				=> $oItem->YoY_Passenger
								];
							}

							$oDetail = [];
							foreach ($oDays as $oItem)
							{
								$key = $oItem['Year'].'-'.$oItem['Month'].'-'.$oItem['Day'];

								$oDetail[] = [
									"Year"       				=> $oItem['Year'],
									"Month"      				=> $oItem['Month'],
									"Day"      					=> $oItem['Day'],
									"Total_Sale"				=> isset($oMap[$key]) ? $oMap[$key]["Total_Sale"] : 0,
									"Total_Booking"				=> isset($oMap[$key]) ? $oMap[$key]["Total_Booking"] : 0,
									"Total_Passenger"			=> isset($oMap[$key]) ? $oMap[$key]["Total_Passenger"] : 0,
									"Previous_Total_Sale"		=> isset($oMap[$key]) ? $oMap[$key]["Previous_Total_Sale"] : 0,
									"Previous_Total_Booking"	=> isset($oMap[$key]) ? $oMap[$key]["Previous_Total_Booking"] : 0,
									"Previous_Total_Passenger"	=> isset($oMap[$key]) ? $oMap[$key]["Previous_Total_Passenger"] : 0,
									"YoY_Sale"					=> isset($oMap[$key]) ? $oMap[$key]["YoY_Sale"] : 0,
									"YoY_Booking"				=> isset($oMap[$key]) ? $oMap[$key]["YoY_Booking"] : 0,
									"YoY_Passenger"				=> isset($oMap[$key]) ? $oMap[$key]["YoY_Passenger"] : 0,
								];
							}

							$oMain = $oRows->first();

							return [
								"Id"		=> $oId,
								"Label"		=> $oMain->Label,
								"Title"		=> $oMain->Title,
								"Detail"	=> $oDetail
							];
					})
					->sortByDesc(function ($item) {
						return collect($item['Detail'])->sum(function ($d) {
							return (float)$d['Total_Sale'];
						});
					})
					->values();
		}


		//------------------------------------------------------------------------------
		//	RESPONSE
		//------------------------------------------------------------------------------
		if ( $pExport == false )
		{
			$oResponse["Response_Status"]           = 200;
			$oResponse["Response_Code"]             = 200;
			$oResponse["Response_Domain"]           = $this->oDomain;
			$oResponse["Response_Message"]          = $this->oDomain . " List";
			$oResponse["Response_Data"]             = $oData;
			$oResponse["Response_Error_Message"]    = "";
			$oResponse["Response_Error_Reason"]     = "";

			return ResponseBuilder::Response($oResponse);
		}
		else
		{
			if ($pGroup == "NONE")
			{
				AnalyticSaleReport::Report_Detailed($oData->toArray());
			}
			else
			{
				if ($pView == "RANKING")
				{
					AnalyticSaleReport::Report_Detailed_Ranking($oData->toArray());
				}
				else
				{
					AnalyticSaleReport::Report_Detailed_Specific($oData->toArray());
				}
			}
		}

    }




	private function GetQueryBase($DateStart, $DateEnd, $Filter)
	{
		$oQuery = DB::query();

		if ($Filter == "DATE-BOOKING")
		{
			$oQuery->from("v_booking_sales");
			$oQuery->selectRaw("
				Id_Invoice,
				Id_Booking,
				Id_Passenger,
				Id_Admin,
				Id_Tour,
				Id_CategoryTour,
				Id_Package,
				Booking_Type,
				Id_UserCountry,
				Booking_Confirm_Date AS Date,
				Booking_Confirm_Year AS Year,
				Booking_Confirm_Month AS Month,
				Booking_Confirm_Day AS Day,
				MAX(Invoice_Total) AS Amount_Invoice_Total,
				MAX(Invoice_TotalPay) AS Amount_Invoice_Paid,
				MAX(Invoice_TotalPending) AS Amount_Invoice_Pending
			");
			$oQuery->whereBetween('Booking_Confirm_Date', [$DateStart, $DateEnd]);
			$oQuery->groupBy("Id_Invoice");
			$oQuery->groupBy("Booking_Confirm_Year");
			$oQuery->groupBy("Booking_Confirm_Month");
		}
		else if($Filter == "DATE-TOUR")
		{
			$oQuery->from("v_booking_sales");
			$oQuery->selectRaw("
				Id_Invoice,
				Id_Booking,
				Id_Passenger,
				Id_Admin,
				Id_Tour,
				Id_CategoryTour,
				Id_Package,
				Booking_Type,
				Id_UserCountry,
				Booking_Start_Date AS Date,
				Booking_Start_Year AS Year,
				Booking_Start_Month AS Month,
				Booking_Start_Day AS Day,
				MAX(Invoice_Total) AS Amount_Invoice_Total,
				MAX(Invoice_TotalPay) AS Amount_Invoice_Paid,
				MAX(Invoice_TotalPending) AS Amount_Invoice_Pending
			");
			$oQuery->whereBetween('Booking_Start_Date', [$DateStart, $DateEnd]);
			$oQuery->groupBy("Id_Invoice");
			$oQuery->groupBy("Booking_Start_Year");
			$oQuery->groupBy("Booking_Start_Month");
		}

		return $oQuery;
	}
	private function GetQueryBaseAge($DateStart, $DateEnd, $Filter)
	{
		$oQuery = DB::query();

		if ($Filter == "DATE-BOOKING")
		{
			$oQuery->from("v_booking_sales AS v");
			$oQuery->join("t_passenger AS p", "p.Id_Passenger", "=", "v.Id_Passenger");
			$oQuery->selectRaw("
				Id_Invoice,
				v.Id_Booking,
				v.Id_Passenger,
				TIMESTAMPDIFF( YEAR, p.Passenger_DOB, v.Booking_Confirm_Date ) AS Passenger_Age,
				Id_Admin,
				Booking_Type,
				v.Id_UserCountry,
				Booking_Confirm_Date AS Date,
				Booking_Confirm_Year AS Year,
				Booking_Confirm_Month AS Month,
				Booking_Confirm_Day AS Day,
				MAX(Invoice_Total) AS Amount_Invoice_Total,
				MAX(Invoice_TotalPay) AS Amount_Invoice_Paid,
				MAX(Invoice_TotalPending) AS Amount_Invoice_Pending
			");
			$oQuery->whereBetween('Booking_Confirm_Date', [$DateStart, $DateEnd]);
			$oQuery->groupBy("Id_Invoice");
			$oQuery->groupBy("Booking_Confirm_Year");
			$oQuery->groupBy("Booking_Confirm_Month");
		}
		else if($Filter == "DATE-TOUR")
		{
			$oQuery->from("v_booking_sales AS v");
			$oQuery->join("t_passenger AS p", "p.Id_Passenger", "=", "v.Id_Passenger");
			$oQuery->selectRaw("
				Id_Invoice,
				v.Id_Booking,
				v.Id_Passenger,
				TIMESTAMPDIFF( YEAR, p.Passenger_DOB, v.Booking_Confirm_Date ) AS Passenger_Age,
				Id_Admin,
				Booking_Type,
				v.Id_UserCountry,
				Booking_Start_Date AS Date,
				Booking_Start_Year AS Year,
				Booking_Start_Month AS Month,
				Booking_Start_Day AS Day,
				MAX(Invoice_Total) AS Amount_Invoice_Total,
				MAX(Invoice_TotalPay) AS Amount_Invoice_Paid,
				MAX(Invoice_TotalPending) AS Amount_Invoice_Pending
			");
			$oQuery->whereBetween('Booking_Start_Date', [$DateStart, $DateEnd]);
			$oQuery->groupBy("Id_Invoice");
			$oQuery->groupBy("Booking_Start_Year");
			$oQuery->groupBy("Booking_Start_Month");
		}

		return $oQuery;
	}

	private function GetSelect($Params)
	{
		$oSelect = "";

		$oSelect = $Params;
		$oSelect .= "
			cur.Total_Sale,
			cur.Total_Booking,
			cur.Total_Passenger,

			IFNULL(pre.Total_Sale, 0) AS Previous_Total_Sale,
			IFNULL(pre.Total_Booking, 0) AS Previous_Total_Booking,
			IFNULL(pre.Total_Passenger, 0) AS Previous_Total_Passenger,

			RANK() OVER (ORDER BY IFNULL(cur.Total_Sale, 0) DESC) AS Rank_Current,
			RANK() OVER (ORDER BY IFNULL(pre.Total_Sale, 0) DESC) AS Rank_Previous,
			( RANK() OVER (ORDER BY IFNULL(pre.Total_Sale, 0) DESC) - RANK() OVER (ORDER BY cur.Total_Sale DESC) ) AS Rank_Change,

			CASE
				WHEN IFNULL(pre.Total_Sale, 0) = 0
				THEN 0
				ELSE (cur.Total_Sale - pre.Total_Sale) / pre.Total_Sale
			END AS YoY_Sale,
			CASE
				WHEN IFNULL(pre.Total_Booking, 0) = 0
				THEN 0
				ELSE (cur.Total_Booking - pre.Total_Booking) / pre.Total_Booking
			END AS YoY_Booking,
			CASE
				WHEN IFNULL(pre.Total_Passenger, 0) = 0
				THEN 0
				ELSE (cur.Total_Passenger - pre.Total_Passenger) / pre.Total_Passenger
			END AS YoY_Passenger
		";

		return $oSelect;
	}

}