statistic.php

<?php defined('SYSPATH') or die('No direct script access.');

/**
 * Admin controller:
 *
 * @author
 * @package
 * @version
 */
class Controller_Admin_Statistic extends Controller_Admin_Abstract
{

    /**
     * Controls access for the whole controller, if not set to FALSE we will only allow user roles specified.
     *
     * See Controller_App for how this implemented.
     *
     * Can be set to a string or an array, for example array('login', 'admin') or 'login'
     */

    public $auth_required = array('admin', 'manager','promotion_manager');

    /** Controls access for separate actions
     *
     *  See Controller_App for how this implemented.
     *
     *  Examples:
     * 'adminpanel' => 'admin' will only allow users with the role admin to access action_adminpanel
     * 'moderatorpanel' => array('login', 'moderator') will only allow users with the roles login and moderator to access action_moderatorpanel
     */
    public $submenu_list = array();

    public $secure_actions = array();


    public function before()
    {
        parent::before();

        $this->template->page_title = 'Статистика.';

        if (Auth::instance()->logged_in('manager') && !Auth::instance()->logged_in('admin')) {
            $this->submenu_list = array(
                'Акции' => '/admin_statistic/actions/index',
                'Статистика по акции' => '/admin_statistic/actionstat/index',
                'Статистика за период' => '/admin_statistic/period/',
            );
        }
        else
        {
            $this->submenu_list = array(
                'Акции' => '/admin_statistic/actions/index',
                'Баннеры' => '/admin_statistic/bannerstat/index',
                'Google' => '/admin_statistic/googlestat/index',
                'Статистика по акции' => '/admin_statistic/actionstat/index',
                'Статистика за период' => '/admin_statistic/period/',
                'Статистика по купонам' => '/admin_statistic/coupons/',
                'Статистика за месяц' => '/admin_reports/per_month_stats/',
                'Внешняя авторизация' => '/admin_statistic/extauth',
                'Рефералы' => '/admin_statistic/referals',
                'ADV' => '/admin_adv',
                'Маркерованные пользователи' => '/admin_statistic/usertags'
//		'ADV' => '/admin_statistic/ADV',
            );
        }


    }

    public function action_index()
    {

        if (Auth::instance()->logged_in('manager') && !Auth::instance()->logged_in('admin')) {
            Request::current()->redirect('admin_statistic/actions/index');
        }

        /*		store::instance();
                store::setKey('admin_stat_index');
                $view = store::get();
                if (!$view)
                {
                    $view = View::factory('admin/statistic/index');


                    $view->count_adv_all = ORM::factory('user')->where('http_referer','like','%am10.ru%')->count_all();

                    $view->count_adv_all_logins = ORM::factory('user')->where('http_referer','like','%am10.ru%')->where('logins','>',1)->count_all();

                    $view->count_adv_all_coupons = ORM::factory('coupon')->with('user')->where('http_referer','like','%am10.ru%')->count_all();

                    $view->count_adv_discharge_coupons = ORM::factory('coupon')->where('is_discharge','=',1)->with('user')->where('http_referer','like','%am10.ru%')->count_all();



                    $view->count_vk_all = ORM::factory('user')->where('http_referer','like','%http://vkontakte.ru/ads.php%')->count_all();

                    $view->count_vk_all_logins = ORM::factory('user')->where('http_referer','like','%http://vkontakte.ru/ads.php%')->where('logins','>',1)->count_all();

                    $view->count_vk_all_coupons = ORM::factory('coupon')->with('user')->where('http_referer','like','%http://vkontakte.ru/ads.php%')->count_all();

                    $view->count_vk_discharge_coupons = ORM::factory('coupon')->where('is_discharge','=',1)->with('user')->where('http_referer','like','%http://vkontakte.ru/ads.php%')->count_all();


                    $view->count_ftp_all = ORM::factory('user')->where('http_referer','like','%Freetopay%')->count_all();

                    $view->count_ftp_all_logins = ORM::factory('user')->where('http_referer','like','%Freetopay%')->where('logins','>',1)->count_all();

                    $view->count_ftp_all_coupons = ORM::factory('coupon')->with('user')->where('http_referer','like','%Freetopay%')->count_all();

                    $view->count_ftp_discharge_coupons = ORM::factory('coupon')->where('is_discharge','=',1)->with('user')->where('http_referer','like','%Freetopay%')->count_all();

                    store::set($view, 60*20);
                }

        */


        //$query = "SELECT COUNT(*) as count FROM  `couppons` WHERE FROM_UNIXTIME(  `start_date` ,  '%d.%m.%Y' ) = '".date("d.m.Y")."'";
        //$count=DB::query(Database::SELECT,$query)->cached(7200)->execute()->as_array();

        $this->template->title = 'Admin Statistic';
        $this->template->submenu_list = $this->submenu_list;
        $this->template->content = ''; //(string) $view;
    }

    public function action_actions()
    {

        $view = View::factory('admin/statistic/actions');

        $this->template->title = 'Admin Statistic';
        $this->template->submenu_list = $this->submenu_list;
        $this->template->content = (string)$view;
    }


    public function action_extauth()
    {

        $view = View::factory('admin/statistic/extauth');

        $view->auth = ORM::factory('auth');

        $this->template->title = 'Admin Statistic';
        $this->template->submenu_list = $this->submenu_list;
        $this->template->content = (string)$view;
    }

    public function action_referals()
    {
        $view = View::factory('admin/statistic/referals');

        $view->user = ORM::factory('user');

        $this->template->title = 'Admin Statistic';
        $this->template->submenu_list = $this->submenu_list;
        $this->template->content = (string)$view;
    }


    public function action_adv()
    {
	    $refferal = $this->request->param('id',null);
        $this->template->title = 'Admin Statistic';
        $this->template->submenu_list = $this->submenu_list;
        $view = View::factory('admin/statistic/adv')->set('refferal', $refferal);

        $this->template->content = (string)$view;
    }

