<?php

defined('_MAILING') or die('Restricted access');

$total_user_agents = 0;
$percent = 0;
$percent_format = '';
$percent_sum = 100;
$country_percent_sum = 100;
$country_name = '';
$city_percent_sum = 100;
$city_name = '';
$most_clicks = '';

/*total values*/
$stmt = $db->query("
    SELECT 
        COUNT(g.user_agent) AS qty 
    FROM 
        ".PREF."_geolocation AS g
        LEFT JOIN ".PREF."_campaign_rec_conn AS crc ON(g.ip_receiver = crc.ip_receiver)
    WHERE
        crc.id_campaign = '".$campaign_id."'
");

$stmt->execute();
$row = $stmt->fetchAll();
$total_user_agents = $row[0]['qty'];



/*recipient user_agent details*/
$sql = "
    SELECT
        g.user_agent, count(g.user_agent) AS qty 
    FROM 
        ".PREF."_geolocation AS g 
        LEFT JOIN ".PREF."_campaign_rec_conn AS crc ON(g.ip_receiver = crc.ip_receiver)
    WHERE
        crc.id_campaign = '".$campaign_id."'
    GROUP BY 
        g.user_agent 
    ORDER BY 
        qty DESC 
    LIMIT 15
";
$result = $db->query($sql);
$user_agent = '
    <table class="table table-striped">
    <tbody>
';
foreach ($result as $row) {
    $percent = ((int)($row['qty']) / $total_user_agents) * 100;
    $percent_format = number_format($percent, 1, ',', '');
    $percent_sum = $percent_sum - $percent;
    $user_agent .= "
        <tr>
            <td>".$percent_format."%</td>
            <td>".htmlspecialchars($row['user_agent'])."</td>
        </tr>
    ";
}
$user_agent .= '
        <tr>
            <th>'.abs(number_format($percent_sum, 1, ',', '')).'%</th>
            <th>'.STATISTICS_OTHER_UA.'</th>
        </tr>
    </tbody>
    </table>
';



/*recipient country_name details*/
$sql = "
    SELECT 
        g.country_name, COUNT(g.country_name) AS qty 
    FROM 
        ".PREF."_geolocation AS g 
        LEFT JOIN ".PREF."_campaign_rec_conn AS crc ON(g.ip_receiver = crc.ip_receiver)
    WHERE
        crc.id_campaign = '".$campaign_id."'
    GROUP BY 
        g.country_name 
    ORDER BY
        qty DESC
    LIMIT 10
";
$country_name = '
    <table class="table table-striped">
    <tbody>
';
$result = $db->query($sql);
foreach ($result as $row) {
    $country_percent = ((int)($row['qty']) / $total_user_agents) * 100;
    $country_percent_format = number_format($percent, 2, ',', '');
    $country_percent_sum = $country_percent_sum - $country_percent;
    $country_name .= "
        <tr>
            <td>".$row['country_name']." (".ceil($country_percent)."%)</td>
        </tr>
    ";
}
$country_name .= '
        <tr>
            <th>'.STATISTICS_OTHERS.' ('.abs(ceil($country_percent_sum)).'%)</th>
        </tr>
    </tbody>
    </table>
';



/*recipient city details*/
$sql = "
    SELECT 
        g.city, count(g.city) as qty 
    FROM 
        ".PREF."_geolocation AS g 
        LEFT JOIN ".PREF."_campaign_rec_conn AS crc ON(g.ip_receiver = crc.ip_receiver)
    WHERE
        crc.id_campaign = '".$campaign_id."'
    GROUP BY 
        g.city 
    ORDER BY 
        qty DESC 
    LIMIT 10
";
$city_name = '
    <table class="table table-striped">
    <tbody>
';
$result = $db->query($sql);
foreach ($result as $row) {
    $city_percent = ((int)($row['qty']) / $total_user_agents) * 100;
    $city_percent_format = number_format($percent, 2, ',', '');
    $city_percent_sum = $city_percent_sum - $city_percent;
    $city_name .= "
        <tr>
            <td>".($row['city'] != '' ? $row['city'] : 'not detected' )." (".ceil($city_percent)."%)</td>
        </tr>
    ";
}
$city_name .= '
        <tr>
            <th>'.STATISTICS_OTHERS.' ('.abs(ceil($city_percent_sum)).'%)</th>
        </tr>
    </tbody>
    </table>
';


/*most clicks*/
$sql = "
    SELECT 
        r.id, r.person, r.email, g.city, g.country_name, crc.clicked
    FROM 
        ".PREF."_campaign_rec_conn AS crc 
        LEFT JOIN ".PREF."_recipient AS r ON(crc.id_recipient = r.id)
        LEFT JOIN ".PREF."_geolocation AS g ON(crc.ip_receiver = g.ip_receiver)
    WHERE 1
        AND crc.id_campaign = '".$campaign_id."'
        AND crc.clicked > 0
    GROUP BY 
        r.id 
    ORDER BY 
        crc.clicked DESC 
    LIMIT 15
";
$most_clicks = '
    <table class="table table-striped">
    <tbody>
';
$result = $db->query($sql);
foreach ($result as $row) {
    $most_clicks .= "
        <tr>
            <td><a href='index.php?manage=recipient&action=edit&id=".$row['id']."' target='_blank'>".$row['person']." (".$row['clicked'].")<br>".$row['country_name']." / ".$row['city']."</a></td>
        </tr>
    ";
}
$most_clicks .= '
        <tr>
            <th>'.TOTAL_CLICKS.' ('.abs(ceil($campaign_total_clicks_qty)).')</th>
        </tr>
    </tbody>
    </table>
';

?>

<div class="row">


    <div class="col-md-3">
        <div class="x_panel tile ">
            <div class="x_title">
                <form action="index.php?manage=statistics&action=export" method="post">
                <h2><?php echo STATISTICS_TOP_COUNTRIES;?></h2>
                    <input type="hidden" name="csrf_token" value="<?php echo $app->getToken();?>">
                    <input type="hidden" name="adata" value="1">
                    <input type="hidden" name="type" value="country">
                    <input type="hidden" name="id_campaign" value="<?php echo $campaign_id;?>">
                    <input type="submit" value="export" class="btn btn-primary btn-xs pull-right">
                </form>
                <div class="clearfix"></div>
            </div>
            <div class="x_content">
                <?php echo $country_name; ?>
            </div>
        </div>
        <div class="x_panel tile ">
            <div class="x_title">
                <form action="index.php?manage=statistics&action=export" method="post">
                <h2><?php echo STATISTICS_TOP_CITIES;?></h2>
                    <input type="hidden" name="csrf_token" value="<?php echo $app->getToken();?>">
                    <input type="hidden" name="adata" value="1">
                    <input type="hidden" name="type" value="city">
                    <input type="hidden" name="id_campaign" value="<?php echo $campaign_id;?>">
                    <input type="submit" value="export" class="btn btn-primary btn-xs pull-right">
                </form>
                <div class="clearfix"></div>
            </div>
            <div class="x_content">
                <?php echo $city_name; ?>
            </div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="x_panel tile ">
            <div class="x_title">
                <form action="index.php?manage=statistics&action=export" method="post">
                <h2><?php echo STATISTICS_TOP_CLICKERS;?></h2>
                    <input type="hidden" name="csrf_token" value="<?php echo $app->getToken();?>">
                    <input type="hidden" name="adata" value="1">
                    <input type="hidden" name="type" value="clicked">
                    <input type="hidden" name="id_campaign" value="<?php echo $campaign_id;?>">
                    <input type="submit" value="export" class="btn btn-primary btn-xs pull-right">
                </form>
                <div class="clearfix"></div>
            </div>
            <div class="x_content">
                <?php echo $most_clicks; ?>
            </div>
        </div>
    </div>

    <div class="col-md-6">
        <div class="x_panel tile ">
            <div class="x_title">
                <form action="index.php?manage=statistics&action=export" method="post">
                <h2><?php echo STATISTICS_TOP_SOFTWARE;?></h2>
                    <input type="hidden" name="csrf_token" value="<?php echo $app->getToken();?>">
                    <input type="hidden" name="adata" value="1">
                    <input type="hidden" name="type" value="browser">
                    <input type="hidden" name="id_campaign" value="<?php echo $campaign_id;?>">
                    <input type="submit" value="export" class="btn btn-primary btn-xs pull-right">
                </form>
                <div class="clearfix"></div>
            </div>
            <div class="x_content">
                <?php echo $user_agent; ?>
            </div>
        </div>
    </div>


</div>