<?php

/*
  MailBeez Automatic Trigger Email Campaigns
  http://www.mailbeez.com

  Copyright (c) 2010 - 2015 MailBeez

  inspired and in parts based on
  Copyright (c) 2003 osCommerce

  Released under the GNU General Public License



 */

///////////////////////////////////////////////////////////////////////////////
///																																					 //
///                 MailBeez Core file - do not edit                         //
///                                                                          //
///////////////////////////////////////////////////////////////////////////////
require_once(MH_DIR_FS_CATALOG . MH_ROOT_PATH . 'common/classes/mailbeez_system.php');


class mailbeez_analytics
{
    var $timeframe_start;
    var $timeframe_end;

    var $limit_module = '';
    var $limit_module_iteration = '';
    var $limit_module_iteration_loopstart = '';
    var $limit_campaign_item_ids = '';
    var $limit_startdate = '';
    var $limit_enddate = '';

    var $limit_module_sql = '';
    var $limit_campaign_items_sql = '';


    // these regexp are also applied during update process
    // see config_queen

    // http://detectmobilebrowsers.com/
    // Regex updated: 9 September 2013
    // preg_match('/' . self::MOBILE_REGEX1 . '/i', $useragent)
    const MOBILE_REGEX1 = '(android|bb\d+|meego).+mobile|avantgo|bada\/|blackberry|blazer|compal|elaine|fennec|hiptop|iemobile|ip(hone|od)|iris|kindle|lge |maemo|midp|mmp|mobile.+firefox|netfront|opera m(ob|in)i|palm( os)?|phone|p(ixi|re)\/|plucker|pocket|psp|series(4|6)0|symbian|treo|up\.(browser|link)|vodafone|wap|windows (ce|phone)|xda|xiino';

    // preg_match('/' . self::MOBILE_REGEX2 . '/i', substr($useragent, 0, 4)
    const MOBILE_REGEX2 = '1207|6310|6590|3gso|4thp|50[1-6]i|770s|802s|a wa|abac|ac(er|oo|s\-)|ai(ko|rn)|al(av|ca|co)|amoi|an(ex|ny|yw)|aptu|ar(ch|go)|as(te|us)|attw|au(di|\-m|r |s )|avan|be(ck|ll|nq)|bi(lb|rd)|bl(ac|az)|br(e|v)w|bumb|bw\-(n|u)|c55\/|capi|ccwa|cdm\-|cell|chtm|cldc|cmd\-|co(mp|nd)|craw|da(it|ll|ng)|dbte|dc\-s|devi|dica|dmob|do(c|p)o|ds(12|\-d)|el(49|ai)|em(l2|ul)|er(ic|k0)|esl8|ez([4-7]0|os|wa|ze)|fetc|fly(\-|_)|g1 u|g560|gene|gf\-5|g\-mo|go(\.w|od)|gr(ad|un)|haie|hcit|hd\-(m|p|t)|hei\-|hi(pt|ta)|hp( i|ip)|hs\-c|ht(c(\-| |_|a|g|p|s|t)|tp)|hu(aw|tc)|i\-(20|go|ma)|i230|iac( |\-|\/)|ibro|idea|ig01|ikom|im1k|inno|ipaq|iris|ja(t|v)a|jbro|jemu|jigs|kddi|keji|kgt( |\/)|klon|kpt |kwc\-|kyo(c|k)|le(no|xi)|lg( g|\/(k|l|u)|50|54|\-[a-w])|libw|lynx|m1\-w|m3ga|m50\/|ma(te|ui|xo)|mc(01|21|ca)|m\-cr|me(rc|ri)|mi(o8|oa|ts)|mmef|mo(01|02|bi|de|do|t(\-| |o|v)|zz)|mt(50|p1|v )|mwbp|mywa|n10[0-2]|n20[2-3]|n30(0|2)|n50(0|2|5)|n7(0(0|1)|10)|ne((c|m)\-|on|tf|wf|wg|wt)|nok(6|i)|nzph|o2im|op(ti|wv)|oran|owg1|p800|pan(a|d|t)|pdxg|pg(13|\-([1-8]|c))|phil|pire|pl(ay|uc)|pn\-2|po(ck|rt|se)|prox|psio|pt\-g|qa\-a|qc(07|12|21|32|60|\-[2-7]|i\-)|qtek|r380|r600|raks|rim9|ro(ve|zo)|s55\/|sa(ge|ma|mm|ms|ny|va)|sc(01|h\-|oo|p\-)|sdk\/|se(c(\-|0|1)|47|mc|nd|ri)|sgh\-|shar|sie(\-|m)|sk\-0|sl(45|id)|sm(al|ar|b3|it|t5)|so(ft|ny)|sp(01|h\-|v\-|v )|sy(01|mb)|t2(18|50)|t6(00|10|18)|ta(gt|lk)|tcl\-|tdg\-|tel(i|m)|tim\-|t\-mo|to(pl|sh)|ts(70|m\-|m3|m5)|tx\-9|up(\.b|g1|si)|utst|v400|v750|veri|vi(rg|te)|vk(40|5[0-3]|\-v)|vm40|voda|vulc|vx(52|53|60|61|70|80|81|83|85|98)|w3c(\-| )|webc|whit|wi(g |nc|nw)|wmlb|wonu|x700|yas\-|your|zeto|zte\-';


// class constructor

