Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
2.78%
1 / 36
50.00%
1 / 2
 CRAP
0.00%
0 / 1
KPIBigQuerySync
2.78%
1 / 36
50.00%
1 / 2
 39.08
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 handle
0.00%
0 / 35
0.00%
0 / 1
 30
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            for ($i = 0; $i < $totalResult; $i++) {
63
64                $item = $result[$i];
65                $region = $item['regions'];
66
67                if (strpos($item['regions'], 'Barcelona') !== false) {
68                    $region = 'Cataluña';
69                }
70
71                if (strpos($item['regions'], 'Valencia') !== false) {
72                    $region = 'Comunidad Valenciana';
73                }
74
75                TblCompanies::where('region', $region)->update(
76                    [
77                        'total_investment' => $item['investments'],
78                    ]
79                );
80
81                $companyIds = TblCompanies::where('region', $region)
82                    ->pluck('company_id')
83                    ->toArray();
84
85                TblFinalSummary::whereIn('company_id', $companyIds)->update(
86                    [
87                        'impressions_total' => $item['impressions'],
88                        'conversions' => $item['conversions'],
89                        'clicks' => $item['clicks'],
90                        'invested_p1' => $item['investments'],
91                        'invested_p2' => $item['investments'],
92                        'invested_p3' => $item['investments'],
93                        'updated_at' => date('Y-m-d H:i:s'),
94                        'updated_by' => 'System',
95                    ]
96                );
97            }
98
99        } catch (\Exception $e) {
100            Log::channel('cron_big_query_sync')->error($e->getMessage());
101        }
102
103        return 0;
104    }
105}