<?php
/*
 * get ajax recipient list
 * return json datatable format
 */

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

$app->checkToken();

$recipients = '';
$draw = (int)($_POST['draw']);
$length = (int)($_POST['length']);
$start = (int)($_POST['start']);
$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'];
$tags = $app->clearSQL($_POST['tags']);
$logic = $app->clearSQL($_POST['logic']);

if ($fraza != ''){
    $filtr = '';
    $fraza_array = explode(" ", $fraza);
    foreach ($fraza_array as $val) {
        $filtr .= " AND (r.person LIKE '%".$val."%' OR r.email LIKE '%".$val."%' OR r.comment LIKE '%".$val."%' OR r.website LIKE '%".$val."%') ";
    }
} else {
    $filtr = "";
}

// get count of all recipients
$stmt = $db->query(" SELECT count(id) as qty from ".PREF."_recipient ");
$row = $stmt->fetchAll();
$recordsTotal = $row[0]['qty'];

// get count of filtered recipients
if ($tags != '') {
    $logicFilter = '';
    if ($logic == 'OR') {
        $logicFilter = " AND rtc.id_tag in (".$tags.") ";
    } else {
        $tagsArray = explode(',', $tags);
        $logicFilter = " group by rtc.id_recipient having count(rtc.id_recipient) = ".count($tagsArray);
    }
    $sql = "
        select count(id) as qty from (
            select 
                distinct(rtc.id_recipient) as id 
            from ".PREF."_recipient_tag_conn as rtc 
                left join ".PREF."_recipient as r on r.id=rtc.id_recipient 
            where 1
                AND rtc.id_tag in (".$tags.")
                ".$filtr."
                ".$logicFilter."
        ) as a
    ";
} else {
    $sql = "
        select count(id) as qty from ".PREF."_recipient as r where 1 ".$filtr."
    ";
}


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



$sqlFields = "
            distinct(r.id),
            (
                concat(
                    '<a href=\'index.php?manage=recipient&amp;action=edit&amp;id=',
                    r.id,
                    '\'>',
                    r.person,
                    '</a>'
                )
            ) as person,
            (
                concat(
                    '<a href=\'index.php?manage=recipient&amp;action=edit&amp;id=',
                    r.id,
                    '\'>',
                    r.email,
                    '</a>'
                )
            ) as email,
            r.comment,
            r.website,
            (
                concat(
                    '<button class=\'btn btn-danger btn-xs pull-right\' data-toggle=\'modal\' data-target=\'#recipientDelete\' data-recipientname=\'',
                    r.person,
                    '\' data-recipientid=\'',
                    r.id,
                    '\' data-recipientemail=\'',
                    r.email,
                    '\'>".DELETE."</button>'
                )
            ) as button,
            (
                SELECT group_concat(rt.name SEPARATOR ', ') AS tags 
                FROM ".PREF."_recipient_tag_conn AS rc 
                LEFT JOIN ".PREF."_recipient_tag AS rt ON(rc.id_tag=rt.id)
                WHERE r.id=rc.id_recipient
            ) AS tag
";


// get data of recipients
if ($tags != '') {
    if ($logic == 'OR') {
        $logicFilter = " AND rtc.id_tag in (".$tags.") ";
    } else {
        $logicFilter = " group by rtc.id_recipient having count(rtc.id_recipient) = ".count($tagsArray);
    }
    $sql = "
        
            select 
                ".$sqlFields." 
            from ".PREF."_recipient_tag_conn as rtc 
                left join ".PREF."_recipient as r on r.id=rtc.id_recipient 
            where 1
                AND rtc.id_tag in (".$tags.") 
                ".$filtr."
                ".$logicFilter."
        
        ORDER BY ".$orderColumnName." ".$orderDir."
        LIMIT $length OFFSET $start
    ";
} else {
    $sql = "
        select 
            ".$sqlFields." 
        from 
            ".PREF."_recipient as r
        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();