    function __construct($parameter = null)
    {
        $this->timeframe_start = (mh_cfg('MAILBEEZ_ANALYTICS_BEGIN_OF_TIME', '2000-01-01 00:00:00'));
        $this->timeframe_end = date('Y-m-d') . ' 23:59:59';

        $statsbar_parameter = array(
            'limit_module', 'limit_startdate_db', 'limit_enddate_db', 'limit_campaign_item_ids'
        );

        // set parameters
        foreach ($statsbar_parameter as $pname) {
            $pvalue = isset($parameter[$pname]) ? $parameter[$pname] : '';

            switch ($pname) {
                case 'limit_module':
                    if (stristr($pvalue, '-')) {
                        if (stristr($pvalue, '-loop')) {
                            list($this->limit_module, $this->limit_module_iteration, , $this->limit_module_iteration_loopstart) = explode('-', $pvalue);
                        } else {
                            list($this->limit_module, $this->limit_module_iteration) = explode('-', $pvalue);
                        }
                    } else {
                        $this->limit_module = $pvalue;
                    }
                    break;

                case 'limit_campaign_item_ids':
                    $this->limit_campaign_item_ids = $pvalue;
                    break;

                case 'limit_startdate_db':
                    $start_date_db = $pvalue;
                    if (mailbeez_system::is_valid_db_date($start_date_db)) {
                        $this->limit_startdate = $start_date_db;
                        $this->timeframe_start = $start_date_db;
                    }
                    break;
                case 'limit_enddate_db':
                    $end_date_db = $pvalue;
                    if (mailbeez_system::is_valid_db_date($end_date_db)) {
                        $this->limit_enddate = $end_date_db;
                        $this->timeframe_end = $end_date_db;
                    }
                    break;
                default:
                    $this->$pname = $pvalue;
                    break;
            }

        }


        if ($this->limit_module) {
            $this->limit_module_sql = " and module = '" . $this->limit_module . "' ";
            if ($this->limit_module_iteration_loopstart) {
                $this->limit_module_sql .= " and iteration >= " . (int)$this->limit_module_iteration_loopstart . "";
            } elseif ($this->limit_module_iteration) {
                $this->limit_module_sql .= " and iteration = " . (int)$this->limit_module_iteration . "";
            }
        }

        if ((int)$this->limit_campaign_item_ids > 0 || (!is_null($this->limit_campaign_item_ids) && stristr($this->limit_campaign_item_ids, ',')) ) {
            if (stristr($this->limit_campaign_item_ids, ',')) {
                $this->limit_campaign_items_sql = " and iteration in (" . $this->limit_campaign_item_ids . ") ";
            } else {
                $this->limit_campaign_items_sql = " and iteration = '" . $this->limit_campaign_item_ids . "' ";
            }
        }
    }

    function mailbeez_analytics($parameter = null)
    {
        self::__construct($parameter);
    }

    function get_engagement_data()
    {

        $query_sql = "select count(*) as cnt_sent,
                        SUM(IF(t.opened > '2000-01-01 00:00:00', 1,0) or IF(t.clicked > '2000-01-01 00:00:00', 1,0)) AS cnt_opened,
                        SUM(IF(t.clicked > '2000-01-01 00:00:00', 1,0)) AS cnt_clicked,
                        SUM(IF(t.bounce_status IS NULL or t.bounce_status not in ('H', 'S'), 1,0) ) AS cnt_delivered
                        from " . TABLE_MAILBEEZ_TRACKING . " t
                      where 
                        date_sent >= '" . $this->timeframe_start . "'
                        and
                        date_sent <= '" . $this->timeframe_end . "'
                        " . $this->limit_module_sql . "
                        " . $this->limit_campaign_items_sql . "
                        " . str_replace('simulation', 't.simulation', MAILBEEZ_SIMULATION_SQL) . " ";

//        echo "<br>Engagement SQL: " . $query_sql;

        $query = mh_db_query($query_sql);
        $result = mh_db_fetch_array($query);

        return array($result['cnt_sent'], $result['cnt_opened'], $result['cnt_clicked'], $result['cnt_delivered']);

    }