    public function action_coupons()
    {

        $view = View::factory('admin/statistic/coupons');

        $view->cities_list = ORM::factory('city')->getList();
        $view->code = $code = isset($_POST['code']) ? $_POST['code'] : null;

        if (!empty($_POST)) {

            $p = $_POST;
            $str = $p['year'] . '-' . $p['month'];
            $start = strtotime($str);
            $end = strtotime('+1 month', $start);

            $coupon = ORM::factory('coupon');

            if ($p['type'] == 'paid') {
                $coupon->where('is_hold', '=', 1);
                $coupon->where('cost', '!=', 0);
            } elseif ($p['type'] == 'discharge') {
                $coupon->where('discharge_date', '>=', $start);
                $coupon->where('discharge_date', '<', $end);
            }


            if (null != $code) {
                $coupon->where('coupon', 'LIKE', $code);
            }

            $coupon->order_by('deal_id');
            $coupon->order_by('id');
            $find = $coupon->find_all();
            //echo $coupon->last_query();
            $view->coupons = $find;
            $view->coupon_model = $coupon;
            $view->set($p);
        }

        $this->template->title = 'Admin Statistic';
        $this->template->submenu_list = $this->submenu_list;
        $this->template->content = (string)$view;
    }


    public function action_bannerstat()
    {
        $view = View::factory('admin/bannerstat/index');
        $this->template->title = 'Admin banner statistic';
        $this->template->submenu_list = $this->submenu_list;
        $this->template->content = (string)$view;
    }

    public function action_googlestat()
    {
        $view = View::factory('admin/googlestat/index');
        $view->filterForm = new Model_Form_StatLandingFilter();
        $this->template->title = 'Admin google statistic';
        $this->template->submenu_list = $this->submenu_list;
        $this->template->content = (string)$view;
    }


    public function action_bannerfullstat()
    {
        $this->auto_render = false;
        $start_date = $_POST['start_date'];
        $end_date = $_POST['end_date'];
        $start_date_conv = date('Y-m-d', strtotime($start_date));
        $end_date_conv = date('Y-m-d', strtotime($end_date));

        $type = $_POST['type'];
        $ref = $_POST['refferal'];


        $all = ORM::factory('bannerstat')
            ->where('time', '>=', $start_date_conv)
            ->where('time', '<=', $end_date_conv . ' 23:59:00');
        if ($type != '0') $all->where('type', '=', $type);
        if ($ref != '0') $all->where('refferal', '=', $ref);
        $all = $all->count_all();


        $reg = ORM::factory('bannerstat')
            ->where('time', '>=', $start_date_conv)
            ->where('time', '<=', $end_date_conv . ' 23:59:00')
            ->where('user_registration', '=', '1');
        if ($type != '0') $reg->where('type', '=', $type);
        if ($ref != '0') $reg->where('refferal', '=', $ref);
        $reg = $reg->count_all();

        $count = ORM::factory('bannerstat')

            ->join('coupons', 'RIGHT')
            ->on('banners_stat.user_id', '=', 'coupons.user_id')
            ->where('time', '>=', $start_date_conv)
            ->where('time', '<=', $end_date_conv . ' 23:59:00')
            ->where('banners_stat.user_id', '!=', 0);
        if ($type != '0') $count->where('type', '=', $type);
        if ($ref != '0') $count->where('refferal', '=', $ref);
        $count = $count->count_all();


        $count_dis = ORM::factory('bannerstat')
            ->join('coupons', 'RIGHT')->on('banners_stat.user_id', '=', 'coupons.user_id')
            ->where('time', '>=', $start_date_conv)
            ->where('time', '<=', $end_date_conv . ' 23:59:00')
            ->where('is_discharge', '=', '1')
            ->where('banners_stat.user_id', '!=', 0);
        if ($type != '0') $count_dis->where('type', '=', $type);
        if ($ref != '0') $count_dis->where('refferal', '=', $ref);
        $count_dis = $count_dis->count_all();


        echo "Просмотров: <b>" . $all . "</b>  <br/>Регистраций: <b>" . $reg . "</b>    <br/>Взято купонов: <b>" . $count . "</b>    <br/>Погашено купонов: <b>" . $count_dis . "</b> ";
    }

