<?php

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

$app->checkToken();

$recipients = '';
$draw = (int)($_POST['draw']);
$length = (int)($_POST['length']);
$start = (int)($_POST['start']);
$id_campaign = (int)($_POST['id_campaign']);
$search = $app->clearSQL($_POST['search']);
$order = $_POST['order'];
$columns = $_POST['columns'];
$orderDir = $app->clearSQL($order['0']['dir']);
$orderColumnName = $app->clearSQL($columns[$order['0']['column']]['data']);
$recordsTotal = 0;
$recordsFiltered = 0;
$fraza = $search['value'];
$recipient_status = $_POST['recipient_status'];


/*
    status
    0 - prepared
    1 - sent
    2 - opened
    3 - unsubscribed

*/

/*all data*/
if ($recipient_status == '0') {
    $recipient_status_sql = "";
}
/*sent*/
if ($recipient_status == '1') {
    $recipient_status_sql = " AND crc.status = '1' ";
}
/*opened (and unsubscribed)*/
if ($recipient_status == '2') {
    $recipient_status_sql = " AND crc.status IN('2', '3') ";
}
/*not opened yet*/
if ($recipient_status == '3') {
    $recipient_status_sql = " AND crc.status = '3' ";
}



if ($fraza != ''){
    $filtr = '';
    $fraza_array = explode(" ", $fraza);
    foreach ($fraza_array as $val) {
        $filtr .= " AND (person LIKE '%".$val."%' OR clicked LIKE '%".$val."%' OR software LIKE '%".$val."%' OR geodata LIKE '%".$val."%') ";
    }
} else {
    $filtr = "";
}

// get count of all recipients
$stmt = $db->query("
    SELECT 
        COUNT(crc.id_campaign) AS qty 
    FROM 
        ".PREF."_campaign_rec_conn AS crc
        LEFT JOIN ".PREF."_recipient AS r ON(r.id = crc.id_recipient)
    WHERE 1
        AND crc.id_campaign = '".$id_campaign."' 
        AND r.id is not null
        ".$recipient_status_sql."
");
$row = $stmt->fetchAll();
$recordsTotal = $row[0]['qty'];

// get count of filtered recipients
$sql = " 
    SELECT count(person) as qty FROM (
        SELECT
            r.id,
            CONCAT (
                r.person, ' ', r.email, ' ', r.comment, ' ', r.website
            ) AS person,
            IF(clicked=0, 'c".NO."', IF(clicked=1, CONCAT('c".YES."_', clicked), CONCAT('c".YES."=', clicked)) ) AS clicked,
            g.user_agent AS software,
            CONCAT (
                g.country_code, ' ', g.city, ' ', g.region, ' ', g.country_name, ' ', g.region_name
            ) AS geodata
        FROM 
            ".PREF."_campaign_rec_conn AS crc
            LEFT JOIN ".PREF."_recipient AS r ON(r.id = crc.id_recipient)
            LEFT JOIN ".PREF."_geolocation AS g ON(g.ip_receiver = crc.ip_receiver)
        WHERE 1
            AND crc.id_campaign = '".$id_campaign."'
            AND r.id is not null
            ".$recipient_status_sql."
    ) as a
    WHERE 1 ".$filtr."
 ";

$stmt = $db->query($sql);
$row = $stmt->fetchAll();
$recordsFiltered = $row[0]['qty'];

// get recipients to show limited by $length from page $start
$sql = "
    SELECT * FROM (
        SELECT
            r.id,
            CONCAT (
                r.person, ' ', r.email, ' ', r.comment, ' ', r.website
            ) AS person,
            IF(clicked=0, 'c".NO."', IF(clicked=1, CONCAT('c".YES."_', clicked), CONCAT('c".YES."=', clicked)) ) AS clicked,
            g.user_agent AS software,
            CONCAT (
                g.country_code, ' ', g.country_name, ' ', g.city, ' ', g.region, ' ', g.region_name
            ) AS geodata
        FROM 
            ".PREF."_campaign_rec_conn AS crc
            LEFT JOIN ".PREF."_recipient AS r ON(r.id = crc.id_recipient)
            LEFT JOIN ".PREF."_geolocation AS g ON(g.ip_receiver = crc.ip_receiver)
        WHERE 1
            AND crc.id_campaign = '".$id_campaign."'
            AND r.id is not null
            ".$recipient_status_sql."
    ) as a
    WHERE 1 ".$filtr."
    ORDER BY ".$orderColumnName." ".$orderDir."
    LIMIT $length OFFSET $start
";
$stmt = $db->query($sql);
$data = $stmt->fetchAll();

$json_data = '
    {
      "draw": '.$draw.',
      "recordsTotal": '.$recordsTotal.',
      "recordsFiltered": '.$recordsFiltered.',
      "data":
        '.json_encode($data).'
    }
';
    
echo $json_data;
exit();
