ONE PIECE HACKING

Ngiler SH3LL 360
: / home / tbf / sala_palatului / app / Http / Controllers /

Information Server
MySQL : OFF Perl : OFF CURL : ON WGET : OFF PKEXEC : OFF

Directive	Local Value
IP Address	89.40.16.97
System	Linux server.atelieruldeit.ro 3.10.0-1160.el7.x86_64 #1 SMP Mon Oct 19 16:18:59 UTC 2020 x86_64
User	tbf
PHP Version	7.3.33
Software	Apache
Doc root	Writable

Edit File : ActivityLogController.php \| Size : 13.82 KB
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\ActivityLog; use App\QuizResult; use Auth; use Carbon\Carbon; use Carbon\CarbonPeriod; use DB; use Maatwebsite\Excel\Facades\Excel; use App\Exports\DashboardMainExport; class ActivityLogController extends Controller { public function index(){ return view('statistics'); } public function getLogs(){ $activity_logs = ActivityLog::all()->groupBy('question'); return response()->json($activity_logs); } public function getStatistics(){ $activity_logs = ActivityLog::with('quiz_result')->get(); $activity_logs_contact_data = ActivityLog::whereHas('quiz_result', function ($query){ $query->where('contact_data', 1); })->get(); $quiz_results = QuizResult::where('contact_data',1)->get(); $json = config('constants.main_json')['questions']; $questions_json_array = []; foreach($json as $key => $question){ $q = $activity_logs->where('question', $key); // $total_time = $q->sum('time'); $min_time = $q->min('time') != null ? $q->min('time') : 0; $med_time = $q->count() > 0 ? $q->sum('time')/$q->count() : 0; $max_time = $q->max('time') != null ? $q->max('time'): 0; $questions_json_array[$key] = [ 'titles' => array_key_exists('title',$question) ? $question['title'] : '', 'min_time' => number_format((float)$min_time, 2, '.', ''), 'med_time' => number_format((float)$med_time, 2, '.', ''), 'max_time' => number_format((float)$max_time, 2, '.', ''), 'total' => $q->count(), 'has_finished' => $activity_logs_contact_data->where('question', $key)->count(), ]; } return response()->json([ 'info_card_1' => $activity_logs->where('question','start_page')->count(), 'info_card_2' => $activity_logs->where('question','100')->count(), 'info_card_3' => $quiz_results->count(), 'sala_palatului' => $quiz_results->where('qualified_for','sala_palatului')->count(), 'questions_array' => $questions_json_array ],200); } public function getDashboardStatistics(Request $request){ if($request->startDate != "null" && $request->endDate != "null"){ $activity_logs = ActivityLog::with('quiz_result')->whereBetween('created_at', [Carbon::parse($request->startDate)->startOfDay(), Carbon::parse($request->endDate)->endOfDay()])->get(); $quiz_results = QuizResult::where('contact_data',1)->whereBetween('created_at', [Carbon::parse($request->startDate)->startOfDay(), Carbon::parse($request->endDate)->endOfDay()]); }else{ $activity_logs = ActivityLog::with('quiz_result')->get(); $quiz_results = QuizResult::where('contact_data',1); } // Sala palatului "confirmat" impreuna cu extra_people // Sala palatului "call_clarificare" impreuna cu extra_people $filter_by_statuses = []; foreach (config('status_options.dropdown_options') as $key => $value) { $applicants = QuizResult::where('contact_data',1); $filter_by_statuses[] = [ 'name' => $value, 'unique_companies' => $applicants->where('status',$key)->get()->count(), 'total_people' => $applicants->where('status',$key)->get()->count()+$applicants->where('status',$key)->get()->sum('extra_people') ]; } return response()->json([ 'info_card_1' => $activity_logs->where('question','start_page')->count(), 'info_card_2' => $activity_logs->where('question','100')->count(), 'info_card_3' => $quiz_results->get()->count(), 'by_statuses' => $filter_by_statuses, ],200); } public function getHoursStatistics(Request $request){ if($request->startDate != "null" && $request->endDate != "null"){ $activity_logs = ActivityLog::with('quiz_result')->whereBetween('created_at', [Carbon::parse($request->startDate)->startOfDay(), Carbon::parse($request->endDate)->endOfDay()])->get(); }else{ $activity_logs = ActivityLog::with('quiz_result')->get(); } $first_page_count_array = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]; $start_quiz_count_array = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]; $finish_quiz_count_array = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]; foreach($activity_logs->where('question','start_page') as $log){ $first_page_count_array[$log->created_at->hour]++; } foreach($activity_logs->where('question','100') as $log){ $start_quiz_count_array[$log->created_at->hour]++; } foreach($activity_logs->where('question','29') as $log){ $finish_quiz_count_array[$log->created_at->hour]++; } return response()->json([ [ "name" => "Persoane care au ajuns pe prima pagina", "data"=> $first_page_count_array ], [ "name" => "Persoane care au pornit quizul", "data"=> $start_quiz_count_array ], [ "name" => "Persoane care terminat quizul", "data"=> $finish_quiz_count_array ] ],200); } public function getQuitStatistics(Request $request){ if($request->startDate != "null" && $request->endDate != "null"){ $activity_logs = DB::select( DB::raw(" select grupat.question, count(grupat.question) as nr FROM (select r.session_id,question from activity_logs inner JOIN (SELECT `session_id`,MAX(id) as max_id FROM `activity_logs` WHERE question not LIKE 'start_page' AND step_in = 1 AND step_out = 0 AND created_at between :startDate and :endDate group by session_id) as r ON activity_logs.id=r.max_id) as grupat GROUP by grupat.question order by nr DESC "),['startDate'=>Carbon::parse($request->startDate)->startOfDay(),'endDate'=>Carbon::parse($request->endDate)->endOfDay()]); }else{ $activity_logs = DB::select( DB::raw(" select grupat.question, count(grupat.question) as nr FROM (select r.session_id,question from activity_logs inner JOIN (SELECT `session_id`,MAX(id) as max_id FROM `activity_logs` WHERE question not LIKE 'start_page' AND step_in = 1 AND step_out = 0 group by session_id) as r ON activity_logs.id=r.max_id) as grupat GROUP by grupat.question order by nr DESC ") ); } $categories = array_keys(config('constants.main_json')['questions']); $categories_value_array = []; foreach($categories as $category){ $categories_value_array[$category] = 0; } foreach($activity_logs as $log){ $categories_value_array[$log->question] = $log->nr; } return response()->json([ 'json' => config('constants.main_json')['questions'], 'categories' => $categories, 'series' => [[ 'name' => 'Persoane care au renuntat la quiz', 'data' => $categories_value_array ] ] ],200); } public function getSteppedStatistics(Request $request){ if($request->startDate != "null" && $request->endDate != "null"){ $activity_logs = DB::select( DB::raw(" select question, count(question) as nr FROM activity_logs where question not LIKE 'start_page' AND step_out = 1 AND created_at between :startDate and :endDate GROUP by question order by nr DESC ") ,['startDate'=>Carbon::parse($request->startDate)->startOfDay(),'endDate'=>Carbon::parse($request->endDate)->endOfDay()]); }else{ $activity_logs = DB::select( DB::raw(" select question, count(question) as nr FROM activity_logs where question not LIKE 'start_page' AND step_out = 1 GROUP by question order by nr DESC ") ); } $categories = array_keys(config('constants.main_json')['questions']); $categories_value_array = []; foreach($categories as $category){ $categories_value_array[$category] = 0; } foreach($activity_logs as $log){ $categories_value_array[$log->question] = $log->nr; } return response()->json([ 'json' => config('constants.main_json')['questions'], 'categories' => $categories, 'series' => [[ 'name' => 'Persoane care au raspuns la intrebare', 'data' => $categories_value_array ] ] ],200); } public function getConversionStatistics(Request $request){ if($request->startDate != "null" && $request->endDate != "null"){ $period = CarbonPeriod::create($request->startDate, $request->endDate); $activity_logs = ActivityLog::select(['question','created_at','step_out'])->whereBetween('created_at', [Carbon::parse($request->startDate)->startOfDay(), Carbon::parse($request->endDate)->endOfDay()]) ->get() ->groupBy(function($date) { return Carbon::parse($date->created_at)->format('d-m-Y'); }) ; }else{ $period = CarbonPeriod::create(Carbon::now()->subMonth(), Carbon::now()); $activity_logs = ActivityLog::select(['question','created_at','step_out'])->whereBetween('created_at', [Carbon::now()->subDays(30)->startOfDay(),Carbon::now()->endOfDay()]) ->get() ->groupBy(function($date) { return Carbon::parse($date->created_at)->format('d-m-Y'); }) ; } $dates = $period->toArray(); foreach($dates as $key => $date){ $formated_dates[$key] = $date->format('d-m-Y'); } $log_keys = array_keys($activity_logs->toArray()); foreach($dates as $key => $day){ if(in_array($day->format('d-m-Y'),$log_keys)){ $start_quiz[$key] = $activity_logs[$day->format('d-m-Y')]->where('question','100')->where('step_out', 1)->count(); $finished_quiz[$key] = $activity_logs[$day->format('d-m-Y')]->where('question','29')->where('step_out', 1)->count(); $start_page[$key] = $activity_logs[$day->format('d-m-Y')]->where('question','start_page')->count(); $conversion_rate[$key] = $start_quiz[$key] > 0 ? round((float)(($finished_quiz[$key]*100)/ $start_quiz[$key]),2) : 0; }else{ $start_page[$key] = 0; $start_quiz[$key] = 0; $finished_quiz[$key] = 0; $conversion_rate[$key] = 0; } } return response()->json([ 'categories' => $formated_dates, 'yaxis'=> [ [ "title" => "Procent ( % )", "color" => "#008FFB", "opposite" => false, "show" => true, "max" => max($conversion_rate)+10, "tickAmount" => 4, "series" => "Conversie ( % )" ], [ "title" => "Persoane", "color" => "#00E396", "opposite" => true, "show" => true, "max" => max($start_page)+20, "tickAmount" => 1, "series" => "Numar de afisari" ], [ "title" => "Persoane", "color" => "#00E396", "opposite" => true, "show" => false, "max" => max($start_page)+20, "tickAmount" => 1, "series" => "Numar de afisari" ], [ "title" => "Persoane", "color" => "#00E396", "opposite" => true, "show" => false, "max" => max($start_page)+20, "tickAmount" => 1, "series" => "Numar de persoane care au finalizat quizul" ], ], 'series' => [ [ 'name' => 'Conversie ( % )', "type" => 'line', 'data' => $conversion_rate ], [ 'name' => 'Numar de afisari', "type" => 'column', 'data' => $start_page ], [ 'name' => 'Numar de persoane care au pornit quizul', "type" => 'column', 'data' => $start_quiz ], [ 'name' => 'Numar de persoane care au finalizat quizul', "type" => 'column', 'data' => $finished_quiz ], ] ],200); } public function exportMainResults(Request $request){ $data = json_decode(json_encode($this->getConversionStatistics($request)->getData()),true); $result['dates'] = $data['categories']; $result['series'] = $data['series']; return Excel::download(new DashboardMainExport($result), 'export.xlsx'); } } Back

Edit File : ActivityLogController.php | Size : 13.82 KB

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\ActivityLog;
use App\QuizResult;
use Auth;
use Carbon\Carbon;
use Carbon\CarbonPeriod;
use DB;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\DashboardMainExport;

class ActivityLogController extends Controller
{
    public function index(){
        return view('statistics');
    }

public function getLogs(){
        $activity_logs = ActivityLog::all()->groupBy('question');
        
        return response()->json($activity_logs);        
    }

public function getStatistics(){
        $activity_logs = ActivityLog::with('quiz_result')->get();
        $activity_logs_contact_data = ActivityLog::whereHas('quiz_result', function ($query){
            $query->where('contact_data', 1);
        })->get();
        $quiz_results = QuizResult::where('contact_data',1)->get();
        $json = config('constants.main_json')['questions'];
        $questions_json_array = [];
        foreach($json as $key => $question){
            $q = $activity_logs->where('question', $key);
            // $total_time = $q->sum('time');
            $min_time = $q->min('time') != null ? $q->min('time') : 0;
            $med_time = $q->count() > 0 ? $q->sum('time')/$q->count() : 0;
            $max_time = $q->max('time') != null ? $q->max('time'): 0;
            $questions_json_array[$key] = [
                'titles' => array_key_exists('title',$question) ? $question['title'] : '',
                'min_time' => number_format((float)$min_time, 2, '.', ''),
                'med_time' => number_format((float)$med_time, 2, '.', ''),
                'max_time' => number_format((float)$max_time, 2, '.', ''),
                'total' => $q->count(),
                'has_finished' => $activity_logs_contact_data->where('question', $key)->count(),
            ];
        }

return response()->json([
            'info_card_1' => $activity_logs->where('question','start_page')->count(),
            'info_card_2' => $activity_logs->where('question','100')->count(),
            'info_card_3' => $quiz_results->count(),
            'sala_palatului' => $quiz_results->where('qualified_for','sala_palatului')->count(),
            'questions_array' => $questions_json_array
        ],200);
    }

public function getDashboardStatistics(Request $request){
        if($request->startDate != "null" && $request->endDate != "null"){
            $activity_logs = ActivityLog::with('quiz_result')->whereBetween('created_at', [Carbon::parse($request->startDate)->startOfDay(), Carbon::parse($request->endDate)->endOfDay()])->get();
            $quiz_results = QuizResult::where('contact_data',1)->whereBetween('created_at', [Carbon::parse($request->startDate)->startOfDay(), Carbon::parse($request->endDate)->endOfDay()]);
        }else{
            $activity_logs = ActivityLog::with('quiz_result')->get();
            $quiz_results = QuizResult::where('contact_data',1);
        }

// Sala palatului "confirmat" impreuna cu extra_people

// Sala palatului "call_clarificare" impreuna cu extra_people
        $filter_by_statuses = [];
        foreach (config('status_options.dropdown_options') as $key => $value) {
            $applicants = QuizResult::where('contact_data',1);

$filter_by_statuses[] = [
                'name' => $value,
                'unique_companies' => $applicants->where('status',$key)->get()->count(),
                'total_people' => $applicants->where('status',$key)->get()->count()+$applicants->where('status',$key)->get()->sum('extra_people') 
            ];
        }

return response()->json([
            'info_card_1' => $activity_logs->where('question','start_page')->count(),
            'info_card_2' => $activity_logs->where('question','100')->count(),
            'info_card_3' => $quiz_results->get()->count(),
            'by_statuses' => $filter_by_statuses,
        ],200);
    }

public function getHoursStatistics(Request $request){
        if($request->startDate != "null" && $request->endDate != "null"){
            $activity_logs = ActivityLog::with('quiz_result')->whereBetween('created_at', [Carbon::parse($request->startDate)->startOfDay(), Carbon::parse($request->endDate)->endOfDay()])->get();
        }else{
            $activity_logs = ActivityLog::with('quiz_result')->get();
        }
        $first_page_count_array = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
        $start_quiz_count_array = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
        $finish_quiz_count_array = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
        
        foreach($activity_logs->where('question','start_page') as $log){
            $first_page_count_array[$log->created_at->hour]++;
        }
        foreach($activity_logs->where('question','100') as $log){
            $start_quiz_count_array[$log->created_at->hour]++;
        }
        foreach($activity_logs->where('question','29') as $log){
            $finish_quiz_count_array[$log->created_at->hour]++;
        }
        return response()->json([
            [
                "name" => "Persoane care au ajuns pe prima pagina",
                "data"=> $first_page_count_array
            ],
            [
                "name" => "Persoane care au pornit quizul",
                "data"=> $start_quiz_count_array
            ],
            [
                "name" => "Persoane care terminat quizul",
                "data"=> $finish_quiz_count_array
            ]
        ],200);
    }

public function getQuitStatistics(Request $request){
        if($request->startDate != "null" && $request->endDate != "null"){
            $activity_logs = DB::select( DB::raw("
                select grupat.question, count(grupat.question) as nr FROM
                (select r.session_id,question from activity_logs inner JOIN
                (SELECT `session_id`,MAX(id) as max_id FROM `activity_logs` WHERE question not LIKE 'start_page' AND step_in = 1 AND step_out = 0 AND created_at between :startDate and :endDate
                group by session_id) as r ON activity_logs.id=r.max_id) as grupat
                GROUP by grupat.question
                order by nr DESC
            "),['startDate'=>Carbon::parse($request->startDate)->startOfDay(),'endDate'=>Carbon::parse($request->endDate)->endOfDay()]);
        }else{
            $activity_logs = DB::select( DB::raw("
                select grupat.question, count(grupat.question) as nr FROM
                (select r.session_id,question from activity_logs inner JOIN
                (SELECT `session_id`,MAX(id) as max_id FROM `activity_logs` WHERE question not LIKE 'start_page' AND step_in = 1 AND step_out = 0
                group by session_id) as r ON activity_logs.id=r.max_id) as grupat
                GROUP by grupat.question
                order by nr DESC
            ") );
        }

$categories = array_keys(config('constants.main_json')['questions']);
        $categories_value_array = [];

foreach($categories as $category){
            $categories_value_array[$category] = 0;
        }
        foreach($activity_logs as $log){
            $categories_value_array[$log->question] = $log->nr;
        }
        return response()->json([
            'json' => config('constants.main_json')['questions'],
            'categories' => $categories,
            'series' => [[
                'name' => 'Persoane care au raspuns la intrebare',
                'data' => $categories_value_array
                ]
            ]
        ],200);
    }
    public function getConversionStatistics(Request $request){
        if($request->startDate != "null" && $request->endDate != "null"){
            $period = CarbonPeriod::create($request->startDate, $request->endDate);
            $activity_logs = ActivityLog::select(['question','created_at','step_out'])->whereBetween('created_at', [Carbon::parse($request->startDate)->startOfDay(), Carbon::parse($request->endDate)->endOfDay()])
                                        ->get()
                                        ->groupBy(function($date) {
                                            return Carbon::parse($date->created_at)->format('d-m-Y');
                                        })
                                        ;
        }else{
            $period = CarbonPeriod::create(Carbon::now()->subMonth(), Carbon::now());
            $activity_logs = ActivityLog::select(['question','created_at','step_out'])->whereBetween('created_at', [Carbon::now()->subDays(30)->startOfDay(),Carbon::now()->endOfDay()])
                                    ->get()
                                    ->groupBy(function($date) {
                                        return Carbon::parse($date->created_at)->format('d-m-Y');
                                    })
                                    ;
        }
        $dates = $period->toArray();
        foreach($dates as $key => $date){
            $formated_dates[$key] = $date->format('d-m-Y');
        }
        $log_keys = array_keys($activity_logs->toArray());
        foreach($dates as $key => $day){
            if(in_array($day->format('d-m-Y'),$log_keys)){
                $start_quiz[$key] = $activity_logs[$day->format('d-m-Y')]->where('question','100')->where('step_out', 1)->count();
                $finished_quiz[$key] = $activity_logs[$day->format('d-m-Y')]->where('question','29')->where('step_out', 1)->count();
                $start_page[$key] = $activity_logs[$day->format('d-m-Y')]->where('question','start_page')->count();
                $conversion_rate[$key] =  $start_quiz[$key] > 0 ? round((float)(($finished_quiz[$key]*100)/ $start_quiz[$key]),2) : 0;
            }else{
                $start_page[$key] = 0;
                $start_quiz[$key] = 0;
                $finished_quiz[$key] = 0;
                $conversion_rate[$key] = 0;
            }
        }
        return response()->json([
            'categories' => $formated_dates,
            'yaxis'=> [
                [
                    "title" => "Procent ( % )",
                    "color" => "#008FFB",
                    "opposite" => false,
                    "show" => true,
                    "max" => max($conversion_rate)+10,
                    "tickAmount" => 4,
                    "series" => "Conversie ( % )"
                ],
                [
                    "title" => "Persoane",
                    "color" => "#00E396",
                    "opposite" => true,
                    "show" => true,
                    "max" => max($start_page)+20,
                    "tickAmount" => 1,
                    "series" => "Numar de afisari"
                ],
                [
                    "title" => "Persoane",
                    "color" => "#00E396",
                    "opposite" => true,
                    "show" => false,
                    "max" => max($start_page)+20,
                    "tickAmount" => 1,
                    "series" => "Numar de afisari"
                ],
                [
                    "title" => "Persoane",
                    "color" => "#00E396",
                    "opposite" => true,
                    "show" => false,
                    "max" => max($start_page)+20,
                    "tickAmount" => 1,
                    "series" => "Numar de persoane care au finalizat quizul"
                ],
            ],
            'series' => [
                [
                    'name' => 'Conversie ( % )',
                    "type" => 'line',
                    'data' => $conversion_rate
                ],
                [
                    'name' => 'Numar de afisari',
                    "type" => 'column',
                    'data' => $start_page
                ],
                [
                    'name' => 'Numar de persoane care au pornit quizul',
                    "type" => 'column',
                    'data' => $start_quiz
                ],
                [
                    'name' => 'Numar de persoane care au finalizat quizul',
                    "type" => 'column',
                    'data' => $finished_quiz
                ],
            ]
        ],200);

}

public function exportMainResults(Request $request){
        $data = json_decode(json_encode($this->getConversionStatistics($request)->getData()),true);
        $result['dates'] = $data['categories'];
        $result['series'] = $data['series'];

return Excel::download(new DashboardMainExport($result), 'export.xlsx');
    }
}

Back