    public function action_googlefullstat()
    {
        $this->auto_render = false;
        $start_date = $_POST['start_date'];
        $end_date = $_POST['end_date'];
        $start_date_conv = date('Y-m-d', strtotime($start_date));
        $end_date_conv = date('Y-m-d', strtotime($end_date));

        $itemsArr = array();
        $groupArr = array(
            'utm_source',
            'utm_medium',
            'utm_term',
            'utm_content',
            'utm_campaign',
        );

        foreach($groupArr as $item){
            if(isset($_POST[$item]) && $_POST[$item] != -1)
                $itemsArr[$item] = $_POST[$item];
        }

        $all = ORM::factory('google_statsdelta')
            ->select(DB::expr('SUM(count) as count2'))
            ->where('date', '>=', $start_date_conv)
            ->where('date', '<=', $end_date_conv );
        $this->_google_where_helper($all,$itemsArr);
        $all = $all->find()->count2;


        $reg = ORM::factory('google_statsdelta')
            ->where('date', '>=', $start_date_conv)
            ->where('date', '<=', $end_date_conv )
            ->where('user_registration', '=', '1');
        $this->_google_where_helper($reg,$itemsArr);
        $reg = $reg->count_all();

        $count = ORM::factory('google_statsdelta')
            ->join('coupons', 'RIGHT')
            ->on('google_stats_delta.user_id', '=', 'coupons.user_id')
            ->where('date', '>=', $start_date_conv)
            ->where('date', '<=', $end_date_conv . ' 23:59:00')
            ->where('google_stats_delta.user_id', '!=', 0);
        $this->_google_where_helper($count,$itemsArr);
        $count = $count->count_all();

        $count_dis = ORM::factory('google_statsdelta')
            ->join('coupons', 'RIGHT')->on('google_stats_delta.user_id', '=', 'coupons.user_id')
            ->where('date', '>=', $start_date_conv)
            ->where('date', '<=', $end_date_conv)
            ->where('is_discharge', '=', '1')
            ->where('google_stats_delta.user_id', '!=', 0);
        $this->_google_where_helper($count_dis,$itemsArr);
        $count_dis = $count_dis->count_all();

        //добавляем из сегодняшних данных

        $all2 = ORM::factory('googlestats')
            ->where('time', '>=', $start_date_conv)
            ->where('time', '<=', $end_date_conv . ' 23:59:00');
        $this->_google_where_helper($all2,$itemsArr);
        $all = $all + $all2->count_all();


        $reg2 = ORM::factory('googlestats')
            ->where('time', '>=', $start_date_conv)
            ->where('time', '<=', $end_date_conv . ' 23:59:00')
            ->where('user_registration', '=', '1');
        $this->_google_where_helper($reg2,$itemsArr);
        $reg += $reg2->count_all();

        $count2 = ORM::factory('googlestats')
            ->join('coupons', 'RIGHT')
            ->on('google_stats.user_id', '=', 'coupons.user_id')
            ->where('time', '>=', $start_date_conv)
            ->where('time', '<=', $end_date_conv . ' 23:59:00')
            ->where('google_stats.user_id', '!=', 0);
        $this->_google_where_helper($count2,$itemsArr);
        $count += $count2->count_all();

        $count_dis2 = ORM::factory('googlestats')
            ->join('coupons', 'RIGHT')->on('google_stats.user_id', '=', 'coupons.user_id')
            ->where('time', '>=', $start_date_conv)
            ->where('time', '<=', $end_date_conv . ' 23:59:00')
            ->where('is_discharge', '=', '1')
            ->where('google_stats.user_id', '!=', 0);
        $this->_google_where_helper($count_dis2,$itemsArr);
        $count_dis += $count_dis2->count_all();


        if($all != 0)
            $conv = sprintf("%.2f",$reg/$all * 100);
        else $conv = false;




        $convTxt = ($conv)?$conv."%":"не определено";
        $this->response->body("Просмотров: <b>" . $all . "</b>  <br/>Регистраций: <b>" . $reg . "</b> <br/>Конверсия: <b>".$convTxt."</b>   <br/>Взято купонов: <b>" . $count . "</b>    <br/>Погашено купонов: <b>" . $count_dis . "</b> ");
		/*return array(
			'all' => $all,
			'conversion' => $conv,
			'registred' => $reg,
			'coupons_geted' => $count,
			'coupons_discharged' => $count_dis
		); */
    }
    public function getCronGoogleData($itemsArr){

        $start_date_conv = "2000-01-01";
        $end_date_conv = date("Y-m-d");
        $groupArr = array(
            'utm_source',
            'utm_medium',
            'utm_term',
            'utm_content',
            'utm_campaign',
        );

        foreach($groupArr as $item){
            if(isset($_POST[$item]) && $_POST[$item] !== false)
                $itemsArr[$item] = $_POST[$item];
        }

        $all = ORM::factory('google_statsdelta')
            ->select(DB::expr('SUM(count) as count2'))
            ->where('date', '>=', $start_date_conv)
            ->where('date', '<=', $end_date_conv );
        $this->_google_where_helper($all,$itemsArr);
        $all = $all->find()->count2;


        $reg = ORM::factory('google_statsdelta')
            ->where('date', '>=', $start_date_conv)
            ->where('date', '<=', $end_date_conv )
            ->where('user_registration', '=', '1');
        $this->_google_where_helper($reg,$itemsArr);
        $reg = $reg->count_all();

		$all2 = ORM::factory('googlestats')
			->select(DB::expr('COUNT(*) as count2'))
			->where('time', '>=', $start_date_conv.' 00:00:00')
			->where('time', '<=', $end_date_conv.' 23:59:59' );
		$this->_google_where_helper($all2,$itemsArr);
		$all += $all2->find()->count2;


		$reg2 = ORM::factory('googlestats')
			->where('time', '>=', $start_date_conv.' 00:00:00')
			->where('time', '<=', $end_date_conv.' 23:59:59' )
			->where('user_registration', '=', '1');
		$this->_google_where_helper($reg2,$itemsArr);
		$reg += $reg2->count_all();

        if($all != 0)
            $conv =$reg/$all * 100;
        else $conv = false;

        return array(
            'all' => $all,
            'conversion' => $conv,
            'registred' => $reg,
        );
    }

    protected function _google_where_helper(&$orm,$values,$cond = '='){
        foreach($values as $key => $value){
            $orm->where($key,$cond,$value);
        }
    }

    public function action_getgoogleemails(){
        $this->auto_render = false;
        $start_date = $_POST['start_date'];
        $end_date = $_POST['end_date'];
        $start_date_conv = date('Y-m-d', strtotime($start_date));
        $end_date_conv = date('Y-m-d', strtotime($end_date));

        $groupArr = array(
            'utm_source',
            'utm_medium',
            'utm_term',
            'utm_content',
            'utm_campaign',
        );

        $utmWhere = array();

        foreach($groupArr as $key){
            if(isset($_POST[$key]) && $_POST[$key] !== false && $_POST[$key] != -1){
                $utmWhere[$key] = $key."='".$_POST[$key]."'";
            }
        }

        $where = implode(" AND ",$utmWhere);
        if($where){
            $where = " AND ".$where;
        }


        $sql = "SELECT user_id,email,FROM_UNIXTIME(created) as date FROM google_stats
                LEFT JOIN users ON (users.id = google_stats.user_id)
                WHERE user_id IS NOT NULL AND time >= '".$start_date_conv."' AND time <= '".$end_date_conv."' ".$where."
                UNION ALL
                SELECT user_id,email,FROM_UNIXTIME(created) as date FROM google_stats_delta
                LEFT JOIN users ON (users.id = google_stats_delta.user_id)
                WHERE user_id IS NOT NULL AND date >= '".$start_date_conv."' AND date <= '".$end_date_conv."' ".$where."
            ";

        //print_r($sql);

        $res = DB::query(Database::SELECT, $sql)->execute()->as_array();
        //array_unshift($res,array("user_id" => "ID","email" => "email","date" => "regDate"));


        //create a scv file
        $content = '';
        foreach($res as $ar)
        {
            $content .= implode(";", $ar);
            $content .= "\r\n";
        }

        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename=emails.csv');
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Pragma: public');
        header('Content-Length: ' . strlen($content));
        echo $content;
        exit;

        //var_dump($content);
        //var_dump($res);
        die();
    }


