AdgoalController.php 16.2 KB
Newer Older
Dominik Hebeler's avatar
Dominik Hebeler committed
1
2
3
4
5
6
7
8
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use LaravelLocalization;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;
9
use Illuminate\Support\Facades\Validator;
Dominik Hebeler's avatar
Dominik Hebeler committed
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

/**
 * Before we redirect users to the affiliate shops we will track the clicks ourself.
 * Reason is that many Affiliate shops redirect to invalid URLs (404, 500, ... Errors) which leads to bad user experience.
 * We will store the clicked Affiliate Link together with the final URL the user should land on.
 * No userdata or other metadata will be stored together with that information.
 * 
 * That way we can do manual validation of affiliate links and exclude bad partnershop links to be shown in the future. Since this will be
 * a lot of links we count the clicks so we can validate the most used ones first.
 */

class AdgoalController extends Controller
{
    # Data will be stored for 24 hours
    const STORAGE_DURATION_HOURS = 24;
    const REDIS_STORAGE_KEY = "affiliate_click";
26
    const REDIS_BLACKLIST_KEY = "affiliate_blacklist";
Dominik Hebeler's avatar
Dominik Hebeler committed
27
28
29
30
31
32
33

    /**
     * This function is called when a user clicks on a affiliate link. It will first validate that the URL
     * was generated by us to prevent random redirect links to be manually created for our domains.
     * After that we will store the necessary information (link and affiliate link) into our database.
     * After that we will redirect the user to the affiliate shop
     */
34
35
    public function forward(Request $request)
    {
Dominik Hebeler's avatar
Dominik Hebeler committed
36
37
38
39
40
41
42
43
44
45
46
47
48
49
        // $link = "https://metager.de";
        // $affillink = "https://test.de";
        // $password = self::generatePassword($affillink, $link);
        // dd(route('adgoal-redirect', ["link" => $link, "affillink" => $affillink, "password" => $password]));
        /**
         * Get Parameters (Result informations)
         * 1. affillink (With Affiliate Redirect)
         * 2. link
         * 5. Password (hmac with adgoal private key and the two parameters)
         */
        $request->validate([
            'affillink' => ['required', 'url', 'active_url'],
            'link' => ['required', 'url', 'active_url'],
            # Validation of redirect request so that one cannot generate random redirect URLs pointing to our domains
50
            'password' => function ($attribute, $value, $fail) use ($request) {
Dominik Hebeler's avatar
Dominik Hebeler committed
51
52
                // Check if hmac matches
                $correctPassword = self::generatePassword($request->input('affillink'), $request->input('link'));
53
                if (!hash_equals($correctPassword, $value)) {
Dominik Hebeler's avatar
Dominik Hebeler committed
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
                    $fail('The given password is incorrect!');
                }
            }
        ]);

        $this->storePartnerCallFast($request->input('affillink'), $request->input('link'));

        return redirect($request->input('affillink'));
    }

    /**
     * Stores Click information into Redis Cache for fast execution since this is synchronous call
     * at search time.
     * A Cronjob will pick the data up and store it into Mariadb later (see self::storePartnerCall)
     */
69
70
    private function storePartnerCallFast($affillink, $link)
    {
Dominik Hebeler's avatar
Dominik Hebeler committed
71
72
        # Generate Data to store
        $host = parse_url($link, PHP_URL_HOST);
73
        if (empty($host)) {
Dominik Hebeler's avatar
Dominik Hebeler committed
74
75
76
77
78
79
80
81
82
83
84
85
86
87
            return;
        }
        $storeObject = [
            "host" => $host,
            "affillink" => $affillink,
            "link" => $link,
        ];


        # Store Data in Redis
        $redis = Redis::connection(config('cache.stores.redis.connection'));
        $redis->rpush($this::REDIS_STORAGE_KEY, json_encode($storeObject));
    }

88
89
    public static function storePartnerCalls()
    {
Dominik Hebeler's avatar
Dominik Hebeler committed
90
        $redis = Redis::connection(config('cache.stores.redis.connection'));
91
92
        DB::transaction(function () use ($redis) {
            while (!empty($data = $redis->lpop(self::REDIS_STORAGE_KEY))) {
Dominik Hebeler's avatar
Dominik Hebeler committed
93
94
                $data = json_decode($data, true);
                # Insert data into mariadb table
95
96
97
98
                DB::insert(
                    'insert into affiliate_clicks (hostname, affillink, link) values (?, ?, ?)',
                    [$data["host"], $data["affillink"], $data["link"]]
                );
Dominik Hebeler's avatar
Dominik Hebeler committed
99
100
101
102
103
104
105
            }
        });
    }

