@extends('dashboard.layout') @section('content') @php $Date = date('Y-m-d',strtotime("-1 month")); $Analytics = []; $Analytics['new'] = []; $Analytics['total'] = []; $row = \DB::select("SELECT count(*) as Counter FROM `notifications_subscribers` WHERE DATE(`created_at`) > ? ",[$Date]); $Analytics['new']['Created'] = $row[0]->Counter; $row = \DB::select(" SELECT count(*) as `Counter` FROM `notifications_subscribers` inner join `visitors` on `visitors`.`id` = `notifications_subscribers`.`visitor_id` WHERE DATE(`notifications_subscribers`.`last_seen`) > ? AND DATE(`notifications_subscribers`.`created_at`) > ? ",[$Date,$Date]); $Analytics['new']['LastSeen'] = $row[0]->Counter; $row = \DB::select("SELECT count(*) as Counter FROM `notifications_subscribers` WHERE DATE(`created_at`) > ? ",[$Date]); $Analytics['total']['LastSeen'] = $row[0]->Counter; $row = \DB::select("SELECT count(*) as Counter FROM `notifications_sent` WHERE `status`='scheduled' AND DATE(`created_at`) > ? ",[$Date]); $Analytics['new']['Scheduled'] = $row[0]->Counter; $row = \DB::select("SELECT count(*) as Counter FROM `notifications_sent` WHERE DATE(`created_at`) > ? ",[$Date]); $Analytics['total']['Scheduled'] = $row[0]->Counter; $Notification = \DB::select(" SELECT SUM(`total_recipients`) as `total_recipients`, SUM(`success_recipients`) as `success_recipients`, SUM(`failure_recipients`) as `failure_recipients` FROM `notifications_sent` WHERE DATE(`created_at`) > ? ",[$Date]); $Analytics['new']['Notifications'] = intval($Notification[0]->total_recipients); $Analytics['new']['success_recipients'] = intval($Notification[0]->success_recipients); $Analytics['new']['failure_recipients'] = intval($Notification[0]->failure_recipients); $Notification = \DB::select(" SELECT SUM(`total_recipients`) as `total_recipients`, SUM(`success_recipients`) as `success_recipients`, SUM(`failure_recipients`) as `failure_recipients` FROM `notifications_sent` WHERE DATE(`created_at`) > ? ",[$Date]); $Analytics['total']['Notifications'] = intval($Notification[0]->total_recipients); $Analytics['total']['success_recipients'] = intval($Notification[0]->success_recipients); $Analytics['total']['failure_recipients'] = intval($Notification[0]->failure_recipients); /**/ $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`browser`='Apple Safari' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $Safari = $row[0]->Counter; $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`browser`='Firefox' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $FireFox = $row[0]->Counter; $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`browser`='Chrome' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $Chrome = $row[0]->Counter; $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`browser`='Opera' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $Opera = $row[0]->Counter; $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`browser`='Edge' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $Edge = $row[0]->Counter; /**/ $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`platform` Like 'Windows%' and `visitors`.`platform`!='Windows Phone' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $Windows = $row[0]->Counter; $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`platform` Like 'linux%' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $Linux = $row[0]->Counter; $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`platform`='Macintosh' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $Mac = $row[0]->Counter; $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`platform`='Android' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $Android = $row[0]->Counter; $row = \DB::select("SELECT count(*) as `Counter` FROM `visitors` INNER Join `notifications_subscribers` on `visitors`.`id`=`notifications_subscribers`.`visitor_id` WHERE `visitors`.`platform`='IOS' and DATE(`visitors`.`created_at`) > ? ",[$Date]); $IOS = $row[0]->Counter; @endphp

{{__('Push Notification')}}

{{$Analytics['new']['Created']}} {{__('New Subscribers')}}
{{$Analytics['new']['LastSeen']}} {{__('Active Subscribers')}}
{{$Analytics['new']['Scheduled']}} {{__('Scheduled Notifications')}}
{{$Analytics['total']['success_recipients']}} {{__('Total Success Recipients')}}
{{$Analytics['new']['success_recipients']}} {{__('New Success Recipients')}}
{{$Analytics['total']['LastSeen']}} {{__('Total Subscribers')}}
{{$Analytics['total']['Notifications']}} {{__('Total Pushed Notifications')}}
{{$Analytics['new']['Notifications']}} {{__('New Pushed Notifications')}}
{{$Analytics['total']['failure_recipients']}} {{__('Total Failure Recipients')}}
{{$Analytics['new']['failure_recipients']}} {{__('New Failure Recipients')}}

{{__('FCM Key')}}

{{__('browser Usage')}}

{{__('OS Usage')}}

{{__('Subscribers')}} (Daily in Last 30 Days)

{{__('Notifications Opened Rate')}} (Last 30 Days)

@endsection