    public function action_getactiondate()
    {
        $this->auto_render = false;
        //читаем параметры
        $curPage = $_POST['page'];
        $rowsPerPage = $_POST['rows'];
        $sortingField = $_POST['sidx'];
        $sortingOrder = $_POST['sord'];

        $qWhere = ' WHERE d.dealstatus_id != 4 ';
        if (isset($_POST['_search']) && $_POST['_search'] == 'true') {
            //$allowedFields = array('coupon', 'user_id', 'deal_id');
            $allowedOperations = array('AND', 'OR');

            $searchData = json_decode($_POST['filters']);

            //ограничение на количество условий
            if (count($searchData->rules) > 10) {
                throw new Exception('Cool hacker is here!!!  ');
            }

            $qWhere = ' WHERE d.dealstatus_id != 4 ';
            $firstElem = false;

            //объединяем все полученные условия
            foreach ($searchData->rules as $rule) {
                if (!$firstElem) {
                    //объединяем условия (с помощью AND или OR)
                    if (in_array($searchData->groupOp, $allowedOperations)) {
                        $qWhere .= ' ' . $searchData->groupOp . ' ';
                    }
                    else {
                        //если получили не существующее условие - возвращаем описание ошибки
                        throw new Exception('Cool hacker is here!!!  ');
                    }
                }
                else {
                    $firstElem = false;
                }

                //вставляем условия
                if (true) {
                    if (($rule->field == 'start_date') OR ($rule->field == 'end_date')) {
                        $rule->data = strtotime($rule->data);
                    }
                    if ($rule->field == 'cityname') {
                        $rule->field = 'city.name';
                    }
                    if ($rule->field == 'name') {
                        $rule->field = 'd.name';
                    }

                    switch ($rule->op) {
                        case 'eq':
                            $qWhere .= $rule->field . " = '" . ($rule->data) . "'";
                            break;
                        case 'ne':
                            $qWhere .= $rule->field . " <> '" . ($rule->data) . "'";
                            break;
                        case 'ge':
                            $qWhere .= $rule->field . " >= '" . ($rule->data) . "'";
                            break;
                        case 'le':
                            $qWhere .= $rule->field . " <= '" . ($rule->data) . "'";
                            break;
                        case 'lt':
                            $qWhere .= $rule->field . " < '" . ($rule->data) . "'";
                            break;
                        case 'gt':
                            $qWhere .= $rule->field . " > '" . ($rule->data) . "'";
                            break;
                        case 'bw':
                            $qWhere .= $rule->field . " LIKE '" . ($rule->data . '%') . "'";
                            break;
                        case 'cn':
                            $qWhere .= $rule->field . " LIKE '" . ('%' . $rule->data . '%') . "'";
                            break;
                        default:
                            throw new Exception('Cool hacker is here!!!  ');
                    }
                }
                else {
                    //если получили не существующее условие - возвращаем описание ошибки
                    throw new Exception('Cool hacker is here!!!  ');
                }
            }
        }


        //определяем количество записей в таблице
        $query = 'SELECT COUNT( d.id ) AS count FROM deals AS d LEFT JOIN cities as city ON city.id = d.city_id' . $qWhere;
        $rows = ORM::factory('coupon')->setquery($query);
        $rows = $rows[0]['count'];
        $totalRows = $rows;
        $firstRowIndex = $curPage * $rowsPerPage - $rowsPerPage;
        //получаем список купонов из базы
        $query = 'SELECT d.*, c2.count1, c3.count2, c4.count3, c5.count4, city.name as cityname
			FROM deals AS d
			LEFT JOIN (

			SELECT c.deal_id, COUNT( c.id ) as count1
			FROM coupons AS c
			GROUP BY c.deal_id
			)c2 ON d.id = c2.deal_id
			LEFT JOIN (

			SELECT deal_id, COUNT( id ) as count2
			FROM coupons
			WHERE  is_discharge =  "1"
			GROUP BY deal_id
			)c3 ON d.id = c3.deal_id

			LEFT JOIN (

			SELECT deal_id, COUNT( id ) as count3
			FROM coupons
			WHERE  is_hold = "1"
			GROUP BY deal_id
			)c4 ON d.id = c4.deal_id

			LEFT JOIN (

			SELECT deal_id, SUM( cost ) as count4
			FROM coupons
			WHERE  is_hold = "1"
			GROUP BY deal_id
			)c5 ON d.id = c5.deal_id

			LEFT JOIN cities as city ON city.id = d.city_id

			' . $qWhere . '

		    ORDER BY ' . $sortingField . ' ' . $sortingOrder . ' LIMIT ' . $firstRowIndex . ', ' . $rowsPerPage;


        $res = ORM::factory('coupon')->setquery($query);
        //сохраняем номер текущей страницы, общее количество страниц и общее количество записей
        $response = new stdClass();
        $response->page = $curPage;
        $response->total = ceil($totalRows / $rowsPerPage);
        $response->records = $totalRows;
        $i = 0;
        $count1 = 0;
        $count2 = 0;
        $count3 = 0;
        $count4 = 0;
        foreach ($res as $row)
        {
            $response->rows[$i]['id'] = $row['id'];
            $discharge_date = 0;
            $response->rows[$i]['cell'] = array($row['id'], strip_tags($row['name']), $row['cityname'],
                date('H:i d-m-Y', $row['start_date']), date('H:i d-m-Y', $row['end_date']),
                $row['count1'], $row['count2'], $row['count3'], $row['count4']);
            $i++;
            $count1 += $row['count1'];
            $count2 += $row['count2'];
            $count3 += $row['count3'];
            $count4 += $row['count4'];

        }
        $response->userdata['name'] = 'Общее количество купонов:';
        $response->userdata['id'] = 's:';
        $response->userdata['count1'] = $count1;
        $response->userdata['count2'] = $count2;
        $response->userdata['count3'] = $count3;
        $response->userdata['count4'] = $count4;

        //  print_r($response);
        echo json_encode($response);

    }