    /**
     * Generates a Redirect URL for our partnershops
     */
106
107
    public static function generateRedirectUrl($affillink, $link)
    {
Dominik Hebeler's avatar
Dominik Hebeler committed
108
109
        $password = self::generatePassword($affillink, $link);
        return LaravelLocalization::getLocalizedURL(
110
            LaravelLocalization::getCurrentLocale(),
Dominik Hebeler's avatar
Dominik Hebeler committed
111
112
113
114
115
116
117
            route('adgoal-redirect', ["link" => $link, "affillink" => $affillink, "password" => $password])
        );
    }

    /**
     * Generates hmac password to validate redirect URLs
     */
118
119
    public static function generatePassword($affillink, $link)
    {
Dominik Hebeler's avatar
Dominik Hebeler committed
120
121
        return hash_hmac("sha256", $affillink . $link, config('metager.metager.adgoal.private_key'));
    }
122
123
124
125
126


    /**
     * Routes for the Admin Interface
     */
127
128
    public function adminIndex(Request $request)
    {
129
130
131
132
133
134
135
136
137
138
139
140
141
        return view('admin.affiliates.index')
            ->with('title', "Affilliates Overview - MetaGer")
            ->with('css', [
                mix('/css/admin/affilliates/index.css')
            ])
            ->with('darkcss', [
                mix('/css/admin/affilliates/index-dark.css')
            ])
            ->with('js', [
                mix('/js/admin/affilliates.js')
            ]);
    }

142
143
144
    public function blacklistJson(Request $request)
    {
        $validator = Validator::make($request->all(), [
145
146
147
            "blacklist" => 'boolean'
        ]);

148
149
150
151
        if ($validator->fails()) {
            return response()->json("Invalid Request Data", 422);
        }

152
153
154
155
        $count = 5; # How Many results to return
        $skip = 0; # How many results to skip
        $blacklist = $request->input('blacklist', true);

156
        $total = DB::select("select count(*) as total_rows from affiliate_blacklist where blacklist = ?", [$blacklist]);
157
158
        $total = intval($total[0]->{"total_rows"});
        $blacklistItems = DB::select('select * from affiliate_blacklist where blacklist = ? order by created_at desc limit ? offset ?', [$blacklist, $count, $skip]);
159

160
161
162
163
164
165
166
167
168
169
        $result = [
            "count" => $count,
            "skip" => $skip,
            "total" => $total,
            "results" => $blacklistItems
        ];

        return response()->json($result);
    }

170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
    public function addblacklistJson(Request $request)
    {
        $validator = Validator::make($request->all(), [
            "hostname" => [
                "required",
                function ($attribute, $value, $fail) use ($request) {
                    # Validate that this is indeed a hostname which is resolvable
                    if (!filter_var(gethostbyname($request->input("hostname")), FILTER_VALIDATE_IP)) {
                        $fail("The selected entry is not a valid hostname");
                    }
                },
                function ($attribute, $value, $fail) use ($request) {
                    # Validate that entry does not already exist in database
                    $entry = DB::select("select * from metager.affiliate_blacklist where hostname = ?", [$request->input("hostname")]);
                    if (sizeof($entry) !== 0) {
                        $fail("The selected entry does already exist in database");
                    }
                }
            ],
        ]);

        if ($validator->fails()) {
            return response()->json([
                "message" => "Invalid Request Data"
            ], 422);
        }

        $hostname = $validator->validated()["hostname"];

        $rowsInserted = DB::insert("insert into metager.affiliate_blacklist (hostname, blacklist) values (?, 1)", [$hostname]);
        if ($rowsInserted === TRUE) {
            return response()->json([
                "message" => "Entry added."
            ]);
        } else {
            return response()->json([
                "message" => "Error inserting entry."
            ], 422);
        }
    }

