838 lines
35 KiB
PHP
Executable File
838 lines
35 KiB
PHP
Executable File
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace IR\App\Controllers;
|
|
|
|
if (!defined('IR_START')) exit('<pre>No direct script access allowed</pre>');
|
|
/**
|
|
* @framework iResponse Framework
|
|
* @version 1.0
|
|
* @author Amine Idrissi <contact@iresponse.tech>
|
|
* @date 2019
|
|
* @name Dashboard.php
|
|
*/
|
|
|
|
# core
|
|
use IR\Core\Application as Application;
|
|
|
|
# mvc
|
|
use IR\Mvc\Controller as Controller;
|
|
use IR\App\Models\Lists\DataList as DataList ;
|
|
use IR\App\Models\Affiliate\AffiliateNetwork as AffiliateNetwork;
|
|
use IR\App\Models\Affiliate\Offer as Offer;
|
|
|
|
# helpers
|
|
use IR\App\Helpers\Authentication as Authentication;
|
|
use IR\App\Helpers\Page as Page;
|
|
use IR\App\Helpers\Permissions as Permissions;
|
|
use IR\App\Models\Admin\MtaServer;
|
|
# exceptions
|
|
use IR\Exceptions\Types\PageException as PageException;
|
|
|
|
/**
|
|
* @name Dashboard
|
|
* @description Dashboard Controller
|
|
*/
|
|
class Dashboard extends Controller
|
|
{
|
|
/**
|
|
* @app
|
|
* @readwrite
|
|
*/
|
|
protected $app;
|
|
|
|
/**
|
|
* @app
|
|
* @readwrite
|
|
*/
|
|
protected $authenticatedUser;
|
|
|
|
/**
|
|
* @name init
|
|
* @description initializing process before the action method executed
|
|
* @once
|
|
* @protected
|
|
*/
|
|
public function init()
|
|
{
|
|
# set the current application to a local variable
|
|
$this->app = Application::getCurrent();
|
|
|
|
# connect to the database
|
|
$this->app->database('system')->connect();
|
|
|
|
# check for authentication
|
|
if (!Authentication::isUserAuthenticated()) {
|
|
Page::redirect($this->app->http->request->getBaseURL() . RDS . 'auth' . RDS . 'login.' . DEFAULT_EXTENSION);
|
|
}
|
|
|
|
# check users roles
|
|
Authentication::checkUserRoles();
|
|
|
|
# get the authenticated user
|
|
$this->authenticatedUser = Authentication::getAuthenticatedUser();
|
|
}
|
|
|
|
/**
|
|
* @name main
|
|
* @description the main action
|
|
* @before init
|
|
* @after closeConnections,checkForMessage
|
|
*/
|
|
public function main()
|
|
{
|
|
# check for permissions
|
|
$access = Permissions::checkForAuthorization($this->authenticatedUser, __CLASS__, __FUNCTION__);
|
|
|
|
if ($access == false) {
|
|
throw new PageException('Access Denied !', 403);
|
|
}
|
|
|
|
# gather first row info
|
|
$servers = $this->app->database('system')->query()->from('admin.mta_servers')->where('status = ?', 'Activated')->count();
|
|
$ips = $this->app->database('system')->query()->from('admin.servers_vmtas m')
|
|
->join('admin.mta_servers s', 'm.mta_server_id = s.id')->where('m.status = ? and s.status = ? and type = ?', ['Activated', 'Activated', 'Default'])->count();
|
|
$affiliateNetworks = $this->app->database('system')->query()->from('affiliate.affiliate_networks')->where('status = ?', 'Activated')->count();
|
|
$offers = $this->app->database('system')->query()->from('affiliate.offers')->where('status = ?', 'Activated')->count();
|
|
$tests = $this->app->database('system')->query()->from('production.mta_processes')->where("process_type NOT LIKE 'drop' AND start_time BETWEEN ? AND current_date + interval '1' day ", date('Y-m-d'))->count()
|
|
+ $this->app->database('system')->query()->from('production.smtp_processes')->where("process_type NOT LIKE 'drop' AND start_time BETWEEN ? AND current_date + interval '1' day ", date('Y-m-d'))->count();
|
|
$drops = $this->app->database('system')->query()->from('production.mta_processes')->where("process_type = 'drop' AND start_time BETWEEN ? AND current_date + interval '1' day ", date('Y-m-d'))->count()
|
|
+ $this->app->database('system')->query()->from('production.smtp_processes')->where("process_type = 'drop' AND start_time BETWEEN ? AND current_date + interval '1' day ", date('Y-m-d'))->count();
|
|
|
|
# gather all information about affiliate networks stats
|
|
$stats = $this->app->database('system')->query()
|
|
->from('actions.leads', ['SUM(payout)' => 'earnings', 'COUNT(1)' => 'conversions'])
|
|
->where("user_full_name != 'Unknown Mailer' and action_time BETWEEN ? AND current_date + interval '1' day", [date('Y-m-01')])
|
|
->first();
|
|
|
|
$earnings = doubleval($this->app->utils->arrays->get($stats, 'earnings'));
|
|
$conversions = intval($this->app->utils->arrays->get($stats, 'conversions'));
|
|
|
|
$clicks = $this->app->utils->arrays->get($this->app->database('system')->query()
|
|
->from('actions.clicks', ['COUNT(1)' => 'count'])
|
|
->where("user_full_name != 'Unknown Mailer' and action_time BETWEEN ? AND current_date + interval '1' day", [date('Y-m-01')])
|
|
->first(), 'count');
|
|
$openers = $this->app->utils->arrays->get($this->app->database('system')->query()
|
|
->from('actions.unsubscribes', ['COUNT(1)' => 'count'])
|
|
->where("user_full_name != 'Unknown Mailer' and action_time BETWEEN ? AND current_date + interval '1' day", [date('Y-m-01')])
|
|
->first(), 'count');
|
|
$unsubs = $this->app->utils->arrays->get($this->app->database('system')->query()
|
|
->from('actions.unsubscribes', ['COUNT(1)' => 'count'])
|
|
->where("user_full_name != 'Unknown Mailer' and action_time BETWEEN ? AND current_date + interval '1' day", [date('Y-m-01')])
|
|
->first(), 'count');
|
|
|
|
# gather all information about sending stats
|
|
$stats = $this->app->database('system')->query()
|
|
->from('production.mta_processes', ['SUM(total_emails)' => 'sum_total', 'SUM(delivered)' => 'sum_delivered', 'SUM(hard_bounced)' => 'sum_hard_bounced'])
|
|
->where("process_type = 'drop' AND start_time BETWEEN ? AND current_date + interval '1' day", [date('Y-m-d')])
|
|
->first();
|
|
|
|
$sent = intval($this->app->utils->arrays->get($stats, 'sum_total'));
|
|
$delivered = intval($this->app->utils->arrays->get($stats, 'sum_delivered'));
|
|
$bounced = intval($this->app->utils->arrays->get($stats, 'sum_hard_bounced'));
|
|
|
|
$stats = $this->app->database('system')->query()
|
|
->from('production.smtp_processes', ['SUM(total_emails)' => 'sum_total', 'SUM(delivered)' => 'sum_delivered', 'SUM(hard_bounced)' => 'sum_hard_bounced'])
|
|
->where("process_type = 'drop' AND start_time BETWEEN ? AND current_date + interval '1' day", [date('Y-m-d')])
|
|
->first();
|
|
|
|
$sent += intval($this->app->utils->arrays->get($stats, 'sum_total'));
|
|
$delivered += intval($this->app->utils->arrays->get($stats, 'sum_delivered'));
|
|
$bounced += intval($this->app->utils->arrays->get($stats, 'sum_hard_bounced'));
|
|
|
|
$dclicks = [];
|
|
$dclicks['all'] = [];
|
|
$dclicks['last_week'] = [];
|
|
|
|
|
|
//check for notifications
|
|
|
|
//servers expiration notifications
|
|
if (isset($_SESSION['servers_clicks_check_time']) && $_SESSION['servers_clicks_check_time'] == date("Y-m-d")) {
|
|
//check expiration date for servers * 3 days duration
|
|
$dclicks['all'] = $_SESSION['clicks_all'];
|
|
$dclicks['last_week'] = $_SESSION['clicks_last_week'];
|
|
} else {
|
|
for ($i = 0; $i < 24; $i++) {
|
|
try {
|
|
//get all
|
|
$dclicks['all'][$i]['count'] = $this->app->utils->arrays->get($this->app->database('system')->query()
|
|
->from('actions.clicks', ['COUNT(1)' => 'count'])
|
|
->where("user_full_name != 'Unknown Mailer' and process_type = 'md' and date_part('hour', action_time) = ?", [$i])
|
|
->first(), 'count');
|
|
$dclicks['all'][$i]['hour'] = $i;
|
|
|
|
//last week
|
|
$dclicks['last_week'][$i]['count'] = $this->app->utils->arrays->get($this->app->database('system')->query()
|
|
->from('actions.clicks', ['COUNT(1)' => 'count'])
|
|
->where("user_full_name != 'Unknown Mailer' and process_type = 'md' and action_time BETWEEN ? AND current_date + interval '1' day and date_part('hour', action_time) = ?", [date('Y-m-d', strtotime('-1 week')), $i])
|
|
->first(), 'count');
|
|
$dclicks['last_week'][$i]['hour'] = $i;
|
|
} catch (\Throwable $th) {
|
|
// echo $th;
|
|
// die();
|
|
}
|
|
}
|
|
|
|
$_SESSION['servers_clicks_check_time'] = date("Y-m-d");
|
|
$_SESSION['clicks_all'] = $dclicks['all'];
|
|
$_SESSION['clicks_last_week'] = $dclicks['last_week'];
|
|
}
|
|
|
|
# set menu status
|
|
$this->masterView->set([
|
|
'dashboard' => 'true'
|
|
]);
|
|
|
|
# set data to the page view
|
|
$this->pageView->set([
|
|
'servers' => $servers,
|
|
'ips' => $ips,
|
|
'affiliateNetworks' => $affiliateNetworks,
|
|
'offers' => $offers,
|
|
'tests' => $tests,
|
|
'drops' => $drops,
|
|
'earnings' => number_format((float)$earnings, 2, '.', ''),
|
|
'clicks' => $clicks,
|
|
'unsubs' => $unsubs,
|
|
'openers' => $openers,
|
|
'conversions' => $conversions,
|
|
'sent' => $sent,
|
|
'delivered' => $delivered,
|
|
'bounced' => $bounced,
|
|
'dclicks_all_hours' => json_encode(array_column($dclicks['all'], 'hour')),
|
|
'dclicks_all_data' => json_encode(array_column($dclicks['all'], 'count')),
|
|
'dclicks_last_week_hours' => json_encode(array_column($dclicks['last_week'], 'hour')),
|
|
'dclicks_last_week_data' => json_encode(array_column($dclicks['last_week'], 'count')),
|
|
]);
|
|
|
|
|
|
//check for notifications
|
|
|
|
//servers expiration notifications
|
|
if (true) {
|
|
//check expiration date for servers * 3 days duration
|
|
$_SESSION['servers_exp_check_time'] = date("Y-m-d");
|
|
$servers_today = [];
|
|
$servers_1_day = [];
|
|
$servers_soon = [];
|
|
$servers = MtaServer::all(MtaServer::FETCH_ARRAY, ['status = ? and expiration_date < ?', ['Activated', date('Y-m-d', strtotime('+3 days'))]], ['name', 'provider_name', 'expiration_date'], 'name');
|
|
|
|
foreach ($servers as $key => $server) {
|
|
$ndays = $this->getDuration(date('d', strtotime($server['expiration_date'])));
|
|
if ($ndays !== false && is_numeric($ndays)) {
|
|
$serverName = "$server[name]: $server[provider_name]";
|
|
if ($ndays == 0) $servers_today[] = $serverName;
|
|
if ($ndays == 1) $servers_1_day[] = $serverName;
|
|
if ($ndays > 1) $servers_soon[] = $serverName;
|
|
}
|
|
}
|
|
if (count($servers_soon) > 0) $this->showNotif('warning', "server(s) <br>" . join(', <br>', $servers_soon) . "<br> will expire in less than 3 days");
|
|
elseif (count($servers_1_day) > 0) $this->showNotif('error', "server(s) <br>" . join(', <br>', $servers_1_day) . "<br> will expire TOMORROW");
|
|
elseif (count($servers_today) > 0) $this->showNotif('error', "server(s) <br>" . join(', <br>', $servers_today) . "<br> will expire TODAY");
|
|
} elseif (isset($_SESSION['exp_message']) && isset($_SESSION['exp_flag'])) {
|
|
$this->showNotif($_SESSION['exp_flag'], $_SESSION['exp_message']);
|
|
};
|
|
$DataList = DataList::all(DataList::FETCH_ARRAY,["status = ? and name like '%data%'",'Activated']);
|
|
$AllOffers = Offer::all(Offer::FETCH_ARRAY,["name not like '%YAHOO%'",'']);
|
|
$AffiliateNetworks = AffiliateNetwork::all(AffiliateNetwork::FETCH_ARRAY,["status = ?",'Activated']);
|
|
$Sponsors = [];
|
|
foreach($AffiliateNetworks as $Sponsor) {
|
|
$Sponsors[] = array(
|
|
"id"=>$Sponsor["id"],
|
|
"affiliate_id"=>$Sponsor["affiliate_id"],
|
|
"name"=>$Sponsor["name"],
|
|
"MonthEarning"=>0,
|
|
"WeekEarning"=>0,
|
|
"YesterdayEarning"=>0,
|
|
"MonthSend"=>0,
|
|
"WeekSend"=>0,
|
|
"YesterdaySend"=>0,
|
|
|
|
"EPMS_Month"=>0,
|
|
"EPMS_Week"=>0,
|
|
"EPMS_Yesterday"=>0,
|
|
|
|
"PercentageDSend"=>0
|
|
|
|
);
|
|
}
|
|
$this->masterView->set([
|
|
'top_stats' => 'true'
|
|
]);
|
|
$OffersStats = [];
|
|
foreach($AllOffers as $Offer) {
|
|
|
|
|
|
$SP_found_key = array_search($Offer['affiliate_network_id'], array_column($Sponsors, 'id'));
|
|
|
|
$MonthLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("offer_production_id = ? and action_time BETWEEN current_date - interval '29' day AND current_date ",[$Offer["production_id"]])
|
|
->first();
|
|
$WeekLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("offer_production_id = ? and action_time BETWEEN current_date - interval '6' day AND current_date ",[$Offer["production_id"]])
|
|
->first();
|
|
$YesterdayLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("offer_production_id = ? and action_time BETWEEN current_date - interval '1' day AND current_date ",[$Offer["production_id"]])
|
|
->first();
|
|
|
|
$MonthSend = $this->app->database('system')->query()
|
|
->from('production.mta_processes',['COALESCE(SUM(progress),0)' => 'send','COUNT(1)' => 'conversions'])
|
|
->where("offer_id = ? and start_time BETWEEN current_date - interval '29' day AND current_date ",[$Offer["id"]])
|
|
->first();
|
|
$WeekSend = $this->app->database('system')->query()
|
|
->from('production.mta_processes',['COALESCE(SUM(progress),0)' => 'send','COUNT(1)' => 'conversions'])
|
|
->where("offer_id = ? and start_time BETWEEN current_date - interval '6' day AND current_date ",[$Offer["id"]])
|
|
->first();
|
|
$YesterdaySend = $this->app->database('system')->query()
|
|
->from('production.mta_processes',['COALESCE(SUM(progress),0)' => 'send','COUNT(1)' => 'conversions'])
|
|
->where("offer_id = ? and start_time BETWEEN current_date - interval '1' day AND current_date ",[$Offer["id"]])
|
|
->first();
|
|
$LastSendDate = $this->app->database('system')->query()
|
|
->from('production.mta_processes',['start_time' => 'lastsend'])
|
|
->where("offer_id = ? ",[$Offer["id"]])
|
|
->order("id")
|
|
->last();
|
|
$Month_SendByMilion = floatval($MonthSend["send"])/1000000 ;
|
|
$Week_SendByMilion = floatval($WeekSend["send"])/1000000 ;
|
|
$Yesterday_SendByMilion = floatval($YesterdaySend["send"])/1000000 ;
|
|
if (is_nan($Month_SendByMilion))
|
|
{
|
|
$Month_SendByMilion = 0 ;
|
|
}
|
|
if (is_nan($Week_SendByMilion))
|
|
{
|
|
$Week_SendByMilion = 0 ;
|
|
}
|
|
if (is_nan($Yesterday_SendByMilion))
|
|
{
|
|
$Yesterday_SendByMilion = 0 ;
|
|
}
|
|
$Month_SendByMilion = round( $Month_SendByMilion, 2);
|
|
$Week_SendByMilion = round( $Week_SendByMilion, 2);
|
|
$Yesterday_SendByMilion = round( $Yesterday_SendByMilion, 2);
|
|
|
|
|
|
$EarnPerMilionSend_Month = $Month_SendByMilion != 0
|
|
? floatval($MonthLeads["earnings"]) / $Month_SendByMilion
|
|
: 0;
|
|
|
|
$EarnPerMilionSend_Week = $Week_SendByMilion != 0
|
|
? floatval($WeekLeads["earnings"]) / $Week_SendByMilion
|
|
: 0;
|
|
|
|
$EarnPerMilionSend_Yesterday = $Yesterday_SendByMilion != 0
|
|
? floatval($YesterdayLeads["earnings"]) / $Yesterday_SendByMilion
|
|
: 0;
|
|
|
|
if (is_nan($EarnPerMilionSend_Month))
|
|
{
|
|
$EarnPerMilionSend_Month = 0 ;
|
|
}
|
|
if (is_nan($EarnPerMilionSend_Week))
|
|
{
|
|
$EarnPerMilionSend_Week = 0 ;
|
|
}
|
|
if (is_nan($EarnPerMilionSend_Yesterday))
|
|
{
|
|
$EarnPerMilionSend_Yesterday = 0 ;
|
|
}
|
|
|
|
if (is_infinite($EarnPerMilionSend_Month))
|
|
{
|
|
$EarnPerMilionSend_Month = 0 ;
|
|
}
|
|
if (is_infinite($EarnPerMilionSend_Week))
|
|
{
|
|
$EarnPerMilionSend_Week = 0 ;
|
|
}
|
|
if (is_infinite($EarnPerMilionSend_Yesterday))
|
|
{
|
|
$EarnPerMilionSend_Yesterday = 0 ;
|
|
}
|
|
|
|
$EarnPerMilionSend_Month = round( $EarnPerMilionSend_Month, 2);
|
|
$EarnPerMilionSend_Week= round( $EarnPerMilionSend_Week, 2);
|
|
$EarnPerMilionSend_Yesterday = round( $EarnPerMilionSend_Yesterday, 2);
|
|
|
|
|
|
|
|
|
|
$Sponsors[$SP_found_key]["MonthEarning"] = $Sponsors[$SP_found_key]["MonthEarning"] + round(floatval($MonthLeads["earnings"]), 2);
|
|
$Sponsors[$SP_found_key]["WeekEarning"] = $Sponsors[$SP_found_key]["WeekEarning"] + round(floatval($WeekLeads["earnings"]), 2);
|
|
$Sponsors[$SP_found_key]["YesterdayEarning"] = $Sponsors[$SP_found_key]["YesterdayEarning"] +round( floatval($YesterdayLeads["earnings"]), 2);
|
|
|
|
$Sponsors[$SP_found_key]["MonthSend"] = $Sponsors[$SP_found_key]["MonthSend"] + floatval($Month_SendByMilion) ;
|
|
$Sponsors[$SP_found_key]["WeekSend"] = $Sponsors[$SP_found_key]["WeekSend"] + floatval($Week_SendByMilion) ;
|
|
$Sponsors[$SP_found_key]["YesterdaySend"] = $Sponsors[$SP_found_key]["YesterdaySend"] + floatval($Yesterday_SendByMilion) ;
|
|
|
|
$Sponsors[$SP_found_key]["EPMS_Month"] = round(($Sponsors[$SP_found_key]["EPMS_Month"] + floatval($EarnPerMilionSend_Month) ) /2) ;
|
|
$Sponsors[$SP_found_key]["EPMS_Week"] = round(($Sponsors[$SP_found_key]["EPMS_Week"] + floatval($EarnPerMilionSend_Week) ) /2) ;
|
|
$Sponsors[$SP_found_key]["EPMS_Yesterday"] = round(($Sponsors[$SP_found_key]["EPMS_Yesterday"] + floatval($EarnPerMilionSend_Yesterday) ) /2) ;
|
|
|
|
|
|
|
|
$OfferStatica= array(
|
|
"OfferProd_ID"=>$Offer["production_id"],
|
|
"Offer_ID"=>$Offer["id"],
|
|
"OfferName"=>$Offer["name"],
|
|
"MonthEarning"=>$MonthLeads["earnings"],
|
|
"WeekEarning"=>$WeekLeads["earnings"],
|
|
"YesterdayEarning"=>$YesterdayLeads["earnings"],
|
|
"MonthSend"=>$Month_SendByMilion,
|
|
"WeekSend"=>$Week_SendByMilion,
|
|
"YesterdaySend"=>$Yesterday_SendByMilion,
|
|
"EPMS_Month"=>$EarnPerMilionSend_Month,
|
|
"EPMS_Week"=>$EarnPerMilionSend_Week,
|
|
"EPMS_Yesterday"=>$EarnPerMilionSend_Yesterday,
|
|
);
|
|
|
|
$OffersStats[] = $OfferStatica ;
|
|
};
|
|
usort($OffersStats, function ($item1, $item2) {return $item2['MonthEarning'] <=> $item1['MonthEarning'];});
|
|
$OffersStatsMONTHSORT =$OffersStats ;
|
|
usort($OffersStats, function ($item1, $item2) {return $item2['WeekEarning'] <=> $item1['WeekEarning'];});
|
|
$OffersStatsWEEKSORT=$OffersStats ;
|
|
usort($OffersStats, function ($item1, $item2) {return $item2['YesterdayEarning'] <=> $item1['YesterdayEarning'];});
|
|
$OffersStatsYESTERDAYSORT =$OffersStats ;
|
|
|
|
|
|
$OffersStatsWEEKSORT = array_slice($OffersStatsWEEKSORT, 0, 4);
|
|
$OffersStatsYESTERDAYSORT = array_slice($OffersStatsYESTERDAYSORT, 0, 5);
|
|
$OffersMonthReal = [] ;
|
|
$OffersNewForActive = [] ;
|
|
$OffersNewForClean = [] ;
|
|
foreach($OffersStatsMONTHSORT as $OffersStats_MNTH)
|
|
{
|
|
if(!in_array($OffersStats_MNTH, $OffersStatsYESTERDAYSORT, true))
|
|
{
|
|
if(!in_array($OffersStats_MNTH, $OffersStatsWEEKSORT, true))
|
|
{
|
|
$OffersMonthReal[] = $OffersStats_MNTH;
|
|
//if(count($OffersMonthReal) == 3)
|
|
//{
|
|
// break;
|
|
//}
|
|
}
|
|
}
|
|
|
|
if(floatval($OffersStats_MNTH["MonthSend"]) <= 1)
|
|
{
|
|
$OffersNewForActive[] = $OffersStats_MNTH;
|
|
}
|
|
if(floatval($OffersStats_MNTH["MonthSend"]) <= 4 && floatval($OffersStats_MNTH["MonthEarning"]) > 1 )
|
|
{
|
|
$OffersNewForClean[] = $OffersStats_MNTH;
|
|
|
|
}
|
|
|
|
}
|
|
$OffersMonthReal = array_slice($OffersMonthReal, 0, 3);
|
|
$OffersNewForActive = array_slice($OffersNewForActive, count($OffersNewForActive)-6, count($OffersNewForActive));
|
|
$OffersNewForClean = array_slice($OffersNewForClean, 0, 4);
|
|
$OffersStatsMONTHSORT = array_slice($OffersStatsMONTHSORT, 0, 3);
|
|
$DataLeads = [];
|
|
foreach($DataList as $Data) {
|
|
|
|
$MonthLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("list_id = ? and action_time BETWEEN current_date - interval '30' day AND current_date ",[$Data["id"]])
|
|
->first();
|
|
$WeekLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("list_id = ? and action_time BETWEEN current_date - interval '7' day AND current_date ",[$Data["id"]])
|
|
->first();
|
|
$YesterdayLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("list_id = ? and action_time BETWEEN current_date - interval '1' day AND current_date ",[$Data["id"]])
|
|
->first();
|
|
|
|
$TopOfferID = $this->app->database('system')->query()
|
|
->from('actions.leads',['offer_production_id' => 'offer_production_id','COUNT(1)' => 'conversions'])
|
|
->where("list_id = ? ",[$Data["id"]])
|
|
->group(["offer_production_id"])
|
|
->order("COUNT(1)")
|
|
->last();
|
|
$TopOffer = $this->app->database('system')->query()
|
|
->from('affiliate.offers',['name' => 'name'])
|
|
->where("production_id = ?",[$TopOfferID["offer_production_id"]])
|
|
->first();
|
|
$OfferName = $TopOffer["name"];
|
|
if($TopOffer)
|
|
{
|
|
$OfferName = "<b style='color:#24695c'>".$TopOfferID["offer_production_id"]." </b>| ".$OfferName ;
|
|
}
|
|
|
|
$DataLeads[]= array("Month"=>$MonthLeads["earnings"],"TopOffer"=>$OfferName, "Week"=>$WeekLeads["earnings"], "Yesterday"=>$YesterdayLeads["earnings"], "name"=>$Data["name"],"id"=>$Data["id"]);
|
|
|
|
};
|
|
$TotalSend = 0 ;
|
|
foreach($Sponsors as $Sponsor) {
|
|
$TotalSend = $TotalSend + $Sponsor["MonthSend"];
|
|
}
|
|
$SponsorsReal = [];
|
|
foreach($Sponsors as $Sponsor) {
|
|
|
|
$Sponsor["PercentageDSend"] = ($TotalSend > 0)
|
|
? round(((100 / $TotalSend) * $Sponsor["MonthSend"]), 2)
|
|
: 0;
|
|
|
|
$SponsorsReal[] = $Sponsor ;
|
|
}
|
|
$this->pageView->set([
|
|
'DataList' => $DataList,
|
|
'DataLeads' => $DataLeads,
|
|
'OffersStats_Month' => $OffersMonthReal,
|
|
'OffersStats_Week' => $OffersStatsWEEKSORT,
|
|
'Sponsors' => $SponsorsReal,
|
|
'OffersStats_Yesterday' => $OffersStatsYESTERDAYSORT,
|
|
'OffersNewForActive' => $OffersNewForActive,
|
|
'OffersNewForClean' => $OffersNewForClean
|
|
]); }
|
|
public function topStats()
|
|
{
|
|
|
|
$this->app = Application::getCurrent();
|
|
|
|
# connect to the database
|
|
$this->app->database('system')->connect();
|
|
|
|
# check for authentication
|
|
if(!Authentication::isUserAuthenticated())
|
|
{
|
|
Page::redirect($this->app->http->request->getBaseURL() . RDS . 'auth' . RDS . 'login.' . DEFAULT_EXTENSION);
|
|
}
|
|
|
|
# check users roles
|
|
Authentication::checkUserRoles();
|
|
|
|
# get the authenticated user
|
|
$this->authenticatedUser = Authentication::getAuthenticatedUser();
|
|
# check for permissions
|
|
$access = Permissions::checkForAuthorization($this->authenticatedUser,__CLASS__,"Dashboard");
|
|
|
|
if($access == false)
|
|
{
|
|
throw new PageException('Access Denied !',403);
|
|
}
|
|
$DataList = DataList::all(DataList::FETCH_ARRAY,["status = ? and name like '%data%'",'Activated']);
|
|
$AllOffers = Offer::all(Offer::FETCH_ARRAY,["name not like '%YAHOO%'",'']);
|
|
$AffiliateNetworks = AffiliateNetwork::all(AffiliateNetwork::FETCH_ARRAY,["status = ?",'Activated']);
|
|
$Sponsors = [];
|
|
foreach($AffiliateNetworks as $Sponsor) {
|
|
$Sponsors[] = array(
|
|
"id"=>$Sponsor["id"],
|
|
"affiliate_id"=>$Sponsor["affiliate_id"],
|
|
"name"=>$Sponsor["name"],
|
|
"MonthEarning"=>0,
|
|
"WeekEarning"=>0,
|
|
"YesterdayEarning"=>0,
|
|
"MonthSend"=>0,
|
|
"WeekSend"=>0,
|
|
"YesterdaySend"=>0,
|
|
|
|
"EPMS_Month"=>0,
|
|
"EPMS_Week"=>0,
|
|
"EPMS_Yesterday"=>0,
|
|
|
|
"PercentageDSend"=>0
|
|
|
|
);
|
|
}
|
|
$this->masterView->set([
|
|
'top_stats' => 'true'
|
|
]);
|
|
$OffersStats = [];
|
|
foreach($AllOffers as $Offer) {
|
|
|
|
|
|
$SP_found_key = array_search($Offer['affiliate_network_id'], array_column($Sponsors, 'id'));
|
|
|
|
$MonthLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("offer_production_id = ? and action_time BETWEEN current_date - interval '29' day AND current_date ",[$Offer["production_id"]])
|
|
->first();
|
|
$WeekLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("offer_production_id = ? and action_time BETWEEN current_date - interval '6' day AND current_date ",[$Offer["production_id"]])
|
|
->first();
|
|
$YesterdayLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("offer_production_id = ? and action_time BETWEEN current_date - interval '1' day AND current_date ",[$Offer["production_id"]])
|
|
->first();
|
|
|
|
$MonthSend = $this->app->database('system')->query()
|
|
->from('production.mta_processes',['COALESCE(SUM(progress),0)' => 'send','COUNT(1)' => 'conversions'])
|
|
->where("offer_id = ? and start_time BETWEEN current_date - interval '29' day AND current_date ",[$Offer["id"]])
|
|
->first();
|
|
$WeekSend = $this->app->database('system')->query()
|
|
->from('production.mta_processes',['COALESCE(SUM(progress),0)' => 'send','COUNT(1)' => 'conversions'])
|
|
->where("offer_id = ? and start_time BETWEEN current_date - interval '6' day AND current_date ",[$Offer["id"]])
|
|
->first();
|
|
$YesterdaySend = $this->app->database('system')->query()
|
|
->from('production.mta_processes',['COALESCE(SUM(progress),0)' => 'send','COUNT(1)' => 'conversions'])
|
|
->where("offer_id = ? and start_time BETWEEN current_date - interval '1' day AND current_date ",[$Offer["id"]])
|
|
->first();
|
|
$LastSendDate = $this->app->database('system')->query()
|
|
->from('production.mta_processes',['start_time' => 'lastsend'])
|
|
->where("offer_id = ? ",[$Offer["id"]])
|
|
->order("id")
|
|
->last();
|
|
$Month_SendByMilion = floatval($MonthSend["send"])/1000000 ;
|
|
$Week_SendByMilion = floatval($WeekSend["send"])/1000000 ;
|
|
$Yesterday_SendByMilion = floatval($YesterdaySend["send"])/1000000 ;
|
|
if (is_nan($Month_SendByMilion))
|
|
{
|
|
$Month_SendByMilion = 0 ;
|
|
}
|
|
if (is_nan($Week_SendByMilion))
|
|
{
|
|
$Week_SendByMilion = 0 ;
|
|
}
|
|
if (is_nan($Yesterday_SendByMilion))
|
|
{
|
|
$Yesterday_SendByMilion = 0 ;
|
|
}
|
|
$Month_SendByMilion = round( $Month_SendByMilion, 2);
|
|
$Week_SendByMilion = round( $Week_SendByMilion, 2);
|
|
$Yesterday_SendByMilion = round( $Yesterday_SendByMilion, 2);
|
|
|
|
|
|
$EarnPerMilionSend_Month = $Month_SendByMilion != 0
|
|
? floatval($MonthLeads["earnings"]) / $Month_SendByMilion
|
|
: 0;
|
|
|
|
$EarnPerMilionSend_Week = $Week_SendByMilion != 0
|
|
? floatval($WeekLeads["earnings"]) / $Week_SendByMilion
|
|
: 0;
|
|
|
|
$EarnPerMilionSend_Yesterday = $Yesterday_SendByMilion != 0
|
|
? floatval($YesterdayLeads["earnings"]) / $Yesterday_SendByMilion
|
|
: 0;
|
|
|
|
if (is_nan($EarnPerMilionSend_Month))
|
|
{
|
|
$EarnPerMilionSend_Month = 0 ;
|
|
}
|
|
if (is_nan($EarnPerMilionSend_Week))
|
|
{
|
|
$EarnPerMilionSend_Week = 0 ;
|
|
}
|
|
if (is_nan($EarnPerMilionSend_Yesterday))
|
|
{
|
|
$EarnPerMilionSend_Yesterday = 0 ;
|
|
}
|
|
|
|
if (is_infinite($EarnPerMilionSend_Month))
|
|
{
|
|
$EarnPerMilionSend_Month = 0 ;
|
|
}
|
|
if (is_infinite($EarnPerMilionSend_Week))
|
|
{
|
|
$EarnPerMilionSend_Week = 0 ;
|
|
}
|
|
if (is_infinite($EarnPerMilionSend_Yesterday))
|
|
{
|
|
$EarnPerMilionSend_Yesterday = 0 ;
|
|
}
|
|
|
|
$EarnPerMilionSend_Month = round( $EarnPerMilionSend_Month, 2);
|
|
$EarnPerMilionSend_Week= round( $EarnPerMilionSend_Week, 2);
|
|
$EarnPerMilionSend_Yesterday = round( $EarnPerMilionSend_Yesterday, 2);
|
|
|
|
|
|
|
|
|
|
$Sponsors[$SP_found_key]["MonthEarning"] = $Sponsors[$SP_found_key]["MonthEarning"] + round(floatval($MonthLeads["earnings"]), 2);
|
|
$Sponsors[$SP_found_key]["WeekEarning"] = $Sponsors[$SP_found_key]["WeekEarning"] + round(floatval($WeekLeads["earnings"]), 2);
|
|
$Sponsors[$SP_found_key]["YesterdayEarning"] = $Sponsors[$SP_found_key]["YesterdayEarning"] +round( floatval($YesterdayLeads["earnings"]), 2);
|
|
|
|
$Sponsors[$SP_found_key]["MonthSend"] = $Sponsors[$SP_found_key]["MonthSend"] + floatval($Month_SendByMilion) ;
|
|
$Sponsors[$SP_found_key]["WeekSend"] = $Sponsors[$SP_found_key]["WeekSend"] + floatval($Week_SendByMilion) ;
|
|
$Sponsors[$SP_found_key]["YesterdaySend"] = $Sponsors[$SP_found_key]["YesterdaySend"] + floatval($Yesterday_SendByMilion) ;
|
|
|
|
$Sponsors[$SP_found_key]["EPMS_Month"] = round(($Sponsors[$SP_found_key]["EPMS_Month"] + floatval($EarnPerMilionSend_Month) ) /2) ;
|
|
$Sponsors[$SP_found_key]["EPMS_Week"] = round(($Sponsors[$SP_found_key]["EPMS_Week"] + floatval($EarnPerMilionSend_Week) ) /2) ;
|
|
$Sponsors[$SP_found_key]["EPMS_Yesterday"] = round(($Sponsors[$SP_found_key]["EPMS_Yesterday"] + floatval($EarnPerMilionSend_Yesterday) ) /2) ;
|
|
|
|
|
|
|
|
$OfferStatica= array(
|
|
"OfferProd_ID"=>$Offer["production_id"],
|
|
"OfferName"=>$Offer["name"],
|
|
"MonthEarning"=>$MonthLeads["earnings"],
|
|
"WeekEarning"=>$WeekLeads["earnings"],
|
|
"YesterdayEarning"=>$YesterdayLeads["earnings"],
|
|
"MonthSend"=>$Month_SendByMilion,
|
|
"WeekSend"=>$Week_SendByMilion,
|
|
"YesterdaySend"=>$Yesterday_SendByMilion,
|
|
"EPMS_Month"=>$EarnPerMilionSend_Month,
|
|
"EPMS_Week"=>$EarnPerMilionSend_Week,
|
|
"EPMS_Yesterday"=>$EarnPerMilionSend_Yesterday,
|
|
);
|
|
|
|
$OffersStats[] = $OfferStatica ;
|
|
};
|
|
usort($OffersStats, function ($item1, $item2) {return $item2['MonthEarning'] <=> $item1['MonthEarning'];});
|
|
$OffersStatsMONTHSORT =$OffersStats ;
|
|
usort($OffersStats, function ($item1, $item2) {return $item2['WeekEarning'] <=> $item1['WeekEarning'];});
|
|
$OffersStatsWEEKSORT=$OffersStats ;
|
|
usort($OffersStats, function ($item1, $item2) {return $item2['YesterdayEarning'] <=> $item1['YesterdayEarning'];});
|
|
$OffersStatsYESTERDAYSORT =$OffersStats ;
|
|
|
|
|
|
$OffersStatsWEEKSORT = array_slice($OffersStatsWEEKSORT, 0, 4);
|
|
$OffersStatsYESTERDAYSORT = array_slice($OffersStatsYESTERDAYSORT, 0, 5);
|
|
$OffersMonthReal = [] ;
|
|
$OffersNewForActive = [] ;
|
|
$OffersNewForClean = [] ;
|
|
foreach($OffersStatsMONTHSORT as $OffersStats_MNTH)
|
|
{
|
|
if(!in_array($OffersStats_MNTH, $OffersStatsYESTERDAYSORT, true))
|
|
{
|
|
if(!in_array($OffersStats_MNTH, $OffersStatsWEEKSORT, true))
|
|
{
|
|
$OffersMonthReal[] = $OffersStats_MNTH;
|
|
//if(count($OffersMonthReal) == 3)
|
|
//{
|
|
// break;
|
|
//}
|
|
}
|
|
}
|
|
|
|
if(floatval($OffersStats_MNTH["MonthSend"]) <= 1)
|
|
{
|
|
$OffersNewForActive[] = $OffersStats_MNTH;
|
|
}
|
|
if(floatval($OffersStats_MNTH["MonthSend"]) <= 4 && floatval($OffersStats_MNTH["MonthEarning"]) > 1 )
|
|
{
|
|
$OffersNewForClean[] = $OffersStats_MNTH;
|
|
|
|
}
|
|
|
|
}
|
|
$OffersMonthReal = array_slice($OffersMonthReal, 0, 3);
|
|
$OffersNewForActive = array_slice($OffersNewForActive, count($OffersNewForActive)-4, count($OffersNewForActive));
|
|
$OffersNewForClean = array_slice($OffersNewForClean, 0, 4);
|
|
$OffersStatsMONTHSORT = array_slice($OffersStatsMONTHSORT, 0, 3);
|
|
$DataLeads = [];
|
|
foreach($DataList as $Data) {
|
|
|
|
$MonthLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("list_id = ? and action_time BETWEEN current_date - interval '30' day AND current_date ",[$Data["id"]])
|
|
->first();
|
|
$WeekLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("list_id = ? and action_time BETWEEN current_date - interval '7' day AND current_date ",[$Data["id"]])
|
|
->first();
|
|
$YesterdayLeads = $this->app->database('system')->query()
|
|
->from('actions.leads',['COALESCE(SUM(payout),0)' => 'earnings','COUNT(1)' => 'conversions'])
|
|
->where("list_id = ? and action_time BETWEEN current_date - interval '1' day AND current_date ",[$Data["id"]])
|
|
->first();
|
|
|
|
$TopOfferID = $this->app->database('system')->query()
|
|
->from('actions.leads',['offer_production_id' => 'offer_production_id','COUNT(1)' => 'conversions'])
|
|
->where("list_id = ? ",[$Data["id"]])
|
|
->group(["offer_production_id"])
|
|
->order("COUNT(1)")
|
|
->last();
|
|
$TopOffer = $this->app->database('system')->query()
|
|
->from('affiliate.offers',['name' => 'name'])
|
|
->where("production_id = ?",[$TopOfferID["offer_production_id"]])
|
|
->first();
|
|
$OfferName = $TopOffer["name"];
|
|
if($TopOffer)
|
|
{
|
|
$OfferName = "<b style='color:#24695c'>".$TopOfferID["offer_production_id"]." </b>| ".$OfferName ;
|
|
}
|
|
|
|
$DataLeads[]= array("Month"=>$MonthLeads["earnings"],"TopOffer"=>$OfferName, "Week"=>$WeekLeads["earnings"], "Yesterday"=>$YesterdayLeads["earnings"], "name"=>$Data["name"],"id"=>$Data["id"]);
|
|
|
|
};
|
|
$TotalSend = 0 ;
|
|
foreach($Sponsors as $Sponsor) {
|
|
$TotalSend = $TotalSend + $Sponsor["MonthSend"];
|
|
}
|
|
$SponsorsReal = [];
|
|
foreach($Sponsors as $Sponsor) {
|
|
|
|
$Sponsor["PercentageDSend"] = round(((100 / $TotalSend) *$Sponsor["MonthSend"] ),2);
|
|
$SponsorsReal[] = $Sponsor ;
|
|
}
|
|
$this->pageView->set([
|
|
'DataList' => $DataList,
|
|
'DataLeads' => $DataLeads,
|
|
'OffersStats_Month' => $OffersMonthReal,
|
|
'OffersStats_Week' => $OffersStatsWEEKSORT,
|
|
'Sponsors' => $SponsorsReal,
|
|
'OffersStats_Yesterday' => $OffersStatsYESTERDAYSORT,
|
|
'OffersNewForActive' => $OffersNewForActive,
|
|
'OffersNewForClean' => $OffersNewForClean
|
|
]);
|
|
}
|
|
/**
|
|
* @name closeConnections
|
|
* @description close all connections
|
|
* @once
|
|
* @protected
|
|
*/
|
|
public function closeConnections()
|
|
{
|
|
# connect to the database
|
|
$this->app->database('system')->disconnect();
|
|
$this->app->database('clients')->disconnect();
|
|
}
|
|
|
|
/**
|
|
* @name checkForMessage
|
|
* @description checks for session messages
|
|
* @once
|
|
* @protected
|
|
*/
|
|
public function checkForMessage()
|
|
{
|
|
# check for message
|
|
Page::checkForMessage($this);
|
|
}
|
|
|
|
|
|
public function showNotif(string $flag, string $message)
|
|
{
|
|
Page::registerMessage("$flag", "$message");
|
|
$_SESSION['exp_message'] = $message;
|
|
$_SESSION['exp_flag'] = $flag;
|
|
}
|
|
public function Convert($Value)
|
|
{
|
|
|
|
if($Value >= 1000 && $Value < 1000000 )
|
|
{
|
|
$Value = $Value/1000 ;
|
|
$Value = round($Value,2) ;
|
|
$Value = $Value."K";
|
|
}
|
|
if($Value >= 1000000)
|
|
{
|
|
$Value = $Value/1000000 ;
|
|
$Value = round($Value,2) ;
|
|
$Value = $Value."M";
|
|
}
|
|
return $Value ;
|
|
}
|
|
|
|
public function getDuration($day)
|
|
{
|
|
$now = date("d"); // or your date as well
|
|
if ($day >= $now) {
|
|
$datediff = $day - $now;
|
|
return round($datediff);
|
|
}
|
|
// echo "false $day > $now <br>";
|
|
return false;
|
|
}
|
|
} |