    public function action_getbannerdate()
    {
        $this->auto_render = false;
        //читаем параметры
        $curPage = $_POST['page'];
        $rowsPerPage = $_POST['rows'];
        $sortingField = $_POST['sidx'];
        $sortingOrder = $_POST['sord'];
        $whereemail = false;
        $qWhere = ' WHERE user_registration = 1 ';
        if (isset($_POST['_search']) && $_POST['_search'] == 'true') {
            //$allowedFields = array('coupon', 'user_id', 'deal_id');
            $allowedOperations = array('AND', 'OR');

            $searchData = json_decode($_POST['filters']);

            //ограничение на количество условий
            if (count($searchData->rules) > 10) {
                throw new Exception('Cool hacker is here!!!  ');
            }


            $firstElem = false;

            //объединяем все полученные условия
            foreach ($searchData->rules as $rule) {
                if (!$firstElem) {
                    //объединяем условия (с помощью AND или OR)
                    if (in_array($searchData->groupOp, $allowedOperations)) {
                        $qWhere .= ' ' . $searchData->groupOp . ' ';
                    }
                    else {
                        //если получили не существующее условие - возвращаем описание ошибки
                        throw new Exception('Cool hacker is here!!!  ');
                    }
                }
                else {
                    $firstElem = false;
                }

                //вставляем условия
                if (true) {
                    switch ($rule->op) {
                        case 'eq':
                            $qWhere .= $rule->field . " = '" . ($rule->data) . "'";
                            break;
                        case 'ne':
                            $qWhere .= $rule->field . " <> '" . ($rule->data) . "'";
                            break;
                        case 'ge':
                            $qWhere .= $rule->field . " >= '" . ($rule->data) . "'";
                            break;
                        case 'le':
                            $qWhere .= $rule->field . " <= '" . ($rule->data) . "'";
                            break;
                        case 'lt':
                            $qWhere .= $rule->field . " < '" . ($rule->data) . "'";
                            break;
                        case 'gt':
                            $qWhere .= $rule->field . " > '" . ($rule->data) . "'";
                            break;
                        case 'bw':
                            $qWhere .= $rule->field . " LIKE '" . ($rule->data . '%') . "'";
                            break;
                        case 'cn':
                            $qWhere .= $rule->field . " LIKE '" . ('%' . $rule->data . '%') . "'";
                            break;
                        default:
                            throw new Exception('Cool hacker is here!!!  ');
                    }
                }
                else {
                    //если получили не существующее условие - возвращаем описание ошибки
                    throw new Exception('Cool hacker is here!!!  ');
                }
            }
        }


        //определяем количество записей в таблице
        $query = 'SELECT COUNT( id ) AS count FROM banners_stat' . $qWhere;


        $rows = ORM::factory('bannerstat')->setquery($query);
        $rows = $rows[0]['count'];
        $totalRows = $rows;
        $firstRowIndex = $curPage * $rowsPerPage - $rowsPerPage;
        //получаем список купонов из базы
        $query = 'SELECT * FROM banners_stat
		    ' . $qWhere . '
			ORDER BY ' . $sortingField . ' ' . $sortingOrder . ' LIMIT ' . $firstRowIndex . ', ' . $rowsPerPage;


        $res = ORM::factory('bannerstat')->setquery($query);
        //сохраняем номер текущей страницы, общее количество страниц и общее количество записей
        $response = new stdClass();
        $response->page = $curPage;
        $response->total = ceil($totalRows / $rowsPerPage);
        $response->records = $totalRows;
        $i = 0;
        foreach ($res as $row)
        {
            $response->rows[$i]['id'] = $row['id'];
            $response->rows[$i]['cell'] = array($row['id'], $row['type'], $row['refferal'], $row['media'], strip_tags($row['name']), $row['user_registration'], $row['time']);
            $i++;
        }
        //  print_r($response);
        echo json_encode($response);

    }

