<?php

	class SQL {
		
		private $dbHost, $dbUser, $dbPass, $dbName, $dbLink;
		
		public function __construct($dHost, $dUser, $dPass, $dName) {
			$this->dbHost = $dHost;
			$this->dbUser = $dUser;
			$this->dbPass = $dPass;
			$this->dbName = $dName;
			$this->connect();
		}
		
		private function connect() {
        	$this->dbLink = @pg_connect("host=".$this->dbHost." dbname=".$this->dbName." user=".$this->dbUser." password=".$this->dbPass);
			if(!$this->dbLink) {
				echo 'Database connection error';
				exit();
			}
    	}
		
		public function select($query) {
			$result	= @pg_query($query);
			if(!$result) {
				echo 'SQL ERROR: '.pg_last_error();
				exit();
			}
			else {
				if(!pg_num_rows($result)) return 0;
				else {
					$return	= array();
					while ($row = pg_fetch_array($result, NULL, PGSQL_ASSOC))
						array_push($return, $row);
					return $return;
				}
			}
		}
		
		public function query($query) {
			$result = @pg_query($query);
			if(!$result) {
				echo 'SQL ERROR: '.pg_last_error();
				exit();
			}
		}
		
		public function __destruct() {
       		@pg_close($this->dbLink);
   		}
		
	}
	
	
	
	class Reports {
		
		public function getproducts() {
			$query = "SELECT * FROM product";
			$result = SQL::select($query);
			return $result;
		}
		
		public function AffiliateDistributionofNUsers($validity, $dim, $prod, $activ) {
			$query = "SELECT affiliate, product_name, SUM(amount) FROM report.view_la_affiliate_distribution WHERE validity = '".$validity."' AND dim='".$dim."'";
			if($prod!="all") $query .= " AND product_id = '".$prod."'";
			if($activ!=0) $query .= " AND action_id = '".$activ."'";
			$query .= " GROUP BY product_name, affiliate";
			
			$result = SQL::select($query);
			return $result;
		}
		
		public function TopXNetworks($validity, $dim, $prod, $ntop) {
			$query = "SELECT * FROM report.view_ui_top_networks WHERE validity = '".$validity."' AND dim='".$dim."'";
			if($prod!="all") $query .= " AND product_id = '".$prod."'";
			$query .= " ORDER BY amount DESC";
			if(strlen($ntop)) $query .= " LIMIT ".$ntop;

			$result = SQL::select($query);
			return $result;
		}
		
		public function NetworkUsersDistributions($validity, $dim, $prod) {
			$query = "SELECT * FROM report.view_ui_network_users_distribution WHERE validity = '".$validity."' AND dim='".$dim."'";
			if($prod!="all") $query .= " AND product_id = '".$prod."'";

			$result = SQL::select($query);
			return $result;
		}

	}
	
	
	
	class Utils {

		/*private function strip_tags_attributes($string, $allowtags = '', $allowattributes = NULL) {
			if($allowattributes) {
          		if(!is_array($allowattributes)) $allowattributes = explode(",",$allowattributes);
          		if(is_array($allowattributes)) $allowattributes = implode("|",$allowattributes);
          		$rep = '/([^>]*) ('.$allowattributes.')(=)(\'.*\'|".*")/i';
          		$string = preg_replace($rep, '$1 $2_-_-$4', $string);
      		}
      		if(preg_match('/([^>]*) (.*)(=\'.*\'|=".*")(.*)/i',$string) > 0) {
          		$string = preg_replace('/([^>]*) (.*)(=\'.*\'|=".*")(.*)/i', '$1$4', $string);
     	 	}
      		$rep = '/([^>]*) ('.$allowattributes.')(_-_-)(\'.*\'|".*")/i';
      		if($allowattributes) $string = preg_replace($rep, '$1 $2=$4', $string);
      		return strip_tags($string, $allowtags);
		}
		
		public function fixEnconding($in_str) {
      		$cur_encoding = mb_detect_encoding($in_str);
      		return ($cur_encoding == "UTF-8" && mb_check_encoding($in_str, "UTF-8")) ? $in_str : utf8_encode($in_str);
    	}

		public function cleanQuery($string) {
			
			$string = trim($string);
			$string = htmlspecialchars($string);
			$string = str_replace(chr(10), "", $string);
			$string = str_replace(chr(13), "<br />", $string);

			// pt xss
			$string = $this->strip_tags_attributes($string);

			//pt sql injection
			if(get_magic_quotes_gpc()) $string = stripslashes($string);
			$string = $this->fixEnconding($string);
  			$string = pg_escape_string($string);
			
			return $string;
		}
		
		public function validate_integer($no) {
			if(!preg_match("/^\d+$/", $no)) return false;
			else return true;
		}*/
		
		public function getStartAndEndDate($d, $type) {
			if($type=="w") {
				$dotw = date('w', $d);
				$startend[0] = ($dotw == 0 /* Sunday */) ? $d : strtotime('last Sunday', $d);
				$startend[1] = ($dotw == 6 /* Saturday */) ? $d : strtotime('next Saturday', $d);
			}
			else if($type=="m") {
				$month = date('m', $d);
				$year = date('Y', $d);
				$days = date('t', $d);
				
				$startend[0] = strtotime($year."-".$month."-01");
				$startend[1] = strtotime($year."-".$month."-".$days);
			}
			else if($type=="y") {
				$year = date('Y', $d);
				
				$startend[0] = strtotime($year."-01-01");
				$startend[1] = strtotime($year."-12-31");
			}
			return $startend;
		}

    
	}
	

?>