File: /var/www/vhost/disk-apps/qas.sports-crowd.com/app/Http/Controllers/ReportAcademyController.php
<?php
namespace App\Http\Controllers;
use App\AcademyLocation;
use App\Http\Controllers\Exports\AcademyBriefCaseExport;
use App\Http\Controllers\Exports\AcademyReportCollectionsvsReceivablesTeacherCaseExport;
use App\Http\Controllers\Exports\AcademyCategoryHeadquartersExport;
use App\Http\Controllers\Exports\AcademyCumulativeHeadquartersExport;
use App\Http\Controllers\Exports\AcademyUserBillingReportExport;
use App\Services\AcademyLocationsService;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
class ReportAcademyController extends Controller
{
private $util;
public function __construct()
{
$this->util = new UtilController();
}
function index()
{
$academyLocationsService = new AcademyLocationsService;
if (!$academyLocationsService->validateAuthorizedLocations()) {
$authorizedLocations = $academyLocationsService->getAuthorizedLocations();
$franchises = AcademyLocation::select('id', 'name')->where('active', 1)->whereIn('id', $authorizedLocations)->orderBy('name', 'ASC')->get();
} else {
$franchises = AcademyLocation::select('id', 'name')->where('active', 1)->orderBy('name', 'ASC')->get();
}
return view("reports_academy.reports_academy")->with('franchises', $franchises);
}
public function filterColumns($request, $type)
{
$distinct = "";
$emptyMonthCounter = 0;
if ($type == "alumno_profesor") {
$distinct = "distinct";
}
if ($type == "sede_categoria" || $type == "acumulado_sede") {
$distinct = "";
}
$months = ["enero", "febrero", "marzo", "abril", "mayo", "junio", "julio", "agosto", "septiembre", "octubre", "noviembre", "diciembre"];
$calculatedMonths = array();
$final_string = "";
$newHaving = "";
if (empty($request->from_term)) {
$emptyMonthCounter += 1;
}
if (empty($request->from_date)) {
$emptyMonthCounter += 1;
}
if (empty($request->to_term)) {
$emptyMonthCounter += 1;
}
if (empty($request->to_date)) {
$emptyMonthCounter += 1;
}
if ($emptyMonthCounter == 1 || $emptyMonthCounter == 2) {
if (!empty($request->from_term) && empty($request->from_date)) {
$request->from_date = $request->from_term;
}
if (empty($request->from_term) && !empty($request->from_date)) {
$request->from_term = $request->from_date;
}
if (!empty($request->to_term) && empty($request->to_date)) {
$request->to_date = $request->to_term;
}
if (empty($request->to_term) && !empty($request->to_date)) {
$request->to_term = $request->to_date;
}
if (empty($request->from_term) && empty($request->from_date)) {
$request->from_term = "2023-01-01";
$request->from_date = "2023-01-01";
}
if (empty($request->to_term) && empty($request->to_date)) {
$request->to_term = date("Y-12-31");
$request->to_date = date("Y-12-31");
}
}
if ($emptyMonthCounter == 3) {
if (!empty($request->from_term) || !empty($request->from_date)) {
$request->from_term = max($request->from_date, $request->from_term);
$request->from_date = max($request->from_date, $request->from_term);
} else {
$request->from_term = "2023-01-01";
$request->from_date = "2023-01-01";
}
if (!empty($request->to_term) || !empty($request->to_date)) {
$request->to_term = max($request->to_term, $request->to_date);
$request->to_date = max($request->to_term, $request->to_date);
} else {
$request->to_term = date("Y-12-31");
$request->to_date = date("Y-12-31");
}
}
if ($emptyMonthCounter == 4) {
$request->from_term = "2023-01-01";
$request->from_date = "2023-01-01";
$request->to_term = date("Y-12-31");
$request->to_date = date("Y-12-31");
}
$from_date_max = min($request->from_date, $request->from_term);
$to_date_max = max($request->to_term, $request->to_date);
// Divide la fecha de inicio y la fecha de finalizacion maximas
$separador = explode("-", $from_date_max);
$year_init = $separador[0];
$month_init = $separador[1];
$separador = explode("-", $to_date_max);
$year_end = $separador[0];
$month_end = $separador[1];
$month_init_aux = (int)$month_init;
$month_end_aux = (int)$month_end;
$year_init_aux = (int)$year_init;
$year_end_aux = (int)$year_end;
if ($year_init_aux == $year_end_aux) {
while ($month_init_aux <= $month_end_aux) {
$month_name = $months[$month_init_aux - 1];
$new_value = "SUM($distinct CASE WHEN ap.term = '$year_init_aux, $month_name' THEN ap.price ELSE 0 END) AS $year_init_aux" . "_" . "$month_name," . "\n";
$final_string .= $new_value;
$calculatedMonths[] = $year_init_aux . "_" . $month_name;
$having = $year_init_aux . "_" . $month_name . "+";
$newHaving .= $having;
$month_init_aux += 1;
}
$newHaving = substr($newHaving, 0, -1);
}
if ($year_init_aux < $year_end_aux) {
$var = 1;
while (($month_init_aux <= 12 || ($month_init_aux - $var != $month_end_aux)) || $year_init_aux != $year_end_aux) {
$month_name = $months[$month_init_aux - $var];
$new_value = "SUM($distinct CASE WHEN ap.term = '$year_init_aux, $month_name' THEN ap.price ELSE 0 END) AS $year_init_aux" . "_" . "$month_name," . "\n";
$final_string .= $new_value;
$calculatedMonths[] = $year_init_aux . "_" . $month_name;
if ($month_init_aux % 12 == 0 && $year_init_aux != $year_end_aux) {
$year_init_aux += 1;
$var = $month_init_aux + 1;
}
$having = $year_init_aux . "_" . $month_name . "+";
$newHaving .= $having;
$month_init_aux += 1;
}
$newHaving = substr($newHaving, 0, -1);
}
return [$final_string, $calculatedMonths, $newHaving];
}
public function validateReport(Request $request)
{
$type = isset($request->type) ? $request->type : null;
if ($type == "report_collections_vs_receivables_teacher") {
$name = 'ReporteRecaudoyCartera' . time() . '.xlsx';
Excel::store(new AcademyUserBillingReportExport($request->all()), $name, 'public');
return response()->json(['success' => true, 'message' => 'Validación OK', 'data' => $name]);
}
$filteredMonths = $this->filterColumns($request, $type);
$from_term_condition = isset($request->from_term) ? "(date(ap.start_term) >= '$request->from_term'" : '';
$to_term_condition = isset($request->to_term) ? "AND date(ap.end_term) <= '$request->to_term' )" : '';
$from_date_condition = isset($request->from_date) ? "or (date(pt.payment_date) >= '$request->from_date' " : '';
$to_date_condition = isset($request->to_date) ? "AND date(pt.payment_date) <= '$request->to_date' )" : '';
$franchises = $request->franchises;
if (!empty($franchises)) {
$franchise_condition = " AND al.id IN (" . implode(',', $franchises) . ")";
} else {
$franchise_condition = "";
}
if (!$type) {
return response()->json(['error' => false, 'message' => 'Tipo de reporte no específicado']);
}
$query = "";
if ($type === "alumno_profesor") {
$query = " SELECT au.id, au.student_name, au.student_last_name, au.identification,ac.name as estado_proceso, al.name as sede_alumno, ac2.name as categoria_alumno, GROUP_CONCAT(DISTINCT CONCAT(u.first_name, ' ', u.last_name) ORDER BY u.first_name, u.last_name ASC) AS coaches,
IF(ap.enrollment_academy_price != 0,ap.price,0) AS enrollment_academy_price, " . $filteredMonths[0] . " SUM(ap.price)/count(DISTINCT CONCAT(u.first_name, ' ', u.last_name)) AS total
FROM academy_users au
LEFT JOIN academy_states ac ON ac.id = au.academy_state_id
LEFT JOIN academy_categories ac2 ON ac2.id = au.academy_category_id
LEFT JOIN academy_locations al ON al.id = ac2.academy_location_id
LEFT JOIN academy_schedules as2 ON as2.id = au.academy_schedule_id
LEFT JOIN academy_schedules_coaches acs ON acs.academy_schedule_id = as2.id
LEFT JOIN users u ON u.id = acs.user_id
LEFT JOIN academy_purchases ap ON ap.academy_user_id = au.id
LEFT JOIN payment_transactions pt ON pt.id = ap.payment_transaction_id
WHERE au.deleted_at IS NULL AND ap.deleted_at IS NULL AND ap.active = 1 AND (pt.state != 'CONFIRMED' OR pt.id IS NULL) AND ($from_term_condition $to_term_condition $from_date_condition $to_date_condition or ap.enrollment_academy_price is not null) $franchise_condition
GROUP BY
au.id,
au.student_name,
au.student_last_name,
au.identification,
ac.name,
ac2.name,
al.name,
enrollment_academy_price,
ap.price
HAVING(" . $filteredMonths[2] . " != 0) or ap.enrollment_academy_price is not null";
} else if ($type === "sede_categoria") {
$query = "SELECT al.name as sede_alumno, ac2.name as plan_entreno, IFNULL(ap.enrollment_academy_price, 0) AS enrollment_academy_price, " . $filteredMonths[0] . " SUM(ap.price) AS total
FROM `academy_users` au
LEFT JOIN academy_states ac ON ac.id = au.academy_state_id
LEFT JOIN academy_categories ac2 ON ac2.id = au.academy_category_id
LEFT JOIN academy_locations al ON al.id = ac2.academy_location_id
LEFT JOIN academy_purchases ap ON ap.academy_user_id = au.id
LEFT JOIN payment_transactions pt ON pt.id = ap.payment_transaction_id
WHERE au.deleted_at IS NULL AND ap.deleted_at IS NULL AND ap.active = 1 AND (pt.state != 'CONFIRMED' OR pt.id IS NULL) AND ($from_term_condition $to_term_condition $from_date_condition $to_date_condition or ap.enrollment_academy_price is not null) $franchise_condition
GROUP BY
sede_alumno,
plan_entreno,
ap.enrollment_academy_price
HAVING(" . $filteredMonths[2] . " != 0) or ap.enrollment_academy_price is not null";
} else if ($type === "acumulado_sede") {
$query = "SELECT al.name as sede_alumno, SUM( CASE WHEN ap.enrollment_academy_price != 0 THEN ap.price ELSE 0 END) AS enrollment_academy_price, " . $filteredMonths[0] . " SUM(ap.price) AS total
FROM `academy_users` au
LEFT JOIN academy_states ac ON ac.id = au.academy_state_id
LEFT JOIN academy_categories ac2 ON ac2.id = au.academy_category_id
LEFT JOIN academy_locations al ON al.id = ac2.academy_location_id
LEFT JOIN academy_purchases ap ON ap.academy_user_id = au.id
LEFT JOIN payment_transactions pt ON pt.id = ap.payment_transaction_id
WHERE au.deleted_at IS NULL AND ap.deleted_at IS NULL AND ap.active = 1 AND (pt.state != 'CONFIRMED' OR pt.id IS NULL) AND ($from_term_condition $to_term_condition $from_date_condition $to_date_condition or ap.enrollment_academy_price is not null) $franchise_condition
GROUP BY
sede_alumno
HAVING(" . $filteredMonths[2] . " != 0) or enrollment_academy_price != 0";
} else if ($type === "report_collections_vs_receivables_teacher") {
$queries = $this->createQuery($request);
$queryStudentInfo = $queries[0];
$resultStudentInfo = $this->util->getGenericData($queryStudentInfo, "");
$queryPaymentInfo = $queries[1];
$resultsPaymentInfo = $this->util->getGenericData($queryPaymentInfo, "");
$studentsCombined = $this->mergeData($resultStudentInfo, $resultsPaymentInfo);
}
if ($type == "report_collections_vs_receivables_teacher" and count($studentsCombined) > 0) {
if ($type === "report_collections_vs_receivables_teacher") {
$name = 'reporteDeRecaudoVScarteraPorProfesor' . time() . '.xlsx';
Excel::store(new AcademyReportCollectionsvsReceivablesTeacherCaseExport($studentsCombined), $name, 'public');
}
return response()->json(['success' => true, 'message' => 'Validación OK', 'data' => $name]);
}
if ($type != "report_collections_vs_receivables_teacher") {
$results = $this->util->getGenericData($query, "");
if (count($results) > 0) {
if ($type === "alumno_profesor") {
$name = 'ReporteCarteraAlumnoProfesor' . time() . '.xlsx';
Excel::store(new AcademyBriefCaseExport($results, $filteredMonths[1]), $name, 'public');
}
if ($type === "sede_categoria") {
$name = 'ReporteSedeCategoria' . time() . '.xlsx';
Excel::store(new AcademyCategoryHeadquartersExport($results, $filteredMonths[1]), $name, 'public');
}
if ($type === "acumulado_sede") {
$name = 'ReporteSede' . time() . '.xlsx';
Excel::store(new AcademyCumulativeHeadquartersExport($results, $filteredMonths[1]), $name, 'public');
}
return response()->json(['success' => true, 'message' => 'Validación OK', 'data' => $name]);
}
}
return response()->json(['success' => false, 'message' => 'No existen datos a exportar']);
}
public function export($name)
{
return $this->util->export($name);
}
public function createQuery($queryParams)
{
// $currentInitDate = date('Y-01-01');
// $currentEndDate = date('Y-m-d');
$start_term = $queryParams['from_term'] ?? null;
$end_term = $queryParams['to_term'] ?? null;
$start_payment_date = $queryParams['from_date'] ?? null;
$end_payment_date = $queryParams['to_date'] ?? null;
$franchises = $queryParams->franchises;
if (!empty($franchises)) {
$franchise_condition = " AND al.id IN (" . implode(',', $franchises) . ")";
} else {
$franchise_condition = "";
}
$queryStudentInfo = "SELECT
au.id,
au.student_name,
au.student_last_name,
au.identification,
ac.name AS estado_proceso,
al.name AS sede_alumno,
ac2.name AS categoria_alumno,
GROUP_CONCAT(DISTINCT CONCAT(u.first_name, ' ', u.last_name) ORDER BY u.first_name, u.last_name ASC) AS coaches,
COALESCE(aep.enrollment_academy_price, 0) AS enrollment_academy_price
FROM
academy_users au
LEFT JOIN academy_states ac ON ac.id = au.academy_state_id
LEFT JOIN academy_categories ac2 ON ac2.id = au.academy_category_id
LEFT JOIN academy_locations al ON al.id = ac2.academy_location_id
LEFT JOIN academy_schedules as2 ON as2.id = au.academy_schedule_id
LEFT JOIN academy_schedules_coaches acs ON acs.academy_schedule_id = as2.id
JOIN academy_purchases ap ON ap.academy_user_id = au.id
LEFT JOIN payment_transactions pt ON pt.id = ap.payment_transaction_id
LEFT JOIN users u ON u.id = acs.user_id
LEFT JOIN (
replaceWithAcademyEnrollmentPrice
GROUP BY ap.academy_user_id
) aep ON aep.academy_user_id = au.id
WHERE
au.deleted_at IS NULL AND ac.id > 4 $franchise_condition";
$queryPaymentInfo = "SELECT
au.id,
ap.term AS fecha_mesualidad,
ap.price AS precio_mesualidad,
pt.state AS estado,
DATE_FORMAT(ap.start_term, '%Y-%m') AS start_term,
IFNULL(pm.name, '') AS forma_de_pago
FROM
academy_users au
LEFT JOIN academy_purchases ap ON ap.academy_user_id = au.id
LEFT JOIN payment_transactions pt ON pt.id = ap.payment_transaction_id
LEFT JOIN payment_methods pm ON pm.id = pt.payment_method_id
WHERE
au.deleted_at IS NULL
AND ap.deleted_at IS NULL
AND ap.monthly_academy_price IS NOT NULL";
$queryAcademyEnrollment = "SELECT
ap.academy_user_id,
MAX(CASE WHEN ap.enrollment_academy_price != 0 THEN ap.price ELSE 0 END) AS enrollment_academy_price
FROM
academy_purchases ap
LEFT JOIN payment_transactions pt ON pt.id = ap.payment_transaction_id
WHERE
ap.deleted_at IS NULL";
$apConditions = [];
$ptConditions = [];
$apConditionToAcademyEnrollment = [];
if ($start_term) {
$apConditions[] = "DATE(ap.start_term) >= '$start_term'";
$apConditionToAcademyEnrollment[] = "DATE(pt.payment_date) >= '$start_term'";
}
if ($end_term) {
$apConditions[] = "DATE(ap.end_term) <= '$end_term'";
}
if ($start_payment_date) {
$ptConditions[] = "DATE(pt.payment_date) >= '$start_payment_date'";
}
if ($end_payment_date) {
$ptConditions[] = "DATE(pt.payment_date) <= '$end_payment_date'";
}
if ($apConditions || $ptConditions) {
$queryPaymentInfo .= " AND (";
$queryStudentInfo .= " AND (";
$queryAcademyEnrollment .= " AND (";
if ($apConditions) {
$queryPaymentInfo .= "(" . implode(' AND ', $apConditions) . ")";
$queryStudentInfo .= "(" . implode(' AND ', $apConditions) . ")";
$queryAcademyEnrollment .= "(" . implode(' AND ', $apConditionToAcademyEnrollment) . ")";
}
if ($apConditions && $ptConditions) {
$queryPaymentInfo .= " OR ";
$queryStudentInfo .= " OR ";
}
if ($ptConditions) {
$queryPaymentInfo .= "(" . implode(' AND ', $ptConditions) . ")";
$queryStudentInfo .= "(" . implode(' AND ', $ptConditions) . ")";
}
$queryPaymentInfo .= ")";
$queryStudentInfo .= ")";
$queryAcademyEnrollment .= ")";
}
$queryPaymentInfo .= " GROUP BY
au.id,
DATE_FORMAT(ap.start_term, '%Y-%m'),
ap.term,
ap.price,
pt.state,
pm.name
ORDER BY
DATE_FORMAT(ap.start_term, '%Y-%m') ASC,
pm.name ASC;";
$queryStudentInfo = str_replace('replaceWithAcademyEnrollmentPrice', $queryAcademyEnrollment, $queryStudentInfo);
$queryStudentInfo .= " GROUP BY
au.id,
au.student_name,
au.student_last_name,
au.identification,
ac.name,
ac2.name,
al.name;";
return [$queryStudentInfo, $queryPaymentInfo];
}
public function groupById($mensualidades)
{
$agrupados = [];
foreach ($mensualidades as $mensualidad) {
$id = $mensualidad->id;
if (!isset($agrupados[$id])) {
$agrupados[$id] = [];
}
$agrupados[$id][] = $mensualidad;
}
return $agrupados;
}
function mergeData($students, $mensualidades)
{
$mensualidadesAgrupadas = $this->groupById($mensualidades);
foreach ($students as &$estudiante) {
$id = $estudiante->id;
if (isset($mensualidadesAgrupadas[$id])) {
$estudiante->mensualidades = $mensualidadesAgrupadas[$id];
} else {
$estudiante->mensualidades = [];
}
}
unset($estudiante);
return $students;
}
}