    public function action_getgoogledate()
    {
        $this->auto_render = false;
        //читаем параметры
        $curPage = $_POST['page'];
        $rowsPerPage = $_POST['rows'];
        $sortingField = $_POST['sidx'];
        $sortingOrder = $_POST['sord'];
        $whereemail = false;
        $qWhere = ' WHERE user_registration = 1 ';
        if (isset($_POST['_search']) && $_POST['_search'] == 'true') {
            //$allowedFields = array('coupon', 'user_id', 'deal_id');
            $allowedOperations = array('AND', 'OR');

            $searchData = json_decode($_POST['filters']);

            //ограничение на количество условий
            if (count($searchData->rules) > 10) {
                throw new Exception('Cool hacker is here!!!  ');
            }


            $firstElem = false;

            //объединяем все полученные условия
            foreach ($searchData->rules as $rule) {
                if (!$firstElem) {
                    //объединяем условия (с помощью AND или OR)
                    if (in_array($searchData->groupOp, $allowedOperations)) {
                        $qWhere .= ' ' . $searchData->groupOp . ' ';
                    }
                    else {
                        //если получили не существующее условие - возвращаем описание ошибки
                        throw new Exception('Cool hacker is here!!!  ');
                    }
                }
                else {
                    $firstElem = false;
                }

                //вставляем условия
                if (true) {
                    switch ($rule->op) {
                        case 'eq':
                            $qWhere .= $rule->field . " = '" . ($rule->data) . "'";
                            break;
                        case 'ne':
                            $qWhere .= $rule->field . " <> '" . ($rule->data) . "'";
                            break;
                        case 'ge':
                            $qWhere .= $rule->field . " >= '" . ($rule->data) . "'";
                            break;
                        case 'le':
                            $qWhere .= $rule->field . " <= '" . ($rule->data) . "'";
                            break;
                        case 'lt':
                            $qWhere .= $rule->field . " < '" . ($rule->data) . "'";
                            break;
                        case 'gt':
                            $qWhere .= $rule->field . " > '" . ($rule->data) . "'";
                            break;
                        case 'bw':
                            $qWhere .= $rule->field . " LIKE '" . ($rule->data . '%') . "'";
                            break;
                        case 'cn':
                            $qWhere .= $rule->field . " LIKE '" . ('%' . $rule->data . '%') . "'";
                            break;
                        default:
                            throw new Exception('Cool hacker is here!!!  ');
                    }
                }
                else {
                    //если получили не существующее условие - возвращаем описание ошибки
                    throw new Exception('Cool hacker is here!!!  ');
                }
            }
        }


        //определяем количество записей в таблице
        $query = 'SELECT COUNT( id ) AS count FROM google_stats_delta' . $qWhere;


        $rows = ORM::factory('google_statsdelta')->setquery($query);
        $rows = $rows[0]['count'];
        $totalRows = $rows;
        $firstRowIndex = $curPage * $rowsPerPage - $rowsPerPage;
        //получаем список купонов из базы
        $query = 'SELECT * FROM google_stats_delta
    		    ' . $qWhere . '
    			ORDER BY ' . $sortingField . ' ' . $sortingOrder . ' LIMIT ' . $firstRowIndex . ', ' . $rowsPerPage;


        $res = ORM::factory('google_statsdelta')->setquery($query);
        //сохраняем номер текущей страницы, общее количество страниц и общее количество записей
        $response = new stdClass();
        $response->page = $curPage;
        $response->total = ceil($totalRows / $rowsPerPage);
        $response->records = $totalRows;
        $i = 0;
        foreach ($res as $row)
        {
            $response->rows[$i]['id'] = $row['id'];
            $response->rows[$i]['cell'] = array($row['id'], $row['utm_source'], $row['utm_medium'], $row['utm_term'], $row['utm_content'], $row['utm_campaign'], $row['user_registration'], $row['date']);
            $i++;
        }
        //  print_r($response);
        echo json_encode($response);

    }


    public function action_actionstat()
    {
        $view = View::factory('admin/statistic/find');

        $this->template->title = 'Admin Statistic';
        $this->template->submenu_list = $this->submenu_list;
        $this->template->content = (string)$view;
    }


    public function action_search()
    {
        $this->auto_render = false;
        if (isset($_POST['name']) && ($_POST['name'] != '')) {
            $name = $_POST['name'];
            $actions = ORM::factory('deal')->with('company')->where('deals.name', 'like', '%' . $name . '%')->or_where('company.name', 'like', '%' . $name . '%')->or_where('company.description', 'like', '%' . $name . '%')->find_all();
            foreach ($actions as $action)
            {
                echo '<tr><td>' . $action->id . '</td><td>' . $action->name . '</td><td>' . $action->company->name . '  </td><td><a href="#" onclick="$(\'#stat\').load(\'/admin_statistic/fullstat/' . $action->id . '\'); return false;">Выбрать</a></td> </tr>';
            }
        }
        return;


    }


    public function action_fullstat()
    {
		$deal_id = $id = $this->request->param('id');
        $coupons = ORM::factory('coupon');
        $countAll = $coupons->where('deal_id', '=', $deal_id)->count_all();
        $countDis = $coupons->where('deal_id', '=', $deal_id)->where('is_discharge', '=', 1)->count_all();
        $countPrint = $coupons->where('deal_id', '=', $deal_id)->where('is_print', '=', 1)->count_all();
        $countHold = $coupons->where('deal_id', '=', $deal_id)->where('is_hold', '=', 1)->count_all();
        $sumHold = DB::SELECT('id', array('SUM("cost")', 'total_sum'))
            ->from('coupons')
            ->where('deal_id', '=', $deal_id)
            ->where('is_hold', '=', 1)
            ->execute()->get('total_sum');

        $deal = ORM::factory('deal', $deal_id);
        $view = View::factory('admin/statistic/fullstat')
            ->set('deal', $deal)
            ->set('countAll', $countAll)
            ->set('countDis', $countDis)
            ->set('countHold', $countHold)
            ->set('sumHold', $sumHold)
            ->set('countPrint', $countPrint);


        if (Request::current()->is_ajax()) {
            $this->auto_render = false;
            echo (string)$view;
            return;
        }
        else
        {
            $this->template->content = (string)$view;
        }


    }

