<?php

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

$emails_qty = 0;
$templates_qty = 0;
$sent_qty = 0;
$effectiveness = 0;
$campaign_all_qty = 0;
$campaign_pending_qty = 0;
$campaign_sent_qty = 0;
$campaign_create_month_qty = '';
$campaign_start_month_qty = '';
$best_camps = '';

// check db parms
if (Settings::$dbHost == '' || Settings::$dbUser == '' || Settings::$dbName == '' || Settings::$tbPrefix == '') {
    echo '<br>Run <a href="install.php"><b>install.php</b></a> to configure your database.';
    exit;
}

// check db structure
$dbErrors = '';
$tables = array(
    PREF."_blacklist",
    PREF."_campaign",
    PREF."_campaign_rec_conn",
    PREF."_geolocation",
    PREF."_recipient",
    PREF."_recipient_bridge",
    PREF."_recipient_tag",
    PREF."_recipient_tag_conn",
    PREF."_sender",
    PREF."_smtp",
    PREF."_template",
    PREF."_template_att",
    PREF."_template_att_conn",
    PREF."_template_thumb",
    PREF."_verify_bulk",
);
foreach ($tables as $table) {
    $sql = "
        SHOW TABLES LIKE '".$table."'
    ";
    $stmt = $db->query($sql);
    $stmt->execute();
    $row = $stmt->fetchAll();
    if (! count($row)) {
        $dbErrors .= 'WARNING! Table <b>'.$table.'</b> not exists!<br>';
    }
}
if ($dbErrors != '') {
    echo $dbErrors.'<br>Run <a href="install.php"><b>install.php</b></a> to prepare database structure.';
    exit;
}


// check data for this year, default is current
$statistic_year = isset($_POST['statistic_year']) ? $_POST['statistic_year'] : @date("Y");

$stmt = $db->query(" SELECT count(id) AS qty FROM ".PREF."_recipient ");
$stmt->execute();
$row = $stmt->fetchAll();
$emails_qty = $row[0]['qty'];

$stmt = $db->query(" SELECT count(id) AS qty FROM ".PREF."_template ");
$stmt->execute();
$row = $stmt->fetchAll();
$templates_qty = $row[0]['qty'];

$stmt = $db->query(" SELECT count(status) AS qty FROM ".PREF."_campaign_rec_conn WHERE status > 0 AND year(date_sent) = '".$statistic_year."' ");
$stmt->execute();
$row = $stmt->fetchAll();
$sent_qty = (int)$row[0]['qty'];

$stmt = $db->query(" SELECT count(status) AS qty FROM ".PREF."_campaign_rec_conn WHERE status > 1 ");
$stmt->execute();
$row = $stmt->fetchAll();
$effectiveness = $sent_qty != 0 ? ((int)($row[0]['qty'])/($sent_qty) * 100) : 0;
$effectiveness = number_format($effectiveness, 2, ',', '').'%';

$stmt = $db->query(" SELECT count(id) AS qty FROM ".PREF."_campaign WHERE 1 and year(date_create) = '".$statistic_year."' ");
$stmt->execute();
$row = $stmt->fetchAll();
$campaign_all_qty = $row[0]['qty'];

$stmt = $db->query(" SELECT count(id) AS qty FROM ".PREF."_campaign WHERE status = '0' and year(date_create) = '".$statistic_year."' ");
$stmt->execute();
$row = $stmt->fetchAll();
$campaign_pending_qty = $row[0]['qty'];

$stmt = $db->query(" SELECT count(id) AS qty FROM ".PREF."_campaign WHERE status = '2' and year(date_create) = '".$statistic_year."' ");
$stmt->execute();
$row = $stmt->fetchAll();
$campaign_sent_qty = $row[0]['qty'];

$result = $db->query(" 
    SELECT * FROM
    (
        SELECT
            c.id,
            c.name,
            c.recipient_qty,
            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='2') / c.recipient_qty) * 100
            ) AS procent
        FROM 
            ".PREF."_campaign AS c
        WHERE 1
            and c.status = '2'
            and year(c.date_create) = '".$statistic_year."'
    ) AS camp
    ORDER BY procent DESC limit 5
");
foreach ($result as $row) {
    $best_camps .='
        <div>
            <p>'.$row['name'].' ('.ceil((int)$row['procent']).'%)</p>
            <div class="">
                <div class="progress progress_sm w80">
                <div class="progress-bar bg-green" role="progressbar" data-transitiongoal="'.(int)$row['procent'].'"></div>
            </div>
            </div>
        </div>
    ';
}

