<?php

namespace Application\Model\Param\Storage;
use Application\Model\Param\Storage;
use Khansia\Generic\Result as Result;
use Zend\Db\ResultSet\ResultSet;

class Mysql extends \Khansia\Db\Storage implements Skeleton {
    
    private $_result;
	
	const LOP 				    = 'lop_cluster';
    const JOIN_LEFT			    = 'left';
    const ORDERHEADER 			= 'order_header';
	const MIDUSER		        = 'user_data_header';
	const DATAUserexTYPE		= 'user_data_role';
	const USERACCESS			= 'user_data_map';
	 
    public function array_change_key_case_recursive($input, $case = CASE_LOWER){ 
        if(!is_array($input)){ 
            trigger_error("Invalid input array '{$array}'",E_USER_NOTICE); exit; 
        } 
        // CASE_UPPER|CASE_LOWER 
        if(null === $case){ 
            $case = CASE_LOWER; 
        } 
        if(!in_array($case, array(CASE_UPPER, CASE_LOWER))){ 
            trigger_error("Case parameter '{$case}' is invalid.", E_USER_NOTICE); exit; 
        } 
        $input = array_change_key_case($input, $case); 
        foreach($input as $key=>$array){ 
            if(is_array($array)){ 
                $input[$key] = $this->array_change_key_case_recursive($array, $case); 
            } 
        } 
        return $input; 
    } 
    
    public function __construct(\Zend\Db\Adapter\Adapter $adapter, $config = array()) {

		parent::__construct($adapter, $config);
		 /* get conn instance */
      $this->_conn = $adapter->getDriver()->getConnection()->getResource();
   
	  
        //print_r($config);
        if (isset($config['tables'])) {
            $tables = $config['tables'];
            foreach ($tables as $key => $value) {
                if (array_key_exists($key, $this->_tables) && $value) {
                    $this->_tables[$key] = $this->_($value);
                }
            }
        }
    }
    
    public function fetchAll(\Zend\Db\Sql\Select $select, $raw = true){

        $statement = $this->_sql->prepareStatementForSqlObject($select);
        if ($result = $statement->execute()) {
            $resultset = new \Zend\Db\ResultSet\ResultSet();
            $data = $resultset->initialize($result)->toArray();
            return $data;
        }

        return false;
    }


    public function deleteGlobal($tabel, $where){
       
        $stmt = $this->_db->query("delete from $tabel where $where");

        return $stmt->execute();
    }
    
    public function saveGlobal($atribut, $table){
        
        $insert = $this->_sql->insert()
                ->into($table)
                ->values($atribut);
                
        $result = $this->execute($insert);
        return $result;

    }

    public function updateGlobal($tabel, $data, $where){
		//print_r($data);die;
		$update = $this->_sql->update()
            ->table($tabel)
            ->set($data)
			->where($where);
            // echo str_replace('"','',$update->getSqlString());die;
        $result = $this->execute($update);
        return $result;
    }

    public function getLastSeqPostgree($tabel, $column){
        $result = new Result();

        $sql        = " select max($column) as total from $tabel ";
        
        $stmt       = $this->_db->query($sql);

        $proced     = $stmt->execute();

        $seq        = $proced->current();

        if ($seq) {
            $result->code = 0;
            $result->info = 'OK';
            $result->data = $seq;            
        } else {
            $result->code = 1;
            $result->info = 'Seq error';
        }

        return $result;

    }