    public function action_period()
    {
        if (isset($_POST['start_date']) && ($_POST['start_date'] != '') && isset($_POST['end_date']) && ($_POST['end_date'] != '')) {

            $start_date = strtotime('00:00' . $_POST['start_date']);
            $end_date = strtotime('23:59' . $_POST['end_date']);
            $citywhere = '';
            if ($_POST['city_id']) {
                $citywhere = ' AND city_id = ' . $_POST['city_id'] . ' ';
            }

            $query = "
		      SELECT  FROM_UNIXTIME(discharge_date,'%d.%m.%Y') as dis_date,`deal_id`, cities.name as c_name, d.name as name, d.coupon_expiry_date as expire  , COUNT( * ) as count
		      FROM  `coupons`
		      LEFT JOIN `deals` as d ON d.id =  coupons.deal_id
		      LEFT JOIN `cities` ON cities.id =  d.city_id
		      WHERE
		      `discharge_date` >= $start_date
		      AND `discharge_date` <= $end_date
		      AND `is_discharge` = '1'" .
                $citywhere .
                "GROUP BY `deal_id`
		      ORDER BY city_id, name
		";

            //echo $query;

            $coupons = DB::query(Database::SELECT, $query)->execute()->as_array();
            $total = 0;
            foreach ($coupons as $coupon)
            {
                $deal = ORM::factory('deal', $coupon['deal_id']);
                switch ($_POST['payment'])
                {
                    case 2:
                        if ($deal->prices()) continue(2);

                        break;

                    case 3:
                        if (!$deal->prices()) continue(2);
                        break;
                }


                $total += $coupon['count'];
                echo '<tr style="cursor:pointer;" onclick="$(\'#couponlist table\').load(\'/admin_statistic/couponlist/?deal_id=' . $coupon['deal_id'] . '&start=' . $start_date . '&end=' . $end_date . '\')"><td>' . strip_tags($coupon['name']) . '</td><td style="width:150px">' . strip_tags($coupon['c_name']) . '</td><td>' . $coupon['count'] . '</td><td style="width:100px"> до ' . date("d-m-Y", $coupon['expire']) . '</td></tr>';
            }
            echo '<tr><td><strong>Всего купонов:</strong></td><td style="width:150px"></td><td>' . $total . '</td></tr>';
            $total = 0;

            if ($_POST['payment'] != 2) {
                echo '<tr><td><strong>Оплаченные купоны:</strong></td><td style="width:150px"></td><td></td></tr>';
                $query = "
			  SELECT  pi.paidtime,`deal_id`, cities.name as c_name, d.name as name, d.coupon_expiry_date as expire  , COUNT( * ) as count
			  FROM  `coupons`
			  LEFT JOIN `deals` as d ON d.id =  coupons.deal_id
			  LEFT JOIN `cities` ON cities.id =  d.city_id
			  LEFT JOIN `payment_items` as pi ON pi.item_id =  coupons.id
			  WHERE
			  pi.paidtime >= FROM_UNIXTIME($start_date+7200)
			  AND pi.paidtime <= FROM_UNIXTIME($end_date+7200)
			  AND `is_hold` = '1'" .
                    $citywhere .
                    "GROUP BY `deal_id`
			  ORDER BY city_id, name
		    ";
                $coupons = DB::query(Database::SELECT, $query)->execute()->as_array();
                $total = 0;
                foreach ($coupons as $coupon)
                {
                    $total += $coupon['count'];
                    echo '<tr style="cursor:pointer;" onclick="$(\'#couponlist table\').load(\'/admin_statistic/couponlist/?deal_id=' . $coupon['deal_id'] . '&start=' . $start_date . '&end=' . $end_date . '\')"><td>' . strip_tags($coupon['name']) . '</td><td style="width:150px">' . strip_tags($coupon['c_name']) . '</td><td>' . $coupon['count'] . '</td><td style="width:100px"> до ' . date("d-m-Y", $coupon['expire']) . '</td></tr>';
                }


                echo '<tr><td><strong>Всего купонов:</strong></td><td style="width:150px"></td><td>' . $total . '</td></tr>';
            }
            $this->auto_render = false;
            exit();
        }

        $view = View::factory('admin/statistic/periodstat');

        $view->cities = ORM::factory('city')->getListNoCache();

        $this->template->title = 'Admin Statistic';
        $this->template->submenu_list = $this->submenu_list;
        $this->template->content = (string)$view;
    }


    public function action_couponlist()
    {
        echo '<tr><td>Погашено: </td><td style="width:100px;"></td></tr>';
        $coupons = ORM::factory('coupon')->where('deal_id', '=', $_GET['deal_id'])->where('discharge_date', '>=', $_GET['start'])->where('discharge_date', '<=', $_GET['end'])->find_all();

        $i = 0;
        foreach ($coupons as $coupon)
        {
            echo '<tr><td>' . ++$i . ') </td><td style="width:100px;">' . $coupon->coupon . '</td><td >' . date("d-m-Y H:i", $coupon->discharge_date) . '</td></tr>';
        }


        $query = "
		      SELECT  pi.paidtime as p_time, coupons.coupon as coupon, d.name as name, d.coupon_expiry_date as expire
		      FROM  `coupons`
		      LEFT JOIN `deals` as d ON d.id =  coupons.deal_id
		      LEFT JOIN `cities` ON cities.id =  d.city_id
		      LEFT JOIN `payment_items` as pi ON pi.item_id =  coupons.id
		      WHERE
		      pi.paidtime >= FROM_UNIXTIME(" . ($_GET['start'] + 7200) . ")
		      AND pi.paidtime <= FROM_UNIXTIME(" . ($_GET['end'] + 7200) . ")
		      AND `is_hold` = '1'
		      AND `deal_id` = (" . $_GET['deal_id'] . ")

		      ORDER BY city_id, name
		";
        $coupons = DB::query(Database::SELECT, $query)->execute()->as_array();
        if (count($coupons) > 0)
            echo '<tr><td>Оплачено: </td><td style="width:100px;"></td></tr>';
        foreach ($coupons as $coupon)
        {
            echo '<tr><td>' . ++$i . ') </td><td style="width:100px;">' . $coupon['coupon'] . '</td><td >' . $coupon['p_time'] . '</td></tr>';
        }

        $this->auto_render = false;
    }

    public function action_graph()
    {
        $deal_id = $_POST['deal_id'];
        $start_date = strtotime($_POST['start_date']);
        $end_date = strtotime($_POST['end_date']) + (23 * 60 * 60) + (59 * 60);
        $this->auto_render = false;

        $query = "
		      SELECT  FROM_UNIXTIME(discharge_date,'%d.%m.%Y') as dis_date , COUNT( * ) as count
		      FROM  `coupons`
		      WHERE `deal_id` = $deal_id
		      AND `discharge_date` >= $start_date
		      AND `discharge_date` <= $end_date
		      AND `is_discharge` = '1'
		      GROUP BY YEAR( FROM_UNIXTIME( `discharge_date`) ) , DAY( FROM_UNIXTIME( `discharge_date`) )
		      ORDER BY `discharge_date`
	    ";

        $coupons = DB::query(Database::SELECT, $query)->execute()->as_array();
        $date_array = array();

        $count = 0;

        foreach ($coupons as $date)
        {
            $date_array[] = array(((int)strtotime($date['dis_date'])) * 1000 + (3 * 1000 * 60 * 60), (int)$date['count']);
            $count += (int)$date['count'];
        }
        $all_count = ORM::factory('coupon')->where('discharge_date', '>=', $start_date)->where('discharge_date', '<=', $end_date)->count_all();

        $json = array('label' => 'Погашенные купоны', 'data' => $date_array, 'dis_count' => $count, 'all_count' => $all_count);
        echo json_encode($json);

    }