$stmt = $db->query(" 
    SELECT * FROM
    (
        (SELECT count(id) AS sty FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 1) AS a,
        (SELECT count(id) AS lut FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 2) AS b,
        (SELECT count(id) AS mar FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 3) AS c,
        (SELECT count(id) AS kwi FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 4) AS d,
        (SELECT count(id) AS maj FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 5) AS e,
        (SELECT count(id) AS cze FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 6) AS f,
        (SELECT count(id) AS lip FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 7) AS g,
        (SELECT count(id) AS sie FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 8) AS h,
        (SELECT count(id) AS wrz FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 9) AS i,
        (SELECT count(id) AS paz FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 10) AS j,
        (SELECT count(id) AS lis FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 11) AS k,
        (SELECT count(id) AS gru FROM ".PREF."_campaign WHERE year(date_create) = '".$statistic_year."' and month(date_create) = 12) AS l
    )
");
$stmt->execute();
$row = $stmt->fetchAll();
$campaign_create_month_qty = $row[0]['sty'].','.$row[0]['lut'].','.$row[0]['mar'].','.$row[0]['kwi'].','.$row[0]['maj'].','.$row[0]['cze'].','.$row[0]['lip'].','.$row[0]['sie'].','.$row[0]['wrz'].','.$row[0]['paz'].','.$row[0]['lis'].','.$row[0]['gru'];

$stmt = $db->query(" 
    SELECT * FROM
    (
        (SELECT count(id) AS sty FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 1) AS a,
        (SELECT count(id) AS lut FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 2) AS b,
        (SELECT count(id) AS mar FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 3) AS c,
        (SELECT count(id) AS kwi FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 4) AS d,
        (SELECT count(id) AS maj FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 5) AS e,
        (SELECT count(id) AS cze FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 6) AS f,
        (SELECT count(id) AS lip FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 7) AS g,
        (SELECT count(id) AS sie FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 8) AS h,
        (SELECT count(id) AS wrz FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 9) AS i,
        (SELECT count(id) AS paz FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 10) AS j,
        (SELECT count(id) AS lis FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 11) AS k,
        (SELECT count(id) AS gru FROM ".PREF."_campaign WHERE year(date_start) = '".$statistic_year."' and month(date_start) = 12) AS l
    )
");
$stmt->execute();
$row = $stmt->fetchAll();
$campaign_start_month_qty = $row[0]['sty'].','.$row[0]['lut'].','.$row[0]['mar'].','.$row[0]['kwi'].','.$row[0]['maj'].','.$row[0]['cze'].','.$row[0]['lip'].','.$row[0]['sie'].','.$row[0]['wrz'].','.$row[0]['paz'].','.$row[0]['lis'].','.$row[0]['gru'];

?>
<div class="row tile_count">
    <div class="animated flipInY col-md-2 col-sm-4 col-xs-4 tile_stats_count">
        <div class="left"></div>
        <div class="right">
            <a class="dashbox" href="index.php?manage=recipient&action=add">
                <span class="count_top"><i class="fa fa-user"></i> <?php echo D_EMAIL_ADDRESS;?></span>
                <div class="count text-center"><?php echo $emails_qty; ?></div>
                <button class="btn btn-success btn-xs"><?php echo D_EMAIL_ADD_NEW;?></button>
            </a>
        </div>
    </div>
    <div class="animated flipInY col-md-2 col-sm-4 col-xs-4 tile_stats_count">
        <div class="left"></div>
        <div class="right">
            <a class="dashbox" href="index.php?manage=templates&action=add">
                <span class="count_top"><i class="fa fa-desktop"></i> <?php echo D_EMAIL_TEMPLATES;?></span>
                <div class="count text-center"><?php echo $templates_qty; ?></div>
                <button class="btn btn-success btn-xs" href=""><?php echo D_EMAIL_TEMPLATES_ADD;?></button>
            </a>
        </div>
    </div>
    <div class="animated flipInY col-md-2 col-sm-4 col-xs-4 tile_stats_count">
        <div class="left"></div>
        <div class="right">
            <a class="dashbox" href="index.php?manage=campaign&action=sent">
                <span class="count_top"><i class="fa fa-envelope-o"></i> <?php echo D_EMAIL_SENT;?></span>
                <div class="count green text-center"><?php echo $sent_qty; ?></div>
                <button class="count_bottom btn btn-default btn-xs" ><i class="green"><?php echo $effectiveness; ?> </i> <?php echo D_EMAIL_EFFICIENCY;?></button>
            </a>
        </div>
    </div>
    <div class="animated flipInY col-md-2 col-sm-4 col-xs-4 tile_stats_count">
        <div class="left"></div>
        <div class="right">
            <a class="dashbox" href="index.php?manage=campaign&action=prepare">
                <span class="count_top"><i class="fa fa-table"></i> <?php echo D_CAMPAIGNS;?></span>
                <div class="count text-center"><?php echo $campaign_all_qty; ?></div>
                <button class="btn btn-success btn-xs"><?php echo D_CAMPAIGNS_ADD;?></button>
            </a>
        </div>
    </div>
    <div class="animated flipInY col-md-2 col-sm-4 col-xs-4 tile_stats_count">
        <div class="left"></div>
        <div class="right">
            <a class="dashbox" href="index.php?manage=campaign&action=waiting">
                <span class="count_top"><i class="fa fa-table"></i> <?php echo D_CAMPAIGNS;?></span>
                <div class="count text-center"><?php echo $campaign_pending_qty; ?></div>
                <button class="btn btn-success btn-xs"><?php echo D_CAMPAIGNS_WAITING;?></button>
            </a>
        </div>
    </div>
    <div class="animated flipInY col-md-2 col-sm-4 col-xs-4 tile_stats_count">
        <div class="left"></div>
        <div class="right">
            <a class="dashbox" href="index.php?manage=campaign&action=sent">
                <span class="count_top"><i class="fa fa-table"></i> <?php echo D_CAMPAIGNS;?></span>
                <div class="count text-center"><?php echo $campaign_sent_qty; ?></div>
                <button class="btn btn-success btn-xs"><?php echo D_CAMPAIGNS_SENT;?></button>
            </a>
        </div>
    </div>
</div>

<div class="row">
    <div class="col-md-12 col-sm-12 col-xs-12">
        <div class="dashboard_graph">
            <div class="row x_title">
                <div class="col-md-12">
                    <form class="form-horizontal" action="" method="post">
                        <h3>
                            <div class="col-sm-6">
                            <?php echo D_STATISTICS;?> <?php echo $statistic_year; ?> <small><?php echo D_THIS_YEAR;?></small>
                            </div>
                            <div class="col-sm-6">
                                <div class="col-sm-3">
                                    <input class="form-control col-sm-2" type="number" name="statistic_year" value="<?php echo $statistic_year;?>">
                                </div>
                                <div class="col-sm-2">
                                    <input class="btn btn-default" type="submit" value="go">
                                </div>
                            </div>
                        </h3>
                    </form>
                </div>
            </div>
            <div class="col-md-7 col-sm-7 col-xs-12">
                <div id="placeholder33" class="demo-placeholder"></div>
                <div class="w100">
                    <canvas id="canvas000"></canvas>
                    <div id="legendDiv"></div>
                </div>
            </div>
            <div class="col-md-5 col-sm-5 col-xs-12 bg-white">
                <div class="x_title">
                    <h2><?php echo D_EMAIL_EFFICIENCY;?></h2>
                    <div class="clearfix"></div>
                </div>
                <div class="col-md-12 col-sm-12 col-xs-6">
                    <?php echo $best_camps; ?>
                    <p><a class="btn btn-primary btn-xs" href="index.php?manage=campaign&action=sent"><?php echo D_CHECK_ALL;?></a></p>
                </div>
            </div>
            <div class="clearfix"></div>
        </div>
    </div>
</div>

<p>&nbsp;</p>

<div class="row">
    <div class="col-md-12 col-sm-12 col-xs-12">
        <div class="x_panel">
            <div class="x_title">
                <h2><?php echo D_HOW_TO;?></h2>
                <div class="clearfix"></div>
            </div>
            <div class="x_content">
                <div class="dashboard-widget-content">
                    <ul class="list-unstyled timeline widget">
                        <li>
                            <div class="block">
                                <div class="block_content">
                                    <h2 class="title"><?php echo D_HOW_STEP_1_TITLE;?></h2>
                                    <p class="excerpt"><?php echo D_HOW_STEP_1_DESC;?></p>
                                </div>
                            </div>
                        </li>
                        <li>
                            <div class="block">
                                <div class="block_content">
                                    <h2 class="title"><?php echo D_HOW_STEP_2_TITLE;?></h2>
                                    <p class="excerpt"><?php echo D_HOW_STEP_2_DESC;?></p>
                                </div>
                            </div>
                        </li>
                        <li>
                            <div class="block">
                                <div class="block_content">
                                    <h2 class="title"><?php echo D_HOW_STEP_3_TITLE;?></h2>
                                    <p class="excerpt"><?php echo D_HOW_STEP_3_DESC;?></p>
                                </div>
                            </div>
                        </li>
                        <li>
                            <div class="block">
                                <div class="block_content">
                                    <h2 class="title"><?php echo D_HOW_STEP_4_TITLE;?></h2>
                                    <p class="excerpt"><?php echo D_HOW_STEP_4_DESC;?></p>
                                </div>
                            </div>
                        </li>
                        <li>
                            <div class="block">
                                <div class="block_content">
                                    <h2 class="title"><?php echo D_HOW_STEP_5_TITLE;?></h2>
                                    <p class="excerpt"><?php echo D_HOW_STEP_5_DESC;?></p>
                                </div>
                            </div>
                        </li>
                    </ul>
                </div>
            </div>
        </div>
    </div>
</div>

<p>&nbsp;</p>
<p>&nbsp;</p>

<div 
    id="campaign_qty"
    class="hidden" 
    data-campaign_create_month_qty="<?php echo $campaign_create_month_qty; ?>"
    data-campaign_start_month_qty="<?php echo $campaign_start_month_qty; ?>"
></div>

<script src="components/default/view.html.js"></script>
