<?php

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

$app->checkToken();


if (strtoupper(substr(PHP_OS,0,3)=='WIN')) {
    $eol="\r\n";
} elseif (strtoupper(substr(PHP_OS,0,3)=='MAC')) {
    $eol="\r";
} else {
    $eol="\n";
}
$data = '';

$id_campaign = isset($_POST['id_campaign']) ? (int)$_POST['id_campaign'] : 0;
$type = $_POST['type'];
$filename = date("Y-m-d_His")."-".$type.".csv";

// PREFIX_campaign_rec_conn.status:
$status = Array(
    0 => 'prepared',
    1 => 'sent',
    2 => 'opened',
    3 => 'unsubscribed',
    4 => 'not valid email address || sending error',
    5 => 'sending process stared'
);

// set charset for exported data
$db->query(" SET NAMES ".Settings::$charset."; ");

// open
if ($type == 'open') {
    $result = $db->query("
        SELECT r.person, r.email FROM ".PREF."_campaign_rec_conn AS crc
        LEFT JOIN ".PREF."_recipient AS r ON r.id = crc.id_recipient
        WHERE crc.id_campaign = ".$id_campaign." and crc.status IN('2','3')
        ");
    foreach ($result as $row) {
        $data .= $row['person'].';'.$row['email'];
        $data .= $eol;
    }
}

// not-open
if ($type == 'not-open') {
    $result = $db->query("
        SELECT r.person, r.email FROM ".PREF."_campaign_rec_conn AS crc
        LEFT JOIN ".PREF."_recipient AS r ON r.id = crc.id_recipient
        WHERE crc.id_campaign = ".$id_campaign." and crc.status IN('1')
        ");
    foreach ($result as $row) {
        $data .= $row['person'].';'.$row['email'];
        $data .= $eol;
    }
}

// unsubscribed
if ($type == 'unsubscribed') {
    $result = $db->query("
        SELECT r.person, r.email FROM ".PREF."_campaign_rec_conn AS crc
        LEFT JOIN ".PREF."_recipient AS r ON r.id = crc.id_recipient
        WHERE crc.id_campaign = ".$id_campaign." and crc.status IN('3')
        ");
    foreach ($result as $row) {
        $data .= $row['person'].';'.$row['email'];
        $data .= $eol;
    }
}

// clicked
if ($type == 'clicked') {
    $result = $db->query("
        SELECT r.person, r.email, crc.clicked FROM ".PREF."_campaign_rec_conn AS crc
        LEFT JOIN ".PREF."_recipient AS r ON r.id = crc.id_recipient
        WHERE crc.id_campaign = ".$id_campaign." and crc.clicked != '0' 
        ORDER BY crc.clicked DESC
        ");
    foreach ($result as $row) {
        $data .= $row['person'].';'.$row['email'].';'.$row['clicked'];
        $data .= $eol;
    }
}

// all
if ($type == 'all') {
    $result = $db->query("
        SELECT * 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 crc.id_campaign = ".$id_campaign."
        ");
    $header = 'date_sent;status;bounced;clicked;IP;person;email;comment;www;user_agent;city;country;latitude;longitude;region'.$eol;
    foreach ($result as $row) {
        $data .= $row['date_sent'].';'
                .$status[$row['status']].';'
                .($row['bounced'] == 0 ? 'No' : 'Yes').';'
                .$row['clicked'].';'
                .$row['ip_receiver'].';'
                .$row['person'].';'
                .$row['email'].';'
                .$row['comment'].';'
                .$row['website'].';'
                .stripslashes(str_replace(';',' ',$row['user_agent'])).';'
                .$row['city'].';'
                .$row['country_name'].';'
                .$row['latitude'].';'
                .$row['longitude'].';'
                .$row['region_name'].';'
                ;
        $data .= $eol;
    }
    $data = $header.$data;
}

// country
if ($type == 'country') {
    $result = $db->query("
        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 = '".$id_campaign."'
        GROUP BY 
            g.country_name 
        ORDER BY
            qty DESC
        ");
    foreach ($result as $row) {
        $data .= $row['country_name'].';'
                .$row['qty'].';'
                ;
        $data .= $eol;
    }
    $data = $data;
}

// city
if ($type == 'city') {
    $result = $db->query("
        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 = '".$id_campaign."'
        GROUP BY 
            g.city 
        ORDER BY 
            qty DESC 
        ");
    foreach ($result as $row) {
        $data .= $row['city'].';'
                .$row['qty'].';'
                ;
        $data .= $eol;
    }
    $data = $data;
}

// browser
if ($type == 'browser') {
    $result = $db->query("
        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 = '".$id_campaign."'
        GROUP BY 
            g.user_agent 
        ORDER BY 
            qty DESC 
        ");
    foreach ($result as $row) {
        $data .= stripslashes(str_replace(';',' ',$row['user_agent'])).';'
                .$row['qty'].';'
                ;
        $data .= $eol;
    }
    $data = $data;
}

// campaign_list
if ($type == 'campaign_list') {
    $result = $db->query("
        SELECT
            c.id,
            c.name,
            c.recipient_qty,
            c.id_template,
            c.status,
            c.date_create,
            c.date_start,
            (
                SELECT count(crc.id_recipient) FROM ".PREF."_campaign_rec_conn AS crc WHERE crc.id_campaign = c.id and crc.status IN('2','3')
            ) AS receivers,
            (
                SELECT count(crc.id_recipient) FROM ".PREF."_campaign_rec_conn AS crc WHERE crc.id_campaign = c.id and crc.bounced='1'
            ) AS bounced,
            (
                SELECT count(crc.clicked) FROM ".PREF."_campaign_rec_conn AS crc WHERE crc.id_campaign = c.id and crc.clicked != '0'
            ) AS clicked,
            (
                SELECT sum(crc.clicked) FROM ".PREF."_campaign_rec_conn AS crc WHERE crc.id_campaign = c.id
            ) AS clicks,
            (
                SELECT count(crc.id_recipient) FROM ".PREF."_campaign_rec_conn AS crc WHERE crc.id_campaign = c.id and crc.status='3'
            ) AS unsubscribers,
            (
                SELECT count(crc.id_recipient) FROM ".PREF."_campaign_rec_conn AS crc WHERE crc.id_campaign = c.id and crc.status='4'
            ) AS errors,
            (
                SELECT t.thumb_filename FROM ".PREF."_template t WHERE t.id = c.id_template
            ) AS filename
        FROM 
            ".PREF."_campaign AS c
        ORDER BY id DESC
        ");
    $header = 'name;recipients;status;created;started;opens;bounced;unique clicks;total clicks;unsubscribed'.$eol;
    foreach ($result as $row) {
        switch ($row['status']) {
        case '0':
            $statusTxt = C_PREPARED;
            break;
        case '1':
            $statusTxt = C_AJAX_PROGRESS;
            break;
        case '2':
            $statusTxt = C_FINISHED;
            break;
        case '3':
            $statusTxt = C_BG_PROGRESS;
            break;
        case '4':
            $statusTxt = C_PAUSED;
            break;
        case '5':
            $statusTxt = C_CRON;
            break;
        }
        $data .= $row['name'].';'
                .$row['recipient_qty'].';'
                .$statusTxt.';'
                .$row['date_create'].';'
                .$row['date_start'].';'
                .$row['receivers'].';'
                .$row['bounced'].';'
                .$row['clicked'].';'
                .$row['clicks'].';'
                .$row['unsubscribers']
                ;
        $data .= $eol;
    }
    $data = $header.$data;
}

// blacklist
if ($type == 'blacklist') {
    $result = $db->query("
        SELECT * FROM ".PREF."_blacklist
        ");
    foreach ($result as $row) {
        $data .= $row['value'].';'.($row['type'] == '1' ? 'domain' : 'ip');
        $data .= $eol;
    }
}

header('Content-Description: File Transfer'.$eol);
header('Content-Type: application/octet-stream'.$eol);
header('Content-Disposition: attachment; filename='.$filename.$eol);
header('Expires: 0'.$eol);
header('Cache-Control: must-revalidate'.$eol);
header('Pragma: public'.$eol);
header('Content-Length: '.strlen($data).$eol);
echo $data;
exit();