    function get_orders_data()
    {


        // slow query
        // joins getting too big to utilize index

//        $SLOW_query_orders_cnt_raw = "select count(distinct mto.orders_id) as cnt_orders
//                                    from " . TABLE_MAILBEEZ_TRACKING_ORDERS . " mto
//                                        join " . TABLE_MAILBEEZ_TRACKING . " mt on (mto.message_id = mt.message_id)
//                                    where
//                                        date_record > '" . $this->timeframe_start . "'
//                                        and
//                                        date_record <= '" . $this->timeframe_end . "'
//                                        " . $this->limit_module_sql . "
//                                        " . $this->limit_campaign_items_sql . " ";


        $simple_query = ($this->limit_module_sql == '' && $this->limit_campaign_items_sql == '');


        if ($simple_query) {
            $query_orders_cnt_raw = "select count(distinct orders_id) as cnt_orders
                                                from " . TABLE_MAILBEEZ_TRACKING_ORDERS . "
                                                where 
                                                    date_record > '" . $this->timeframe_start . "'
                                                    and 
                                                    date_record <= '" . $this->timeframe_end . "'
                                                    ";
        } else {
            $query_orders_cnt_raw = "select count(distinct mto.orders_id) as cnt_orders
            from " . TABLE_MAILBEEZ_TRACKING_ORDERS . " mto
                join ( select message_id from " . TABLE_MAILBEEZ_TRACKING . " where
                    true
                        and ordered > '" . $this->timeframe_start . "' 
                        and ordered <= '" . $this->timeframe_end . "'
                        " . $this->limit_module_sql . " 
                        " . $this->limit_campaign_items_sql . "                
                    GROUP BY message_id ) mt 
                on (mto.message_id = mt.message_id 
                    and mto.date_record > '" . $this->timeframe_start . "'
                    and mto.date_record <= '" . $this->timeframe_end . "'
                ) ";
        }


        $query_orders_cnt = mh_db_query($query_orders_cnt_raw);
        $result_cnt = mh_db_fetch_array($query_orders_cnt);

        // exclude double trackings:
        if ($simple_query) {
            $query_revenue_raw = "select sum(ot.value) as revenue_value from
                                    (select distinct orders_id
                                        from " . TABLE_MAILBEEZ_TRACKING_ORDERS . "
                                            where 
                                                date_record > '" . $this->timeframe_start . "'
                                                and 
                                                date_record <= '" . $this->timeframe_end . "'
                                     ) mo
                                  join " . TABLE_ORDERS_TOTAL . " ot
                                    on (ot.orders_id = mo.orders_id and class='" . mh_cfg('MAILBEEZ_ANALYTICS_SUBTOTAL_MODULE') . "')
                                  where 1";
        } else {
            $query_revenue_raw = "select sum(ot.value) as revenue_value from
                                (select distinct mto.orders_id
                                    from " . TABLE_MAILBEEZ_TRACKING_ORDERS . " mto
                                        left join " . TABLE_MAILBEEZ_TRACKING . " mt on (mto.message_id = mt.message_id)
                                    where 
                                        date_record > '" . $this->timeframe_start . "'
                                        and
                                        date_record <= '" . $this->timeframe_end . "'
                                    " . $this->limit_module_sql . "
                                    " . $this->limit_campaign_items_sql . "
                                 ) mo
                              join " . TABLE_ORDERS_TOTAL . " ot
                                on (ot.orders_id = mo.orders_id and class='" . mh_cfg('MAILBEEZ_ANALYTICS_SUBTOTAL_MODULE') . "')
                              where 1";
        }


//        echo "<br>Subtotal SQL: " . $query_revenue_raw;

        $query_revenue = mh_db_query($query_revenue_raw);
        $result_revenue = mh_db_fetch_array($query_revenue);
        if ($simple_query) {
            $query_coupon_raw = "select sum(abs(ot.value)) as coupon_value from
                                    (select distinct orders_id
                                        from " . TABLE_MAILBEEZ_TRACKING_ORDERS . "
                                        where 
                                            date_record > '" . $this->timeframe_start . "'
                                            and
                                            date_record <= '" . $this->timeframe_end . "'

                                     ) mo
                                  join " . TABLE_ORDERS_TOTAL . " ot
                                    on (ot.orders_id = mo.orders_id and class='ot_coupon')
                                  where 1";

        } else {

            $query_coupon_raw = "select sum(abs(ot.value)) as coupon_value from
                                (select distinct mto.orders_id
                                    from " . TABLE_MAILBEEZ_TRACKING_ORDERS . " mto
                                        left join " . TABLE_MAILBEEZ_TRACKING . " mt on (mto.message_id = mt.message_id)
                                    where 
                                        date_record > '" . $this->timeframe_start . "'
                                        and
                                        date_record <= '" . $this->timeframe_end . "'
                                    " . $this->limit_module_sql . "
                                    " . $this->limit_campaign_items_sql . "
                                 ) mo
                              join " . TABLE_ORDERS_TOTAL . " ot
                                on (ot.orders_id = mo.orders_id and class='ot_coupon')
                              where 1";
        }

//        echo "<br>Coupon SQL: " . $query_coupon_raw;

        $query_coupon = mh_db_query($query_coupon_raw);
        $result_coupon = mh_db_fetch_array($query_coupon);

        return array($result_revenue['revenue_value'], $result_cnt['cnt_orders'], $result_coupon['coupon_value']);
//        return mh_price($query_revenue_result['revenue']);
    }

