src/Service/ClickHouse/DataManager/IncomeRawDataCHManagerService.php line 13

Open in your IDE?
  1. <?php
  3. namespace App\Service\ClickHouse\DataManager;
  5. use App\Entity\Profile\ProfileType;
  6. use App\Enums\Constants;
  7. use Symfony\Component\HttpKernel\Exception\BadRequestHttpException;
  9. class IncomeRawDataCHManagerService extends ClickHouseBaseDataManagerService
  10. {
  11.     public const NAME = 'income_raw_data';
  13.     public function getIncomeByComposition(ProfileType $profileType)
  14.     {
  15.         try {
  16.             // Get the ClickHouse connection
  17.             $db = $this->getCHConnection();
  18.             $query = "SELECT
  19.                       composition_id,
  20.                       sum(income_total_gbp_share_net) AS total_income_total_gbp_share_net,
  21.                       sum(income_se_net) AS total_income_se_net,
  22.                       sum(income_pa_net) AS total_income_pa_net,
  23.                       sum(income_pu_net) AS total_income_pu_net,
  24.                       sum(income_sw_net) AS total_income_sw_net
  25.                     FROM income_raw_data
  26.                     GROUP BY composition_id ";
  28.             switch ($profileType->getName()){
  29.                 case Constants::PROFILE_TYPE_SONGWRITER:
  30.                     $query .= "ORDER BY total_income_sw_net DESC;";
  31.                     break;
  32.                 case Constants::PROFILE_TYPE_PUBLISHER:
  33.                     $query .= "ORDER BY total_income_pu_net DESC;";
  34.                     break;
  35.                 case Constants::PROFILE_TYPE_PUBLISHER_ADMINISTRATOR:
  36.                     $query .= "ORDER BY total_income_pa_net DESC;";
  37.                     break;
  38.                 default:
  39.                     $query .= "ORDER BY total_income_total_gbp_share_net DESC;";
  40.                     break;
  41.             }
  43.             // Execute the query
  44.             $statement = $db->executeQuery($query);
  46.             // Fetch and return the data as an array
  47.             return $statement->fetchAllAssociative();
  48.         }catch (\Exception $e) {
  49.             throw new BadRequestHttpException($e->getMessage());
  50.         }
  51.     }
  52. }