    public function deleteblacklistJson(Request $request)
    {
        $validator = Validator::make($request->all(), [
            "id" => ["required", "integer", "min:1", function ($attribute, $value, $fail) use ($request) {
                $entry = DB::select("select * from metager.affiliate_blacklist where id = ? and blacklist = ?", [$request->input("id"), true]);
                if (sizeof($entry) !== 1) {
                    $fail("The selected entry does not exist in database");
                }
            }],
        ]);

        if ($validator->fails()) {
            return response()->json("Invalid Request Data", 422);
        }

        $id = intval($validator->validated()["id"]);
        $rowsDeleted = DB::delete("delete from metager.affiliate_blacklist where id = ?", [$id]);
        if ($rowsDeleted > 0) {
            return response()->json([
                "message" => "$rowsDeleted entries deleted."
            ]);
        } else {
            return response()->json([
                "message" => "Error deleting entry."
            ], 422);
        }
    }

    public function whitelistJson(Request $request)
    {
241
        $input = $request->all();
242
        $input["blacklist"] = false;
243
244
245
246
        $request->replace($input);
        return $this->blacklistJson($request);
    }

247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
    public function addwhitelistJson(Request $request)
    {
        $validator = Validator::make($request->all(), [
            "hostname" => [
                "required",
                function ($attribute, $value, $fail) use ($request) {
                    # Validate that this is indeed a hostname which is resolvable
                    if (!filter_var(gethostbyname($request->input("hostname")), FILTER_VALIDATE_IP)) {
                        $fail("The selected entry is not a valid hostname");
                    }
                },
                function ($attribute, $value, $fail) use ($request) {
                    # Validate that entry does not already exist in database
                    $entry = DB::select("select * from metager.affiliate_blacklist where hostname = ?", [$request->input("hostname")]);
                    if (sizeof($entry) !== 0) {
                        $fail("The selected entry does already exist in database");
                    }
                }
            ],
        ]);

        if ($validator->fails()) {
            return response()->json([
                "message" => "Invalid Request Data"
            ], 422);
        }

        $hostname = $validator->validated()["hostname"];

        $rowsInserted = DB::insert("insert into metager.affiliate_blacklist (hostname, blacklist) values (?, 0)", [$hostname]);
        if ($rowsInserted === TRUE) {
            return response()->json([
                "message" => "Entry added."
            ]);
        } else {
            return response()->json([
                "message" => "Error inserting entry."
            ], 422);
        }
    }

    public function deletewhitelistJson(Request $request)
    {
        $validator = Validator::make($request->all(), [
            "id" => ["required", "integer", "min:1", function ($attribute, $value, $fail) use ($request) {
                $entry = DB::select("select * from metager.affiliate_blacklist where id = ? and blacklist = ?", [$request->input("id"), false]);
                if (sizeof($entry) !== 1) {
                    $fail("The selected entry does not exist in database");
                }
            }],
        ]);

        if ($validator->fails()) {
            return response()->json("Invalid Request Data", 422);
        }

        $id = intval($validator->validated()["id"]);
        $rowsDeleted = DB::delete("delete from metager.affiliate_blacklist where id = ?", [$id]);
        if ($rowsDeleted > 0) {
            return response()->json([
                "message" => "$rowsDeleted entries deleted."
            ]);
        } else {
            return response()->json([
                "message" => "Error deleting entry."
            ], 422);
        }
    }

