Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
2.44%
1 / 41
50.00%
1 / 2
 CRAP
0.00%
0 / 1
KPIBigQuerySync
2.44%
1 / 41
50.00%
1 / 2
 52.50
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 handle
0.00%
0 / 40
0.00%
0 / 1
 42
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblCompanies;
6use App\Models\TblFinalSummary;
7use App\Services\BigQueryService;
8use Illuminate\Console\Command;
9use Illuminate\Support\Facades\Log;
10
11class KPIBigQuerySync extends Command
12{
13    /**
14     * The name and signature of the console command.
15     *
16     * @var string
17     */
18    protected $signature = 'sync:kpi-big-query';
19
20    /**
21     * The console command description.
22     *
23     * @var string
24     */
25    protected $description = 'Command description';
26
27    /**
28     * Create a new command instance.
29     *
30     * @return void
31     */
32    public function __construct()
33    {
34        parent::__construct();
35    }
36
37    /**
38     * Execute the console command.
39     *
40     * @return int
41     */
42    public function handle()
43    {
44        try {
45
46            $bigQuery = new BigQueryService;
47
48            $query = "SELECT
49                        SPLIT(ad_group_name, '_')[OFFSET(1)] AS regions,
50                        SUM(metrics_clicks) clicks,
51                        SUM(metrics_impressions) impressions,
52                        SUM(metrics_cost_micros) / 1000000 AS investments,
53                        SUM(metrics_conversions) AS conversions,
54                        SAFE_DIVIDE(SUM(metrics_clicks), SUM(metrics_impressions)) * 100 AS ctr
55                    FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
56                    GROUP BY SPLIT(ad_group_name, '_')[OFFSET(1)]
57                    ORDER BY SPLIT(ad_group_name, '_')[OFFSET(1)]";
58
59            $result = $bigQuery->query($query);
60            $totalResult = count($result);
61
62            // FIRE-1151: pre-fetch all companies once, grouped by region.
63            // Pre-fix this loop ran TblCompanies::where('region', ...)->pluck()
64            // on every iteration — one SELECT per BigQuery row. Now it's one
65            // SELECT total, then the in-loop lookup is an array access.
66            $companyIdsByRegion = TblCompanies::query()
67                ->select(['company_id', 'region'])
68                ->get()
69                ->groupBy('region')
70                ->map(fn ($rows) => $rows->pluck('company_id')->all());
71
72            for ($i = 0; $i < $totalResult; $i++) {
73
74                $item = $result[$i];
75                $region = $item['regions'];
76
77                if (strpos($item['regions'], 'Barcelona') !== false) {
78                    $region = 'Cataluña';
79                }
80
81                if (strpos($item['regions'], 'Valencia') !== false) {
82                    $region = 'Comunidad Valenciana';
83                }
84
85                TblCompanies::where('region', $region)->update(
86                    [
87                        'total_investment' => $item['investments'],
88                    ]
89                );
90
91                $companyIds = $companyIdsByRegion[$region] ?? [];
92                if (empty($companyIds)) {
93                    continue;
94                }
95
96                TblFinalSummary::whereIn('company_id', $companyIds)->update(
97                    [
98                        'impressions_total' => $item['impressions'],
99                        'conversions' => $item['conversions'],
100                        'clicks' => $item['clicks'],
101                        'invested_p1' => $item['investments'],
102                        'invested_p2' => $item['investments'],
103                        'invested_p3' => $item['investments'],
104                        'updated_at' => date('Y-m-d H:i:s'),
105                        'updated_by' => 'System',
106                    ]
107                );
108            }
109
110        } catch (\Exception $e) {
111            Log::channel('cron_big_query_sync')->error($e->getMessage());
112        }
113
114        return 0;
115    }
116}