     /* cek duplikasi data */
     public function checkDuplicateData($table, $column, $value, $msg){
        $result = new Result();
        try {
            
            $select = $this->select()
                    ->from($table)
                    ->where(array($column => $value));
            //echo str_replace('"','',$select->getSqlString());die;
            $return = $this->fetchAll($select);
            if ($return) {
                
                $result->code = 100;
                $result->info = 'DUPLICATE "'.$msg.'" ';
                $result->data = $return;
                
                
            } else {
                $result->code = 0;
                $result->info = 'nok';
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }


    public function loadParam($paramtype = NULL, $param_val3 = null, $param_parent = null){
		 $result = new Result();
        try {
				$select = $this->select()
						->from(array('' => 'master_parameter'));
                if($paramtype){
                    $select->where(array($this->_('param_type', '') . " = '" .$paramtype."'"));
                }
                if($param_val3){
                    $select->where(array($this->_('param_val3', '') . " = '" .$param_val3."'"));
                }
                if($param_parent){
                    $select->where(array($this->_('param_parent', '') . " = '" .$param_parent."'"));
                }

                $select->order(array('idm_parameter ASC'));
			// echo str_replace('"','',$select->getSqlString());die;

			$return = $this->fetchAll($select);

            if ($return) {

                $result->code = 0;
                $result->info = 'OK';
                $result->data = $return;


            } else {
                $result->code = 1;
                $result->info = 'nok';
            }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    /* load data mitra */
	public function loadProfileMap($role) {
		$result = new Result();
	   	try {

			$sql = " 	select am.map_id, ua.access_name, ua.access_code, am.access_status 
						from user_data_access ua, user_data_map am
						where ua.access_code=am.access_code and am.role_code='$role' ";
            
            //print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
            $listdata   = array();
            
            foreach($resdata as $val){
          
                    // print_r($val);die;
                array_push($listdata, $val);

            }

			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
    }

    /* get data tag  */
	public function getTypepost(){
		$result = new Result();
		
		try{
			$select =  $this->select()
						->from(self::DATAUserexTYPE);
			
			if($data = $this->fetchAll($select)){
				$result->code = 0;
				$result->info = 'OK';
				$result->data = array_change_key_case($data);
			}else{
				$result->code = 1;
				$result->info = 'not found';
				$result->data = array_change_key_case($data);
			}
			
		}catch (\Exception $ex) {
			$result->code = 2;
			$result->info = 'Error:' . $ex->getMessage();
			$result->data = $ex->getMessage();
		}
		
		return $result;
		
    }
    
    /* load data user */
	public function getUserData($tipe = null, $id = null){
		$result = new Result();
	   	try {

			$sql = "    SELECT
                        CASE
                                concat ( 'Regional ', A.regional_id ) 
                                WHEN 'Regional ' THEN
                                '-' ELSE concat ( 'Regional ', A.regional_id ) 
                            END Regional,
                            A.*,
                            b.NAME role_name,
                            dw.divisi_witel_name witel 
                        FROM
                            user_data_header
                            A LEFT JOIN user_data_role b ON A.ROLE = b.access_role_code
                            LEFT JOIN divisi_witel dw ON A.witel_id = dw.divisi_witel_id 
                        WHERE
                            iduser IS NOT NULL"; 

		   
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
    }

    /* load data user */
	 public function loadUserData($id = null){
		$result = new Result();
	   	try {

            $sql = "  SELECT * FROM masiwa WHERE id IS NOT NULL "; 
            
            if($id){
                $sql .= " AND id= $id";
            }

            $sql .= " ORDER BY create_date ASC";

		    // print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
    }

    //load data chart
    public function loadChartData($idweb){
        $result = new Result();
	   	try {

            $sql = "SELECT userweb_info.id_location, userweb_location.city, count(userweb_info.id_location) as sumlocation  FROM userweb_info
					INNER JOIN userweb_location
					ON userweb_location.id_location=userweb_info.id_location
					WHERE userweb_info.id_web = $idweb
					GROUP BY userweb_location.id_location, userweb_info.id_location
					ORDER BY sumlocation desc
					limit 5";
     
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
    }

	public function loadChartDataimage($idweb, $country, $province, $city){
        $result = new Result();
	   	try {
			$sql = "SELECT ui.nodeimage,  count(*) FROM userweb_detailimage ud
					INNER JOIN userweb_image ui
					ON ui.id_image = ud.id_image
					INNER JOIN userweb_info uin
					ON uin.id_user = ud.id_user
					INNER JOIN userweb_location ulo
					ON ulo.id_location = uin.id_location
					WHERE uin.id_web  = $idweb";
			
			
			if($country && $province && $city){
				$sql .= "AND ulo.country = '$country' AND ulo.province='$province' AND ulo.city='$city'";
			}else if ($country && $province){
				$sql .= "AND ulo.country = '$country' AND ulo.province='$province'";
			}else if($country){
				$sql .= "AND ulo.country = '$country'";
			}

			$sql .= "GROUP BY ud.id_image, ui.id_image
						ORDER BY count DESC
						LIMIT 5";
			// print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
    }

    public function loadTableUserData($idweb){
        $result = new Result();
	   	try {

            $sql = "  SELECT ui.id_user, ui.ip_address_client, ui.mac_address, ul.city, ub.browser_name, ui.ip_address_public, ui.date_first_visit FROM userweb_info ui
					INNER JOIN userweb_browser ub
					ON ub.id_browser = ui.id_browser
					INNER JOIN userweb_location ul
					ON ul.id_location = ui.id_location
					WHERE ui.id_web = $idweb
					ORDER BY ui.date_first_visit DESC "; 
            
           

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}
	public function loadtablepage($idweb){
        $result = new Result();
	   	try {

            $sql = "SELECT ui.ip_address_client ,up.name_page , ud.date_access, ud.time_access, ud.total_time FROM userweb_detailpage ud
					INNER JOIN userweb_info ui
					ON ui.id_user = ud.id_user
					INNER JOIN userweb_page up
					ON up.id_page = ud.id_page
					WHERE up.id_web = $idweb
					ORDER BY ud. date_access DESC, ud.time_access DESC"; 
            

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

	public function loadtablebutton($idweb){
        $result = new Result();
	   	try {

            $sql = "SELECT ui.ip_address_client, ub.nodebtn, ud.date, ud.time from userweb_detailbutton ud
			INNER JOIN userweb_info ui
			ON ui.id_user = ud.id_user
			INNER JOIN userweb_button ub
			ON ub.id_btn = ud.id_btn
			WHERE ui.id_web = $idweb
			ORDER BY ud.date DESC, ud.time DESC"; 
            

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}
	public function loadtablebrowser($idweb){
        $result = new Result();
	   	try {

            $sql = "SELECT ui.ip_address_client ,ub.browser_name, ub.browser_ver FROM userweb_info ui
					INNER JOIN userweb_browser ub
					ON ui.id_browser = ub.id_browser 
					WHERE ui.id_web = $idweb
					ORDER BY ui.date_first_visit DESC"; 
            

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}
	
	public function loadtablevideo($idweb){
        $result = new Result();
	   	try {

            $sql = "SELECT ui.ip_address_client, uv.nodevideo, ud.date, ud.time FROM userweb_detailvideo ud
			INNER JOIN userweb_info ui
			ON ui.id_user = ud.id_user
			INNER JOIN userweb_video uv
			ON uv.id_video = ud.id_video
			WHERE ui.id_web = $idweb			
			ORDER BY ud.date DESC, ud.time DESC"; 
            

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

	public function loadtableimage($idweb){
        $result = new Result();
	   	try {

            $sql = "SELECT ui.ip_address_client, uim.nodeimage, ud.date, ud.time FROM userweb_detailimage ud
			INNER JOIN userweb_info ui
			ON ui.id_user = ud.id_user
			INNER JOIN userweb_image uim
			ON uim.id_image= ud.id_image
			WHERE ui.id_web = $idweb
			ORDER BY ud.date DESC, ud.time DESC"; 
            

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

	public function loadtableother($idweb){
        $result = new Result();
	   	try {

            $sql = "SELECT ui.ip_address_client, uo.nodeother, ud.date, ud.time FROM userweb_detailother ud
					INNER JOIN userweb_info ui
					ON ui.id_user = ud.id_user
					INNER JOIN userweb_other uo
					ON uo.id_other= ud.id_other
					WHERE ui.id_web = $idweb
					ORDER BY ud.date DESC, ud.time DESC"; 
            

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

	public function loadcountry($idweb){
        $result = new Result();
	   	try {

            $sql = "SELECT ul.country FROM userweb_info ui
			INNER JOIN userweb_location ul
			ON ul.id_location = ui.id_location
			WHERE ui.id_web = $idweb
			GROUP BY ul.country"; 
            

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

	public function loadprovince($idweb, $country){
        $result = new Result();
	   	try {

            $sql = "SELECT ul.province FROM userweb_info ui
					INNER JOIN userweb_location ul
					ON ul.id_location = ui.id_location
					WHERE ui.id_web = $idweb AND ul.country='$country'
					GROUP BY ul.province"; 
            

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}
	
	public function loadcity($idweb, $country, $city){
        $result = new Result();
	   	try {

            $sql = "SELECT ul.city FROM userweb_info ui
					INNER JOIN userweb_location ul
					ON ul.id_location = ui.id_location
					WHERE ui.id_web = $idweb AND ul.country='$country' AND ul.province='$city'
					GROUP BY ul.city"; 
            

		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
				array_push($listdata, $val);
				
            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

    public function loadAreaPengunjungData($id = null){
        $result = new Result();
	   	try {

            $sql = "  SELECT * FROM pengunjung WHERE id IS NOT NULL "; 
            
            if($id){
                $sql .= " AND id= $id";
            }

            $sql .= " ORDER BY id ASC";

		    //print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
    }

    public function loadBarHalamanData($idweb){
        $result = new Result();
	   	try {

            $sql = "SELECT up.name_page, count(*) FROM userweb_detailpage ud
					INNER JOIN userweb_page up
					ON ud.id_page = up.id_page
					WHERE up.id_web = $idweb
					GROUP BY ud.id_page, up.id_page
					ORDER BY count DESC
					LIMIT 5
					"; 

		    //print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
    }

    public function loadBarButtonData($idweb, $country, $province, $city){
        $result = new Result();
	   	try {

            $sql = "SELECT ui.nodebtn, count(*) FROM userweb_detailbutton ud
					INNER JOIN userweb_button ui
					ON ui.id_btn = ud.id_btn
					INNER JOIN userweb_info uin
					ON uin.id_user = ud.id_user
					INNER JOIN userweb_location ulo
					ON ulo.id_location = uin.id_location
					WHERE uin.id_web = $idweb"; 
            
			if($country && $province && $city){
				$sql .= "AND ulo.country = '$country' AND ulo.province='$province' AND ulo.city='$city'";
			}else if ($country && $province){
				$sql .= "AND ulo.country = '$country' AND ulo.province='$province'";
			}else if($country){
				$sql .= "AND ulo.country = '$country'";
			}

			$sql .= "GROUP BY ud.id_btn, ui.id_btn
					ORDER BY count DESC
					LIMIT 6";
					
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
    }


    public function userLoginData(){
        $result = new Result();
	   	try {

            $sql = "  SELECT * FROM plugin_user"; 
            
            if($id){
                $sql .= " AND id= $id";
            }

            //$sql .= " ORDER BY jumlah DESC";

		    //print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}
	
	public function loadWebsiteUser($id){
		$result = new Result();
	   	try {

            //$sql = "  SELECT * FROM website_list WHERE id_user=".$id; 
            	$sql = "  SELECT * FROM website_list"; 
            if($id){
                $sql .= " WHERE id_user= $id";
            }

            // $sql .= " ORDER BY jumlah DESC";

		    //print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

	//load table user plugin
	public function loadUsername($id){
		$result = new Result();
	   	try {

            $sql = "  SELECT * FROM user_data_header"; 
            
            if($id){
                $sql .= " WHERE iduser= $id";
            }

             $sql .= " ORDER BY iduser ASC";

		    //print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

	public function validateWebToken($websiteName, $token){
		$result = new Result();
	   	try {

            $sql = "SELECT 
						COUNT(*) 
					FROM 
						website_list
					WHERE
						website_name='$websiteName' AND token='$token';"; 
            
            // if($id){
            //     $sql .= " AND id= $id";
            // }

            // $sql .= " ORDER BY jumlah DESC";

		    //print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

	public function loadtokenid($token){
		$result = new Result();
	   	try {

            $sql = "  SELECT id_web FROM website_list"; 
            
            if($token){
                $sql .= " WHERE token= $token";
            }

            //$sql .= " ORDER BY iduser ASC";

		    //print_r($sql);die;
		    $stmt       = $this->_db->query($sql);
		    $resdata    = $stmt->execute();
		   
		    $listdata = array();
		    foreach($resdata as $val){
          
                // print_r($val);die;
                array_push($listdata, $val);

            }
			
		    if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
		    }else{
			   $result->code = 1;
			   $result->info = 'nok';
		    }
		   
	    }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
	    }
	   return $result;
	}

	public function loadkey($key){
		$result = new Result();
		   try {
			$sql = "SELECT token FROM website_list where token='$key'";
			 
			
			$stmt       = $this->_db->query($sql);
			$resdata    = $stmt->execute();
			$listdata = array();
			foreach($resdata as $val){
		  
				// print_r($val);  
				array_push($listdata, $val);

			}
			
			if ($listdata) {
			   $result->code = 0;
			   $result->info = 'OK';
			   $result->data = $listdata;
			}else{
			   $result->code = 1;
			   $result->info = 'nok';
			}
		   
		}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
		   $result->code = 3;
		   $result->info = 'ERROR : ' . $ex->getMessage();
		}catch (\Exception $ex) {
		   $result->code = 4;
		   $result->info = 'ERROR : ' . $ex->getMessage();
		}
	   return $result;
	}
	
	public function setuserdb($dataarray, $location, $browser, $nameweb, $page){
		$result = new Result();
		try{
			// print_r($page);  
			$namepage = array(
				'name_page' 	=> $page['name_page'],
			);
			$detailpage = array(
				'date_access'	=> $page['date_access'],
				'time_access'	=> $page['time_access'],
				'total_time'	=> $page['total_time'],
			);
			$wherepage = array(
				'date_access'	=> $page['date_access'],
				'time_access'	=> $page['time_access'],
			);
			// print_r($wherepage);  

			$datefirstvisit = $page['date_access'];

			$selectbrowser = $this->select()
				-> from('userweb_browser')
				-> where($browser);
			$returnbrowser = $this->fetchAll($selectbrowser);

			if($returnbrowser){
				$idbrowser = $returnbrowser[0]['id_browser'];
			}else{
				$insertbrowser = $this->_sql->insert()
					->into('userweb_browser')
					->values($browser);
				$resultbrowser = $this->execute($insertbrowser);
				
				$selectbrowser = $this->select()
					-> from('userweb_browser')
					-> where($browser);
				$returnbrowser = $this->fetchAll($selectbrowser);
			}

			$selectlocation = $this->select()
				-> from('userweb_location')
				-> where($location);
			$returnlocation = $this->fetchAll($selectlocation);

			if($returnlocation){
				$idlocation = $returnlocation[0]['id_location'];
			}else{
				$insert = $this->_sql->insert()
					->into('userweb_location')
					->values($location);
				$valinsert = $this->execute($insert);
				
				$selectlocation = $this->select()
					-> from('userweb_location')
					-> where($location);
				$returnlocation = $this->fetchAll($selectlocation);
				$idlocation = $returnlocation[0]['id_location'];
			}
			
			$selectweb = $this->select()
				-> from('website_list')
				-> where(array('website_name'=>$nameweb));
			$returnweb = $this->fetchAll($selectweb);

			$selectpage = $this->select()
				-> from('userweb_page')
				-> where($namepage+array('id_web'=>$returnweb[0]['id_web']));
			$returnpage = $this->fetchAll($selectpage);
			
			$selectuser = $this->select()
				->from('userweb_info')
				->where($dataarray+array('id_browser'=>$idbrowser));
			$returnuser = $this->fetchAll($selectuser);

			$selectdetailpage = $this->select()
				-> from('userweb_detailpage')
				-> where($wherepage+array('id_page'=>$returnpage[0]['id_page'],'id_user'=>$returnuser[0]["id_user"]));
			$returndetailpage = $this->fetchAll($selectdetailpage);

			// print_r($returnuser[0]["id_user"]);  
			// print_r($returndetailpage);  

			if($returnweb){
				$idweb = $returnweb[0]['id_web'];
			}

			
			if ($returnuser ) {
				if($returnpage){ 
					if($returndetailpage){
						$update = $this->_sql->update()
							->table('userweb_detailpage')
							->set(array('total_time'=>$page['total_time']))
							->where($wherepage+array('id_page'=>$returnpage[0]['id_page'],'id_user'=>$returnuser[0]["id_user"]));
						$result = $this->execute($update);
					}else{
						$insertdetailpage = $this->_sql->insert()
							->into('userweb_detailpage')
							->values($detailpage+array('id_page'=>$returnpage[0]['id_page'], 'id_user'=>$returnuser[0]["id_user"]));
						$resultdetailpage = $this->execute($insertdetailpage);
					}
				}else{
					$idweb = $returnweb[0]['id_web'];
					
					$insertpage = $this->_sql->insert()
						->into('userweb_page')
						->values($namepage+array('id_web'=>$idweb));
					$resultpage = $this->execute($insertpage);

					$selectpage = $this->select()
						-> from('userweb_page')
						-> where($namepage+array('id_web'=>$returnweb[0]['id_web']));
					$returnpage = $this->fetchAll($selectpage);
					
					
					$insertdetailpage = $this->_sql->insert()
						->into('userweb_detailpage')
						->values($detailpage+array('id_page'=>$returnpage[0]['id_page'],'id_user'=>$returnuser[0]["id_user"]));
					$resultdetailpage = $this->execute($insertdetailpage);
				}
				$result->code = 0;
				$result->info = 'Data user Exist';
			} else {
				if($returnbrowser && $returnlocation && $returnweb && $dataarray){
					$insert = $this->_sql->insert()
						->into('userweb_info')
						->values($dataarray+array('id_location'=>$idlocation, 'id_web'=>$idweb, 'date_first_visit'=>$datefirstvisit, 'id_browser'=>$returnbrowser[0]['id_browser']));
					$result = $this->execute($insert);
					
					$selectbrowser = $this->select()
						-> from('userweb_browser')
						-> where($browser);
					$returnbrowser = $this->fetchAll($selectbrowser);
			
					$idbrowser = $returnbrowser[0]['id_browser'];

					$selectuser = $this->select()
						->from('userweb_info')
						->where($dataarray+array('id_browser'=>$idbrowser));
					$returnuser = $this->fetchAll($selectuser);

					$selectpage = $this->select()
						-> from('userweb_page')
						-> where($namepage+array('id_web'=>$returnweb[0]['id_web']));
					$returnpage = $this->fetchAll($selectpage);
					
					if($returnpage){
						$insertdetailpage = $this->_sql->insert()
							->into('userweb_detailpage')
							->values($detailpage+array('id_page'=>$returnpage[0]['id_page'], 'id_user'=>$returnuser[0]["id_user"]));
						$resultdetailpage = $this->execute($insertdetailpage);
					}else{
						$insertpage = $this->_sql->insert()
						->into('userweb_page')
						->values($namepage+array('id_web'=>$idweb));
						$resultpage = $this->execute($insertpage);

						$selectpage = $this->select()
							-> from('userweb_page')
							-> where($namepage+array('id_web'=>$returnweb[0]['id_web']));
						$returnpage = $this->fetchAll($selectpage);
					
						$insertdetailpage = $this->_sql->insert()
							->into('userweb_detailpage')
							->values($detailpage+array('id_page'=>$returnpage[0]['id_page'],'id_user'=>$returnuser[0]["id_user"]));
						$resultdetailpage = $this->execute($insertdetailpage);
					}	

					$result->code = 0;
					$result->info = 'Data Insert';
				}else{
					$result->code = 2;
					$result->info = 'Data not found';
				}
			}
		}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
			$result->code = 3;
			$result->info = 'ERROR : ' . $ex->getMessage();
		 }catch (\Exception $ex) {
			$result->code = 4;
			$result->info = 'ERROR : ' . $ex->getMessage();
		 }
		 return $result;
	}

	public function namewebsite($token){
		$result = new Result();
		try{
			$select = $this->select()
                    ->from('website_list')
					->where(array("token"=>$token));
			$return = $this->fetchAll($select);

			$result->code = 0;
			$result->info = 'Ok';
			$result->data = $return[0]['website_name'];

		}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
			$result->code = 3;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}catch (\Exception $ex) {
			$result->code = 4;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}
		return $result;
	}
	
	public function getweb($token){
		$result = new Result();
		try{
			$select = $this->select()
					->from('website_list')
					->where(array("token"=>$token));
			$return = $this->fetchAll($select);

			$result->code = 0;
			$result->info = 'Ok';
			$result->data = $return;

		}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
			$result->code = 3;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}catch (\Exception $ex) {
			$result->code = 4;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}
		return $result;
	}


	public function setbehavbtn($behavbtn, $page, $web, $user, $browser){
		$result = new Result();
		try{
			$namepage = $page['name_page'];

			$selectweb = $this->select()
				->from('website_list')
				->where(array("website_name"=>$web));
			$returnweb = $this->fetchAll($selectweb);

			$selectpage = $this->select()
				->from('userweb_page')
				->where(array('name_page'=>$namepage, 'id_web'=>$returnweb[0]['id_web']));
			$returnpage = $this->fetchAll($selectpage);
			
			$selectbtn = $this->select()
				->from('userweb_button')
				->where(array('nodebtn'=>$behavbtn['nodebtn'], 'id_page'=>$returnpage[0]['id_page']));
			$returnbtn = $this->fetchAll($selectbtn);

			$selectbrowser = $this->select()
				-> from('userweb_browser')
				-> where($browser);
			$returnbrowser = $this->fetchAll($selectbrowser);
			
			$idbrowser = $returnbrowser[0]['id_browser'];

			$selectuser = $this->select()
				->from('userweb_info')
				->where($user+array('id_browser'=>$idbrowser));
			$returnuser = $this->fetchAll($selectuser);

			$selectbtndetail = $this->select()
				->from('userweb_detailbutton')
				->where(array('id_btn'=>$returnbtn[0]['id_btn'],'id_user'=>$returnuser[0]["id_user"], 'date'=>$behavbtn['date'], 'time'=>$behavbtn['time'] ));
			$returnbtndetail = $this->fetchAll($selectbtndetail);
			
			if($returnbtn){
				if($returnbtndetail){
					$result->code = 0;
					$result->info = 'behavior button detail exists';
				}else{
					$insertbtndetail = $this->_sql->insert()
						->into('userweb_detailbutton')
						->values(array('id_btn'=>$returnbtn[0]['id_btn'], 'date'=>$behavbtn['date'], 'time'=>$behavbtn['time'],'id_user'=>$returnuser[0]["id_user"]));
					$resultbtndetail = $this->execute($insertbtndetail);

					$result->code = 0;
					$result->info = 'behavior button insert';
				}
			}else{
				$insertbtn = $this->_sql->insert()
					->into('userweb_button')
					->values(array('id_page'=>$returnpage[0]['id_page'], 'nodebtn'=>$behavbtn['nodebtn']));
				$resultbtn = $this->execute($insertbtn);

				$selectbtn = $this->select()
					->from('userweb_button')
					->where(array('nodebtn'=>$behavbtn['nodebtn'], 'id_page'=>$returnpage[0]['id_page']));
				$returnbtn = $this->fetchAll($selectbtn);

				$insertbtndetail = $this->_sql->insert()
					->into('userweb_detailbutton')
					->values(array('id_btn'=>$returnbtn[0]['id_btn'], 'date'=>$behavbtn['date'], 'time'=>$behavbtn['time'],'id_user'=>$returnuser[0]["id_user"]));
				$resultbtndetail = $this->execute($insertbtndetail);

				$result->code = 0;
				$result->info = 'behavior button and detail button insert';
			}
		}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
			$result->code = 3;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}catch (\Exception $ex) {
			$result->code = 4;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}
		return $result;
	}

	public function setbehavimg($behavimage, $page, $web, $user, $browser){
		$result = new Result();
		try{
			$namepage = $page['name_page'];

			$selectweb = $this->select()
					->from('website_list')
					->where(array("website_name"=>$web));
			$returnweb = $this->fetchAll($selectweb);

			$selectpage = $this->select()
				->from('userweb_page')
				->where(array('name_page'=>$namepage, 'id_web'=>$returnweb[0]['id_web']));
			$returnpage = $this->fetchAll($selectpage);

			$selectbrowser = $this->select()
				-> from('userweb_browser')
				-> where($browser);
			$returnbrowser = $this->fetchAll($selectbrowser);
			
			$idbrowser = $returnbrowser[0]['id_browser'];

			$selectuser = $this->select()
				->from('userweb_info')
				->where($user+array('id_browser'=>$idbrowser));
			$returnuser = $this->fetchAll($selectuser);

			$selectimage = $this->select()
				->from('userweb_image')
				->where(array('nodeimage'=>$behavimage['nodeimg'], 'id_page'=>$returnpage[0]['id_page']));
			$returnimage = $this->fetchAll($selectimage);

			$selectdetailimg = $this->select()
				->from('userweb_detailimage')
				->where(array('id_image'=>$returnimage[0]['id_image'],'id_user'=>$returnuser[0]["id_user"], 'date'=>$behavimage['date'], 'time'=>$behavimage['time'] ));
			$returndetailimg = $this->fetchAll($selectdetailimg);

			if($returnimage){
				if($returndetailimg){
					$result->code = 0;
					$result->info = 'behavior button detail exists';
				}else{
					
					$insertdetailimg = $this->_sql->insert()
						->into('userweb_detailimage')
						->values(array('id_image'=>$returnimage[0]['id_image'], 'date'=>$behavimage['date'], 'time'=>$behavimage['time'],'id_user'=>$returnuser[0]["id_user"]));
					$resultdetailimage = $this->execute($insertdetailimg);

					$result->code = 0;
					$result->info = 'behavior button insert';
				}
			}else{
				$insertimage = $this->_sql->insert()
					->into('userweb_image')
					->values(array('id_page'=>$returnpage[0]['id_page'], 'nodeimage'=>$behavimage['nodeimg']));
				$resultimage = $this->execute($insertimage);

				$selectimage = $this->select()
					->from('userweb_image')
					->where(array('nodeimage'=>$behavimage['nodeimg'], 'id_page'=>$returnpage[0]['id_page']));
				$returnimage = $this->fetchAll($selectimage);

				$insertdetailimg = $this->_sql->insert()
					->into('userweb_detailimage')
					->values(array('id_image'=>$returnimage[0]['id_image'], 'date'=>$behavimage['date'], 'time'=>$behavimage['time'],'id_user'=>$returnuser[0]["id_user"]));
				$resultdetailimage = $this->execute($insertdetailimg);

				$result->code = 0;
				$result->info = 'behavior button and detail button insert';
			}	

		}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
			$result->code = 3;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}catch (\Exception $ex) {
			$result->code = 4;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}
		return $result;
	}


	public function setbehavideo($behavideo, $page, $web, $user, $browser){
		$result = new Result();
		try{
			$namepage = $page['name_page'];

			$selectweb = $this->select()
					->from('website_list')
					->where(array("website_name"=>$web));
			$returnweb = $this->fetchAll($selectweb);

			$selectpage = $this->select()
				->from('userweb_page')
				->where(array('name_page'=>$namepage, 'id_web'=>$returnweb[0]['id_web']));
			$returnpage = $this->fetchAll($selectpage);

			$selectbrowser = $this->select()
				-> from('userweb_browser')
				-> where($browser);
			$returnbrowser = $this->fetchAll($selectbrowser);
			
			$idbrowser = $returnbrowser[0]['id_browser'];

			$selectuser = $this->select()
				->from('userweb_info')
				->where($user+array('id_browser'=>$idbrowser));
			$returnuser = $this->fetchAll($selectuser);

			$selectvideo = $this->select()
				->from('userweb_video')
				->where(array('nodevideo'=>$behavideo['nodevideo'], 'id_page'=>$returnpage[0]['id_page']));
			$returnvideo = $this->fetchAll($selectvideo);

			$selectdetailvideo = $this->select()
				->from('userweb_detailvideo')
				->where(array('id_video'=>$returnvideo[0]['id_video'],'id_user'=>$returnuser[0]["id_user"], 'date'=>$behavideo['date'], 'time'=>$behavideo['time'] ));
			$returndetailvideo = $this->fetchAll($selectdetailvideo);
			
			// print_r($returnvideo);  

			if($returnvideo){
				if($returndetailvideo){
					$result->code = 0;
					$result->info = 'behavior video detail exists';
				}else{
					$insertdetailvideo = $this->_sql->insert()
						->into('userweb_detailvideo')
						->values(array('id_video'=>$returnvideo[0]['id_video'], 'date'=>$behavideo['date'], 'time'=>$behavideo['time'],'id_user'=>$returnuser[0]["id_user"]));
					$resultdetailvideo = $this->execute($insertdetailvideo);

					$result->code = 0;
					$result->info = 'behavior video insert';
				}
			}else{
				$insertvideo = $this->_sql->insert()
					->into('userweb_video')
					->values(array('id_page'=>$returnpage[0]['id_page'], 'nodevideo'=>$behavideo['nodevideo']));
				$resultvideo = $this->execute($insertvideo);

				$selectvideo = $this->select()
					->from('userweb_video')
					->where(array('nodevideo'=>$behavideo['nodevideo'], 'id_page'=>$returnpage[0]['id_page']));
				$returnvideo = $this->fetchAll($selectvideo);


				$insertdetailvideo = $this->_sql->insert()
					->into('userweb_detailvideo')
					->values(array('id_video'=>$returnvideo[0]['id_video'], 'date'=>$behavideo['date'], 'time'=>$behavideo['time'],'id_user'=>$returnuser[0]["id_user"]));
				$resultdetailvideo = $this->execute($insertdetailvideo);

				$result->code = 0;
				$result->info = 'behavior button and detail button insert';
			}	
		}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
			$result->code = 3;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}catch (\Exception $ex) {
			$result->code = 4;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}
		return $result;
	}
	
	public function setbehavother($behavother, $page, $web, $user, $browser){
		// print_r($behavother['nodeother']);  
		$result = new Result();
		try{
			$namepage = $page['name_page'];

			$selectweb = $this->select()
					->from('website_list')
					->where(array("website_name"=>$web));
			$returnweb = $this->fetchAll($selectweb);

			$selectpage = $this->select()
				->from('userweb_page')
				->where(array('name_page'=>$namepage, 'id_web'=>$returnweb[0]['id_web']));
			$returnpage = $this->fetchAll($selectpage);

			$selectbrowser = $this->select()
				-> from('userweb_browser')
				-> where($browser);
			$returnbrowser = $this->fetchAll($selectbrowser);
			
			$idbrowser = $returnbrowser[0]['id_browser'];

			$selectuser = $this->select()
				->from('userweb_info')
				->where($user+array('id_browser'=>$idbrowser));
			$returnuser = $this->fetchAll($selectuser);

			$selectother = $this->select()
				->from('userweb_other')
				->where(array('nodeother'=>$behavother['nodeother'], 'id_page'=>$returnpage[0]['id_page']));
			$returnother = $this->fetchAll($selectother);
		
			
			$selectdetailother = $this->select()
				->from('userweb_detailother')
				->where(array('id_other'=>$returnother[0]['id_other'],'id_user'=>$returnuser[0]["id_user"], 'date'=>$behavother['date'], 'time'=>$behavother['time'] ));
			$returndetailother = $this->fetchAll($selectdetailother);
			
			if($returnother){
				if($returndetailother){
					$result->code = 0;
					$result->info = 'behavior other detail exists';
				}else{
					$insertdetailother = $this->_sql->insert()
						->into('userweb_detailother')
						->values(array('id_other'=>$returnother[0]['id_other'], 'date'=>$behavother['date'], 'time'=>$behavother['time'],'id_user'=>$returnuser[0]["id_user"]));
					$resultdetailother = $this->execute($insertdetailother);

					$result->code = 0;
					$result->info = 'behavior other insert';
				}
			}else{
				$insertother = $this->_sql->insert()
					->into('userweb_other')
					->values(array('id_page'=>$returnpage[0]['id_page'], 'nodeother'=>$behavother['nodeother']));
				$resultother = $this->execute($insertother);

				$selectother = $this->select()
					->from('userweb_other')
					->where(array('nodeother'=>$behavother['nodeother'], 'id_page'=>$returnpage[0]['id_page']));
				$returnother = $this->fetchAll($selectother);
				
				$insertdetailother = $this->_sql->insert()
					->into('userweb_detailother')
					->values(array('id_other'=>$returnother[0]['id_other'], 'date'=>$behavother['date'], 'time'=>$behavother['time'],'id_user'=>$returnuser[0]["id_user"]));
				$resultdetailother = $this->execute($insertdetailother);

				$result->code = 0;
				$result->info = 'behavior button and detail button insert';
			}		
		
		}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
			$result->code = 3;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}catch (\Exception $ex) {
			$result->code = 4;
			$result->info = 'ERROR : ' . $ex->getMessage();
		}
		return $result;
	}

public function totalvisitor($idweb){
	$result = new Result();

	$sql = "SELECT count(*) FROM userweb_info WHERE id_web = $idweb";
	
	$stmt       = $this->_db->query($sql);

	$proced     = $stmt->execute();

	$total       = $proced->current();

	if ($total) {
		$result->code = 0;
		$result->info = 'OK';
		$result->data = $total;            
	} else {
		$result->code = 1;
		$result->info = 'Seq error';
	}

	return $result;
}
public function totallocation($idweb){
	$result = new Result();

	$sql = "SELECT id_location FROM userweb_info WHERE id_web = $idweb GROUP BY id_location";

	$stmt       = $this->_db->query($sql);
	$resdata    = $stmt->execute();
   
	$listdata   = array();
	
	foreach($resdata as $val){
		array_push($listdata, $val);
	}

	if ($listdata) {
		$result->code = 0;
		$result->info = 'OK';
		$result->data = count($listdata);            
	} else {
		$result->code = 1;
		$result->info = 'Seq error';
	}

	return $result;
}

public function pageviews($idweb){
	$result = new Result();

	$sql = "SELECT count(*) FROM userweb_detailpage";

	$stmt       = $this->_db->query($sql);
	$proced    = $stmt->execute();
   
	$total       = $proced->current();

	if ($total) {
		$result->code = 0;
		$result->info = 'OK';
		$result->data = $total['count'];            
	} else {
		$result->code = 1;
		$result->info = 'Seq error';
	}

	return $result;
}

public function newvisitor($idweb){
	$result = new Result();

	$sql = "SELECT count(*) FROM  userweb_info WHERE date_first_visit > now() - interval '1 WEEK' AND id_web = $idweb";

	$stmt       = $this->_db->query($sql);
	$proced    = $stmt->execute();
   
	$total       = $proced->current();

	if ($total) {
		$result->code = 0;
		$result->info = 'OK';
		$result->data = $total['count'];            
	} else {
		$result->code = 1;
		$result->info = 'Seq error';
	}

	return $result;
}

public function loadyearvisitor($idweb){
	$result = new Result();
	// print_r($idweb);  
	$sql = "SELECT extract(year from date_first_visit) FROM userweb_info WHERE id_web = $idweb GROUP BY date_part" ;
	$stmt       = $this->_db->query($sql);
	$resdata    = $stmt->execute();
   
	$listdata = array();
	foreach($resdata as $val){
		array_push($listdata, $val);
	}

	// print_r($listdata);  
	if ($listdata) {
		$result->code = 0;
		$result->info = 'OK';
		$result->data = $listdata;            
	} else {
		$result->code = 1;
		$result->info = 'Seq error';
	}

	return $result;
}



public function loadnumbervisitor($idweb, $year){
	$result = new Result();
	// print_r($year);  
	$sql = "SELECT extract(month from ui.date_first_visit) as month, count(*) as total from userweb_info ui
			WHERE extract(year from ui.date_first_visit) = $year AND ui.id_web = $idweb
			GROUP BY month
			ORDER BY month asc" ;

	$stmt       = $this->_db->query($sql);
	$resdata    = $stmt->execute();
   
	$listdata = array();

	foreach($resdata as $val){
		array_push($listdata, $val);
	}

	if ($listdata) {
		$result->code = 0;
		$result->info = 'OK';
		$result->data = $listdata;            
	} else {
		$result->code = 1;
		$result->info = 'Seq error';
	}

	return $result;
}

public function loadPieBrowserData($idweb){
	$result = new Result();
	try {
		$sql = "SELECT ub.browser_name, count(*) as total from userweb_info ui 
				INNER JOIN userweb_browser ub
				ON ui.id_browser = ub.id_browser
				WHERE ui.id_web = $idweb
				GROUP BY ub.browser_name 
				LIMIT 5"; 
		
		$stmt       = $this->_db->query($sql);
		$resdata    = $stmt->execute();
	   
		$listdata = array();

		foreach($resdata as $val){
			array_push($listdata, $val);
		}
		
		if ($listdata) {
		   $result->code = 0;
		   $result->info = 'OK';
		   $result->data = $listdata;
		}else{
		   $result->code = 1;
		   $result->info = 'nok';
		}
	   
	}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
	   $result->code = 3;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}catch (\Exception $ex) {
	   $result->code = 4;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}
   return $result;
}

public function loadmonthvisitor($idweb, $year){
	$result = new Result();
	try {
		$sql = "SELECT  extract(month from date_first_visit) AS month 
				FROM userweb_info 
				WHERE id_web = $idweb AND extract(year from date_first_visit) =  $year
				GROUP BY month";
		
		$stmt       = $this->_db->query($sql);
		$resdata    = $stmt->execute();
	   
		$listdata = array();

		foreach($resdata as $val){
			array_push($listdata, $val);
		}
		
		if ($listdata) {
		   $result->code = 0;
		   $result->info = 'OK';
		   $result->data = $listdata;
		}else{
		   $result->code = 1;
		   $result->info = 'nok';
		}
	   
	}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
	   $result->code = 3;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}catch (\Exception $ex) {
	   $result->code = 4;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}
	return $result;
}

public function loadvisitorbymonth($idweb, $year, $month){
	$result = new Result();
	try {
		$sql = "SELECT 1 + FLOOR((EXTRACT(DAY FROM date_first_visit) - 1) / 7) as week, count(*) as total
				FROM userweb_info
				WHERE extract(month from date_first_visit) = $month AND extract(year from date_first_visit)  = $year AND id_web = $idweb
				GROUP BY week
				ORDER BY week ASC";
		
		$stmt       = $this->_db->query($sql);
		$resdata    = $stmt->execute();
	   
		$listdata = array();

		foreach($resdata as $val){
			array_push($listdata, $val);
		}
		
		if ($listdata) {
		   $result->code = 0;
		   $result->info = 'OK';
		   $result->data = $listdata;
		}else{
		   $result->code = 1;
		   $result->info = 'nok';
		}
	   
	}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
	   $result->code = 3;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}catch (\Exception $ex) {
	   $result->code = 4;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}
	return $result;
}


public function loadbrowserbylocat($idweb, $city, $province, $country){
	$result = new Result();
	try {
		$sql = "SELECT ub.browser_name, count(*) AS total FROM userweb_info ui 
				INNER JOIN userweb_browser ub
				ON ui.id_browser = ub.id_browser
				INNER JOIN userweb_location ul
				ON ui.id_location = ul.id_location
				WHERE ui.id_web = $idweb";

	
		
		if($country && $province && $city){
			$sql .= "AND ul.country = '$country' AND ul.province='$province' AND ul.city='$city'";
		}else if ($country && $province){
			$sql .= "AND ul.country = '$country' AND ul.province='$province'";
		}else if($country){
			$sql .= "AND ul.country = '$country'";
		}

		$sql .= "GROUP BY ub.browser_name
				ORDER BY total DESC	
				LIMIT 5";

		$stmt       = $this->_db->query($sql);
		$resdata    = $stmt->execute();
	   
		$listdata = array();

		foreach($resdata as $val){
			array_push($listdata, $val);
		}
		
		if ($listdata) {
		   $result->code = 0;
		   $result->info = 'OK';
		   $result->data = $listdata;
		}else{
		   $result->code = 1;
		   $result->info = 'nok';
		}
	   
	}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
	   $result->code = 3;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}catch (\Exception $ex) {
	   $result->code = 4;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}
	return $result;
}


public function loadpagebylocat($idweb, $city, $province, $country){
	$result = new Result();
	try {
		$sql = "SELECT  up.name_page, count(*) FROM userweb_detailpage ud
				INNER JOIN userweb_page up
				ON ud.id_page = up.id_page
				INNER JOIN userweb_info ui
				ON ud.id_user = ui.id_user
				INNER JOIN userweb_location ul
				ON ui.id_location = ul.id_location
				WHERE ui.id_web = $idweb";

	
		
		if($country && $province && $city){
			$sql .= "AND ul.country = '$country' AND ul.province='$province' AND ul.city='$city'";
		}else if ($country && $province){
			$sql .= "AND ul.country = '$country' AND ul.province='$province'";
		}else if($country){
			$sql .= "AND ul.country = '$country'";
		}

		$sql .= "GROUP BY up.id_page
				ORDER BY count DESC
				LIMIT 5";

		$stmt       = $this->_db->query($sql);
		$resdata    = $stmt->execute();
	   
		$listdata = array();

		foreach($resdata as $val){
			array_push($listdata, $val);
		}
		
		if ($listdata) {
		   $result->code = 0;
		   $result->info = 'OK';
		   $result->data = $listdata;
		}else{
		   $result->code = 1;
		   $result->info = 'nok';
		}
	   
	}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
	   $result->code = 3;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}catch (\Exception $ex) {
	   $result->code = 4;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}
	return $result;
}

public function loadvisitorbytime($idweb, $year, $month, $province, $country){
	$result = new Result();
	try {
		if( $province && $country){
			$sql = "SELECT userweb_location.city as location,";
		}else if($country){
			$sql = "SELECT userweb_location.province as location,";
		}else{
			$sql = "SELECT userweb_location.country as location,";
		}

		$sql .= "count(userweb_info.id_location) as total FROM userweb_info
				INNER JOIN userweb_location
				ON userweb_location.id_location=userweb_info.id_location
				WHERE userweb_info.id_web =  $idweb";

		if($year && $month ){
			$sql .= "AND EXTRACT(MONTH FROM userweb_info.date_first_visit) = $month AND EXTRACT(YEAR FROM userweb_info.date_first_visit) = $year";
		}else if ($year){
			$sql .= "AND EXTRACT(YEAR FROM userweb_info.date_first_visit) = $year";
		}


		if($province && $country){
			$sql .= "AND userweb_location.country = '$country' AND userweb_location.province='$province'";
		}else if ($country){
			$sql .= "AND userweb_location.country='$country'";
		}

		$sql .= "GROUP BY location
				ORDER BY total desc
				limit 5";
		
		$stmt       = $this->_db->query($sql);
		$resdata    = $stmt->execute();
	   
		$listdata = array();

		foreach($resdata as $val){
			array_push($listdata, $val);
		}
		
		if ($listdata) {
		   $result->code = 0;
		   $result->info = 'OK';
		   $result->data = $listdata;
		}else{
		   $result->code = 1;
		   $result->info = 'nok';
		}
	   
	}catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
	   $result->code = 3;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}catch (\Exception $ex) {
	   $result->code = 4;
	   $result->info = 'ERROR : ' . $ex->getMessage();
	}
	return $result;
}


}