File: /var/www/vhost/disk-apps/qas.sports-crowd.com/app/Http/Controllers/PublicController.php
<?php
namespace App\Http\Controllers;
use App\DataPolicy;
use DB;
class PublicController extends Controller
{
public function effectiveTransactions($year = null, $month = null)
{
$report = '<style>
#customers {
font-family: Arial, Helvetica, sans-serif;
border-collapse: collapse;
width: 100%;
cursor: pointer;
}
#customers td, #customers th {
border: 1px solid #ddd;
padding: 8px;
}
#customers tr:nth-child(even){background-color: #f2f2f2;}
#customers tr:hover {background-color: #ddd;}
#customers th {
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: black;
color: white;
}
</style>';
$report .= '<h1 style="text-align: center; background-color: black; color: white; margin: 0px;">Reporte de transacciones efectivas</h1>';
$query = 'SELECT week,
SUM(newUsers) AS newUsers,
SUM(userTicketPurchases) AS userTicketPurchases,
SUM(soldTickets) AS soldTickets,
SUM(userOrders) AS userOrders,
SUM(orders) AS orders,
SUM(userAcademyPayments) AS userAcademyPayments,
SUM(academyPayments) AS academyPayments,
SUM(userTournamentPayments) AS userTournamentPayments,
SUM(tournamentPayments) AS tournamentPayments
FROM (
SELECT DATE_FORMAT(DATE_ADD((SELECT u.created_at - INTERVAL (WEEKDAY(u.created_at) + 1) DAY), INTERVAL 1 DAY),"%Y-%m-%d") AS weekFilter,
DATE_ADD(DATE(u.created_at), INTERVAL (7 - DAYOFWEEK(u.created_at)) + 2 DAY) AS week,
COUNT(u.id) AS newUsers,
0 AS userTicketPurchases,
0 AS soldTickets,
0 AS userOrders,
0 AS orders,
0 AS userAcademyPayments,
0 AS academyPayments,
0 AS userTournamentPayments,
0 AS tournamentPayments
FROM users u
WHERE u.deleted_at IS NULL AND u.rol_id = 4' . ($year ? ' AND YEAR(u.created_at) = ' . $year : '') . ($month ? ' AND MONTH(u.created_at) = ' . $month : '') . '
GROUP BY weekFilter, week
UNION
SELECT DATE_FORMAT(DATE_ADD((SELECT t.created_at - INTERVAL (WEEKDAY(t.created_at) + 1) DAY), INTERVAL 1 DAY),"%Y-%m-%d") AS weekFilter,
DATE_ADD(DATE(t.created_at), INTERVAL (7 - DAYOFWEEK(t.created_at)) + 2 DAY) AS week,
0 AS newUsers,
COUNT(DISTINCT(t.user_id)) AS userTicketPurchases,
COUNT(t.id) AS soldTickets,
0 AS userOrders,
0 AS orders,
0 AS userAcademyPayments,
0 AS academyPayments,
0 AS userTournamentPayments,
0 AS tournamentPayments
FROM tickets t
WHERE t.ticket_status_id = 1' . ($year ? ' AND YEAR(t.created_at) = ' . $year : '') . ($month ? ' AND MONTH(t.created_at) = ' . $month : '') . '
GROUP BY weekFilter, week
UNION
SELECT DATE_FORMAT(DATE_ADD((SELECT o.creation_date - INTERVAL (WEEKDAY(o.creation_date) + 1) DAY), INTERVAL 1 DAY),"%Y-%m-%d") AS weekFilter,
DATE_ADD(DATE(o.creation_date), INTERVAL (7 - DAYOFWEEK(o.creation_date)) + 2 DAY) AS week,
0 AS newUsers,
0 AS userTicketPurchases,
0 AS soldTickets,
COUNT(DISTINCT(o.client_id)) AS userOrders,
COUNT(o.id) AS orders,
0 AS userAcademyPayments,
0 AS academyPayments,
0 AS userTournamentPayments,
0 AS tournamentPayments
FROM users u
JOIN orders o ON o.client_id = u.id
WHERE o.gw_state = "CONFIRMED"' . ($year ? ' AND YEAR(o.creation_date) = ' . $year : '') . ($month ? ' AND MONTH(o.creation_date) = ' . $month : '') . '
GROUP BY weekFilter, week
UNION
SELECT DATE_FORMAT(DATE_ADD((SELECT pt.payment_date - INTERVAL (WEEKDAY(pt.payment_date) + 1) DAY), INTERVAL 1 DAY),"%Y-%m-%d") AS weekFilter,
DATE_ADD(DATE(pt.payment_date), INTERVAL (7 - DAYOFWEEK(pt.payment_date)) + 2 DAY) AS week,
0 AS newUsers,
0 AS userTicketPurchases,
0 AS soldTickets,
0 AS userOrders,
0 AS orders,
COUNT(DISTINCT(ap.user_id)) AS userAcademyPayments,
COUNT(ap.id) AS academyPayments,
0 AS userTournamentPayments,
0 AS tournamentPayments
FROM academy_purchases ap
JOIN payment_transactions pt ON ap.payment_transaction_id = pt.id
WHERE pt.state = "CONFIRMED"' . ($year ? ' AND YEAR(pt.payment_date) = ' . $year : '') . ($month ? ' AND MONTH(pt.payment_date) = ' . $month : '') . '
GROUP BY weekFilter, week
UNION
SELECT DATE_FORMAT(DATE_ADD((SELECT pt.payment_date - INTERVAL (WEEKDAY(pt.payment_date) + 1) DAY), INTERVAL 1 DAY),"%Y-%m-%d") AS weekFilter,
DATE_ADD(DATE(pt.payment_date), INTERVAL (7 - DAYOFWEEK(pt.payment_date)) + 2 DAY) AS week,
0 AS newUsers,
0 AS userTicketPurchases,
0 AS soldTickets,
0 AS userOrders,
0 AS orders,
0 AS userAcademyPayments,
0 AS academyPayments,
COUNT(DISTINCT(au.user_id)) AS userTournamentPayments,
COUNT(ap.id) AS tournamentPayments
FROM academy_users au
JOIN academy_tournament_payments ap ON ap.academy_user_id = au.id
JOIN payment_transactions pt ON ap.payment_transaction_id = pt.id
WHERE pt.state = "CONFIRMED"' . ($year ? ' AND YEAR(pt.payment_date) = ' . $year : '') . ($month ? ' AND MONTH(pt.payment_date) = ' . $month : '') . '
GROUP BY weekFilter, week) a
GROUP BY weekFilter, week
ORDER BY weekFilter DESC';
DB::statement("SET sql_mode = ''");
$data = DB::select($query);
DB::statement("SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
$table = '<table id="customers">
<tr>
<th>Semana</th>
<th>Transacciones Semana</th>
<th>Boletas Vendidas</th>
<th>Pedidos</th>
<th>Pagos Academias</th>
<th>Pagos Torneos</th>
<th> </th>
<th>Nuevos Usuarios</th>
<th>Compras Boletería Usuarios</th>
<th>Pedidos Usuarios</th>
<th>Pagos Academias Usuarios</th>
<th>Pagos Torneos Usuarios</th>
</tr>';
$totalNewUsers = 0;
$totalWeekTransactions = 0;
$totalUserTicketPurchases = 0;
$totalSoldTickets = 0;
$totalUserOrders = 0;
$totalOrders = 0;
$totalUserAcademyPayment = 0;
$totalAcademyPayments = 0;
$totalUserTournamentPayments = 0;
$totalTournamentPayments = 0;
foreach ($data as $item) {
$totalNewUsers += $item->newUsers;
$totalUserTicketPurchases += $item->userTicketPurchases;
$totalSoldTickets += $item->soldTickets;
$totalUserOrders += $item->userOrders;
$totalOrders += $item->orders;
$totalUserAcademyPayment += $item->userAcademyPayments;
$totalAcademyPayments += $item->academyPayments;
$totalUserTournamentPayments += $item->userTournamentPayments;
$totalTournamentPayments += $item->tournamentPayments;
$weekTransactions = $item->soldTickets + $item->orders + $item->academyPayments + $item->tournamentPayments;
$totalWeekTransactions += $weekTransactions;
$table .= '<tr style="border-bottom: 1px solid grey;">' .
'<td style="font-weight: bold">' . $item->week . '</td>' .
'<td>' . $weekTransactions . '</td>' .
'<td>' . $item->soldTickets . '</td>' .
'<td>' . $item->orders . '</td>' .
'<td>' . $item->academyPayments . '</td>' .
'<td>' . $item->tournamentPayments . '</td>' .
'<td></td>' .
'<td>' . $item->newUsers . '</td>' .
'<td>' . $item->userTicketPurchases . '</td>' .
'<td>' . $item->userOrders . '</td>' .
'<td>' . $item->userAcademyPayments . '</td>' .
'<td>' . $item->userTournamentPayments . '</td>' .
'</tr>';
}
$table .= '<tr>
<th>Total</th>
<th>' . $totalWeekTransactions . '</th>
<th>' . $totalSoldTickets . '</th>
<th>' . $totalOrders . '</th>
<th>' . $totalAcademyPayments . '</th>
<th>' . $totalTournamentPayments . '</th>
<th></th>
<th>' . $totalNewUsers . '</th>
<th>' . $totalUserTicketPurchases . '</th>
<th>' . $totalUserOrders . '</th>
<th>' . $totalUserAcademyPayment . '</th>
<th>' . $totalUserTournamentPayments . '</th>
</tr>';
$table .= '</table>';
$report .= $table;
return $report;
}
public function privacity()
{
$dataPolicyUrl = DataPolicy::where('active', 1)->orderByDesc('id')->value('url');
return view('publics.privacity')->with('url', $dataPolicyUrl);
}
public function faq()
{
return view('publics.faq');
}
}