    static function _get_time_stamp($day, $month, $year, $endofday = false)
    {

        $hr = 0;
        $min = 0;
        $sec = 0;

        if ($endofday) {
            $hr = 23;
            $min = 59;
            $sec = 59;
        }

        mktime($hr, $min, $sec, $month, $day, $year);

    }

    static function get_date_range($id, $single = null, $format = 'm/d/Y')
    {
//                $timestamp_start = mailbeez_analytics::_get_time_stamp($d, $m, $y);
//                $timestamp_end = '';

        $timestamp_start = time();
        $timestamp_end = time();

        switch ($id) {
            case 'today':
                $timestamp_start = strtotime("today");
                $timestamp_end = strtotime("today");
                break;
            case 'yesterday':
                $timestamp_start = strtotime("yesterday");
                $timestamp_end = strtotime("yesterday");
                break;

            case 'this_week':
                $timestamp_start = strtotime("monday this week");
                $timestamp_end = strtotime("sunday this week");
                break;
            case 'last_week':
                $timestamp_start = strtotime("monday last week");
                $timestamp_end = strtotime("sunday last week");
                break;

            case 'this_month':
                $timestamp_start = strtotime("first day of this month");
                $timestamp_end = strtotime("last day of this month");
                break;
            case 'last_month':
                $timestamp_start = strtotime("first day of last month");
                $timestamp_end = strtotime("last day of last month");
                break;

            case 'this_year':
                $timestamp_start = strtotime("first day of January");
                $timestamp_end = strtotime("last day of December");
                break;
            case 'last_year':
                $timestamp_start = strtotime("first day of January last year");
                $timestamp_end = strtotime("last day of December last year");
                break;


        }

        $start_date = date($format, $timestamp_start);
        $end_date = date($format, $timestamp_end);

        if ($single == 'start') {
            return $start_date;
        }
        if ($single == 'end') {
            return $end_date;
        }


        return array($start_date, $end_date);

    }

    function get_mobile_useragent_data()
    {
        $query_mobile_raw = "select count(*) as cnt_mobile
                                    from " . TABLE_MAILBEEZ_TRACKING . "
                                where mobile='1' and
                                    date_sent >= '" . $this->timeframe_start . "'
                                    and
                                    date_sent <= '" . $this->timeframe_end . "'
                                    " . $this->limit_module_sql . "      
                                    " . $this->limit_campaign_items_sql . "      
                                " . MAILBEEZ_SIMULATION_SQL . "
                                ";

        $query_mobile = mh_db_query($query_mobile_raw);
        $result_mobile = mh_db_fetch_array($query_mobile);
        return array($result_mobile['cnt_mobile']);


        /*
         *
         * not tested
        $query_mobile_raw = "select count(*) as total, (
                            count(*) / (
                                select count(*)
                                    from " . TABLE_MAILBEEZ_TRACKING . "
                                where mobile='1')
                            ) * 100 as 'percentage'
                            from " . TABLE_MAILBEEZ_TRACKING . "";

        $query_mobile = mh_db_query($query_mobile_raw);
        $result_mobile = mh_db_fetch_array($query_mobile);
        return array($result_mobile['total'], $result_mobile['percentage']);
         */
    }

    static function is_mobile_useragent($useragent)
    {
        // http://detectmobilebrowsers.com/
        $match = preg_match('/' . self::MOBILE_REGEX1 . '/i', $useragent) || preg_match('/' . self::MOBILE_REGEX2 . '/i', substr($useragent, 0, 4));
        return ($match) ? 1 : 0;
    }
}