    public function action_couponsgraph()
    {
        $deal_id = $_POST['deal_id'];
        $start_date = strtotime($_POST['start_date']);
        $end_date = strtotime($_POST['end_date']) + (23 * 60 * 60) + (59 * 60);


        $this->auto_render = false;

        $query = "
		      SELECT  created , COUNT( * ) as count
		      FROM  `coupons`

		      WHERE `deal_id` = $deal_id
		      AND `created` >= $start_date
		      AND `created` <= $end_date
		      GROUP BY YEAR( FROM_UNIXTIME( `created`) ) , DAY( FROM_UNIXTIME( `created`) )
		      ORDER BY `created`
	    ";
        $coupons = DB::query(Database::SELECT, $query)->execute()->as_array();


        $date_array = array();

        $count = 0;
        $i = 0;
        foreach ($coupons as $date)
        {
            $count = (int)$date['count'];
            $date_array[] = array($i, $count);
            $count += (int)$date['count'];
            $i++;
        }


        $json = array('label' => 'Взятые купоны', 'data' => $date_array);
        echo json_encode($json);

    }


    public function action_couponsdisgraph()
    {
        $deal_id = $_POST['deal_id'];
        $start_date = strtotime($_POST['start_date']);
        $end_date = strtotime($_POST['end_date']) + (23 * 60 * 60) + (59 * 60);


        $this->auto_render = false;

        $query = "
		      SELECT  created , COUNT( * ) as count
		      FROM  `coupons`

		      WHERE `deal_id` = $deal_id
		      AND `created` >= $start_date
		      AND `created` <= $end_date
		      GROUP BY YEAR( FROM_UNIXTIME( `created`) ) , DAY( FROM_UNIXTIME( `created`) )
		      ORDER BY `created`
	    ";
        $coupons = DB::query(Database::SELECT, $query)->execute()->as_array();


        $query = "
		      SELECT  created , COUNT( * ) as count
		      FROM  `coupons`

		      WHERE `deal_id` = $deal_id
		      AND `created` >= $start_date
		      AND `created` <= $end_date
		      AND `is_discharge` = '1'
		      GROUP BY YEAR( FROM_UNIXTIME( `created`) ) , DAY( FROM_UNIXTIME( `created`) )
		      ORDER BY `created`
	    ";
        $coupons_dis = DB::query(Database::SELECT, $query)->execute()->as_array();

        $date_array = array();

        $count = 0;
        $i = 0;
        foreach ($coupons_dis as $date)
        {

            if ($coupons[$i]['count'] != 0) {
                $count = (100 / $coupons[$i]['count']) * (int)$date['count'];
            }
            else {
                $count = 0;
            }
            $date_array[] = array($i, $count);
            $count += (int)$date['count'];
            $i++;
        }


        $json = array('label' => 'Погашенные купоны', 'data' => $date_array);
        echo json_encode($json);

    }


    public function action_export()
    {
	    $deal_id = $this->request->param('id');
        $csv = '';
        $csv .= '"user_id","coupon","discharge_date"' . "\n";
        $coupons = ORM::factory('coupon')->where('deal_id', '=', $deal_id)->where('is_discharge', '=', 1)->find_all();
        foreach ($coupons as $coupon)
        {
            $csv .= '"' . $coupon->user_id . '","' . $coupon->coupon . '","' . date("d-m-Y", $coupon->discharge_date) . '"' . "\n";
        }


        //Передам браузеру заголовки, что будет передоваться файл 'csv'
        header("Content-type: csv/plain");

        //Передам браузеру заголовки, как будет называться файл
        header("Content-Disposition: attachment; filename=export" . date("Y-m-d") . ".csv");

        //Если известен размер передаем размер
        header("Content-length:" . (string)(strlen($csv)));
        echo $csv;
        $this->auto_render = false;

    }

    public function action_usersreg()
    {
        $view = View::factory('admin/statistic/usersreg');
        $this->template->content = (string)$view;
    }

	public function action_usertags(){
		$view = View::factory('admin/statistic/usertags');
		$view->tags = ORM::factory('usertags')->find_all();
		$this->template->content = (string)$view;
	}

	public function action_usertags_getdata(){
		$tag_id = Arr::get($_POST,'tag_id',0);
		$start_date = $_POST['start_date'];
		$end_date = $_POST['end_date'];
		$dateUnix = strtotime($end_date);
		$start_date_conv = strtotime($start_date);
		$end_date_conv = mktime(23,59,59,date("m",$dateUnix),date("d",$dateUnix),date("Y",$dateUnix));

		$users_in_period = ORM::factory('usertags',$tag_id)->users
			->where('users.created','>=',$start_date_conv)
			->where('users.created','<=',$end_date_conv);
		$user_count = clone $users_in_period;
		$user_count = $user_count->count_all();


		$users_coupons = $users_in_period->join('coupons', 'RIGHT')
			->on('users.id', '=', 'coupons.user_id');

		$users_distinct = clone $users_coupons;
		$users_distinct = $users_distinct->group_by('coupons.user_id')->find_all()->count();



		$users_coupons_count = clone $users_coupons;
		$users_coupons_count = $users_coupons_count->count_all();



		$users_coupons_discharge = clone $users_coupons;
		$users_coupons_discharge = $users_coupons_discharge->where('coupons.is_discharge','=',1)->count_all();



		$this->auto_render = false;
		$ret = new jsonResponse(1);
		$ret->data = array(
			'users_count' => $user_count,
			'users_distinct' => $users_distinct,
			'users_coupons' => $users_coupons_count,
			'users_discharge' => $users_coupons_discharge
		);

		echo $ret->encode();




	}


}