<?php

class paar{
	private $db_conn;
	function __construct() 
	{
		$dsn  = 'host=192.168.9.109 ';
		$dsn .= ' port= 5432';
		$dsn .= ' dbname=paar';
		$dsn .= ' user=robert';
		$dsn .= ' password=robert';
		$this->db_conn = pg_connect($dsn);
	}

	public function getProductName()
	{
	      $val = "";
	      $query = "select name::character varying from product";
	      $res = pg_query($this->db_conn,$query);
	      while($row=pg_fetch_assoc($res)){
		  $val  .= $row['name'].";";
 	      }
	      return $val;
	}

      public function getTotalInstall_Uninstall($from,$to,$os,$action)
      {
	
	$return = '';
	$query  = "select * from report.return_install_uninstall_sum('$from','$to'";
	if($os != "All") $query .= ",'".preg_replace("/defice/","–",$os)."'";
	if($action> 0) $query .= ",$action";
	$query .=") as (\"OS\" character varying,\"32\" bigint,\"64\" bigint) ";


	$res = pg_query($this->db_conn,$query);
	      while($row=pg_fetch_assoc($res)){
		  $return  .= $row['OS'];
		  $return  .= "|";
		  $return  .= $row['32'];  
		  $return  .= "|";
		  $return  .= $row['64'];
		  $return  .= "|";
		  $return  .= $row['32'] + $row['64'];
		  $return  .= "\n";
 	      }
	      return $return;
	      //return $query;


      }

      public function getInstUninstPerPeriod($from,$to,$prod_id,$action,$steps='1 day')
      {
	
	$return = '';
	$query  = "select b.name,array_accum(b.\"validity\"||':'||b.\"Sum 32\"||':'||b.\"Sum 64\") as prod_data from
		    (select a.validity,a.\"name\",sum(a.sum_32)as \"Sum 32\",sum(a.sum_64) as \"Sum 64\" from
			  ((select validity::date,os.\"name\"::character varying,sum(amount) as sum_32,(0)as sum_64
			      from report.la_install_uninstall join product on (product.id = product_id) join os on (os_id=os.id)
			      where (validity > '$from' and validity <= '$to') and dim = '$steps' and os_plat_id = 32";
	//if($os != "All") $query .= " and product.\"name\" ='". preg_replace("/defice/","–",$os)."'";
	if($prod_id != "All") $query .= " and product_id =$prod_id";
	if($action> 0) $query.= "and action_id = $action ";
	$query.= "	  group by validity,os.\"name\" order by validity)
			  union 
			  (select validity::date,os.\"name\"::character varying,(0)as sum_32,sum(amount) as sum_64
			      from report.la_install_uninstall join product on (product.id = product_id) join os on (os_id=os.id)
			      where (validity > '$from' and validity <= '$to') and dim = '$steps' and os_plat_id = 64 ";
	if($prod_id != "All") $query .= " and product_id =$prod_id";
	if($action> 0) $query.= "and action_id = $action ";
	$query .="	  group by validity,os.\"name\" order by validity)) as a 
		      group by  a.validity,a.\"name\"	
		  order by a.\"validity\") as b group by  b.name";
	
	$res = pg_query($this->db_conn,$query);
	      while($row=pg_fetch_assoc($res)){
		  $return  .= $row['name'];
		  $return  .= "|";
		  $return  .= $row['prod_data'];
		  $return  .= "\n";
 	      }
	      return $return;
	      //return $query;


      }




      public function getTotalProductTotal($from,$to,$activity_type)
      {
	
	$return = '';
	$query  = "select product.\"name\"::character varying, sum(amount)::bigint as total
	from report.la_install_uninstall 
		join product on (report.la_install_uninstall.product_id=product.id)
	where (validity > '$from' and validity <= '$to') and dim = '1 day'";
	if($activity_type>0) 	$query .= " and action_id =$activity_type ";
	$query .="      group by product.\"name\"
	union
	select 'Total',sum(a.cnt)::bigint as total from(select product.\"name\", sum(amount) as cnt
	from report.la_install_uninstall 
	      join product on (report.la_install_uninstall.product_id=product.id)
	      where (validity > '$from' and validity <= '$to') and dim = '1 day'";
	if($activity_type>0) 	$query .= " and action_id =$activity_type ";
	$query .="      group by product.\"name\") as a
	order by total";


	$res = pg_query($this->db_conn,$query);
	      while($row=pg_fetch_assoc($res)){
		  $return  .= $row['name'];
		  $return  .= "|";
		  $return  .= $row['total'];
		  $return  .= "\n";
 	      }
	      return $return;
	      //return $query;


      }
      public function getAppOsLangStat($from,$to,$src,$product,$activity_type){
	$report = "";
	$query = "select lang.\"name\"::character varying,";
	if($src === 'os') $query .= "sum(os_lang_amount) as lang_amount ";
	else $query .= "sum(app_lang_amount) as lang_amount ";
	$query .="from report.la_lang_distribution join lang on(lang.id=lang_id) join product on (product.id=product_id)
		where (validity > '$from' and validity <= '$to') and dim = '1 day'";
	if($src === 'os') $query .=" and os_lang_amount > 0 ";  
	else $query .=" and app_lang_amount > 0 ";  
	if($product != 'All') $query .= " and product.\"name\" = '".preg_replace("/defice/","–",$product)."' ";
	if($activity_type>0) 	$query .= " and action_id =$activity_type ";
	$query .= "group by lang.\"name\" order by lang.\"name\"";
	$res = pg_query($this->db_conn,$query);
	while($row=pg_fetch_assoc($res)){
		  $return  .= $row['name'];
		  $return  .= "|";
		  $return  .= $row['lang_amount'];
		  $return  .= "\n";
 	      }
	      return $return;


      }

      public function getAffiliateDist($from,$to,$product,$activity_type){
	$report ="";
	$query = "select product.\"name\",array_accum(affiliate||':'||amount) as n_affiliate from report.la_affiliate_distribution 
		    join product on (product.id=product_id)
		    where (validity > '$from' and validity<='$to') and dim = '1 day'";
	if($product != 'All') $query .= " and product.\"name\" = '".preg_replace("/defice/","–",$product)."' ";
	if($activity_type>0) 	$query .= " and action_id =$activity_type ";
	$query .= "group by product.\"name\"";
	$res = pg_query($this->db_conn,$query);
	while($row=pg_fetch_assoc($res)){
		  $return  .= $row['name'];
		  $return  .= "|";
		  $return  .= preg_replace("/}/","",preg_replace("/{/","",$row['n_affiliate']));
		  $return  .= "\n";
 	      }
	      return $return;

      }

	function __destruct() 
	{ 
		pg_close($this->db_conn);
	}


}

?>