    public function hostsJson(Request $request)
    {
        $validator = Validator::make($request->all(), [
            "count" => ["integer", "min:1", "max:50"],
            "skip" => ["integer", "min:0"],
        ]);

        if ($validator->fails()) {
            return response()->json("Invalid Request Data", 422);
        }

        $count = intval($request->input("count", 10));
        $skip = intval($request->input("skip", 0));

        $filter = $request->input("filter", "");

        $hostCount = DB::table("metager.affiliate_clicks", "c")
            ->select(DB::raw("count(distinct c.hostname) as total_hosts"))
            ->leftJoin("metager.affiliate_blacklist", function ($join) {
                $join->on("c.hostname", "=", "metager.affiliate_blacklist.hostname");
            })
            ->where("c.hostname", 'like', "%$filter%")
            ->whereNull("metager.affiliate_blacklist.hostname")
            ->get();
        $hostCount = $hostCount[0]->{"total_hosts"};

        $clickCount = DB::table("metager.affiliate_clicks", "c")
            ->select(DB::raw("count(*) as click_count"))
            ->leftJoin("metager.affiliate_blacklist", function ($join) {
                $join->on("c.hostname", "=", "metager.affiliate_blacklist.hostname");
            })
            ->where("c.hostname", 'like', "%$filter%")
            ->whereNull("metager.affiliate_blacklist.hostname")
            ->get();
        $clickCount = $clickCount[0]->{"click_count"};

        $hosts = DB::table("metager.affiliate_clicks", "c")
            ->select("c.hostname", DB::raw('count(c.hostname) as clicks'))
            ->leftJoin("metager.affiliate_blacklist", function ($join) {
                $join->on("c.hostname", "=", "metager.affiliate_blacklist.hostname");
            })
            ->where("c.hostname", 'like', "%$filter%")
            ->whereNull("metager.affiliate_blacklist.hostname")
            ->groupBy("c.hostname")
            ->orderByDesc("clicks")
            ->limit($count)
            ->offset($skip)
            ->get();

        $result = [
            "count" => $count,
            "skip" => $skip,
            "total_hosts" => $hostCount,
            "total_clicks" => $clickCount,
            "hosts" => $hosts
        ];

        return response()->json($result);
    }

    public function hostClicksJson(Request $request)
    {
        $validator = Validator::make($request->all(), [
            "count" => ["integer", "min:1", "max:50"],
            "skip" => ["integer", "min:0"],
            "hostname" => [
                "required",
                function ($attribute, $value, $fail) use ($request) {
                    # Validate that this is indeed a hostname which is resolvable
                    if (!filter_var(gethostbyname($request->input("hostname")), FILTER_VALIDATE_IP)) {
                        $fail("The selected entry is not a valid hostname");
                    }
                },
                function ($attribute, $value, $fail) use ($request) {
                    # Validate that entry does not already exist in database
                    $entry = DB::table("affiliate_clicks", "c")
                        ->where("hostname", "=", $request->input("hostname"))
                        ->limit(1)
                        ->get();
                    if (sizeof($entry) !== 1) {
                        $fail("The selected entry does not exist in database");
                    }
                }
            ],
        ]);

        if ($validator->fails()) {
            return response()->json([
                "message" => "Invalid Request Data"
            ], 422);
        }

        $count = intval($request->input("count", 10));
        $skip = intval($request->input("skip", 0));

        $total = DB::table("affiliate_clicks", "c")
            ->select(DB::raw("count(*) as count"))
            ->where("hostname", "=", $request->input("hostname"))
            ->get();
        $total = $total[0]->count;

        // Query Data
        $clicks = DB::table("affiliate_clicks", "c")
            ->where("hostname", "=", $request->input("hostname"))
            ->orderByDesc("c.created_at")
            ->limit($count)
            ->offset($skip)
            ->get();

        $result = [
            "count" => $count,
            "skip" => $skip,
            "total" => $total,
            "results" => $clicks
        ];
        return response()->json($result);
    }
Dominik Hebeler's avatar
Dominik Hebeler committed
433
}