<?php
//print "paar.class.php<br>";
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 getDBConn()
	{
		return $this->db_conn;
	}
	
	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 getInstUninstPerPeriodAPI($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 getTotalProductTotalAPI($from,$to,$prod_id,$activity_type,$steps='1 day')
      {

        $return = '';
	$query  = "select a.name,a.total,array_accum(a.validity ||':'||a.total) as prod_data from (
        select validity::date, 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 = '$steps'";
	if($activity_type>0) 	$query .= " and action_id = $activity_type ";
        if($prod_id != "All") $query .= " and product_id = $prod_id";

        $query .="
            group by validity, product.\"name\"
            order by validity, total )a
            group by a.name,a.total
            order by a.total";


	$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 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);
        $return = '';
	while($row=pg_fetch_assoc($res)){
		  $return  .= $row['name'];
		  $return  .= "|";
		  $return  .= $row['lang_amount'];
		  $return  .= "\n";
 	      }
	      return $return;


      }

      public function getAppOsLangStatAPI($from,$to,$src,$product,$activity_type,$steps='1 day'){
	$report = "";
	$query = "select lang_name as name, array_accum(validity::date||':'||lang_amount) as prod_data from (select lang.\"name\"::character varying as lang_name, validity, ";
	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)
		where (validity > '$from' and validity <= '$to') and dim = '$steps'";
	if($src === 'os') $query .=" and os_lang_amount > 0 ";
	else $query .=" and app_lang_amount > 0 ";
	if($product != 'All') $query .= " and product_id = $product";
	if($activity_type>0) 	$query .= " and action_id =$activity_type ";
	$query .= " group by lang.\"name\", validity order by lang.\"name\", validity) a group by lang_name order by lang_name";
	$res = pg_query($this->db_conn,$query);
        $return = '';
	while($row=pg_fetch_assoc($res)){
		  $return  .= $row['name'];
		  $return  .= "|";
		  $return  .= $row['prod_data'];
		  $return  .= "\n";
 	      }
              //print $query;
	      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;

      }
	  
	  
	 private function getDateRange($duration, $date='', $dateCustom='')
	 {//fileLog::log(__FUNCTION__ .":" . $duration . ','. $date . ','. $dateCustom);
		 $dateRange;
		 
		switch($duration) 
		{
		case "Yesterday":
			$dateRange['validity'] = date("Y-m-d", strtotime("now"))." 00:00:00";
			$dateRange['dim'] = '1 day';
		break;
		case "Last Week":
			$dateRange['validity'] = date("Y-m-d", strtotime("last Monday"))." 00:00:00";
			$dateRange['dim'] = '7 days';
		break;
		case "Last Month":
			$dateRange['validity'] = date("Y-m", strtotime("now"))."-01 00:00:00";
			$dateRange['dim'] = '1 mon';
		break;
		case "Last Year":
			$dateRange['validity'] = date("Y", strtotime("now"))."-01-01 00:00:00";
			$dateRange['dim'] = '1 year';
		break;
		case "Custom":
			switch($dateCustom) {
				case "day":
					$dateRange['validity'] = date("Y-m-d", strtotime("+1 day", $date))." 00:00:00";
					$dateRange['dim'] = '1 day';
				break;
				case "week":
					$dateRange['validity'] = date("Y-m-d", strtotime("next Monday", $date))." 00:00:00";
					$dateRange['dim'] = '7 days';
				break;
				case "month":
					$dateRange['validity'] = date("Y-m", strtotime("next month", $date))."-01 00:00:00";
					$dateRange['dim'] = '1 mon';
				break;
				case "year":
					$dateRange['validity'] = date("Y", strtotime("next year", $date))."-01-01 00:00:00";
					$dateRange['dim'] = '1 year';
				break;
			}
		break;
	    } 

		return $dateRange;
	 }
	  
	 public function getTopXNetworks($prod, $validity, $date, $dim,  $ntop)
	 {
		$dateRange = $this->getDateRange($validity, $date, $dim);
		$prod = str_replace('defice', '–', $prod);
		$query = "SELECT * FROM report.view_ui_top_networks WHERE validity = '".$dateRange['validity']."' AND dim='".$dateRange['dim']."'";
		if($prod!="All")
		{
			$result = pg_query($this->db_conn, 'SELECT id FROM product WHERE "name" = \''. $prod . '\' GROUP BY id;');
			$prod = pg_fetch_result($result, 0);
			$query .= " AND product_id = '".$prod."'";
		}

		$query .= " ORDER BY amount DESC";
		if(strlen($ntop)) $query .= " LIMIT ".$ntop;
		$result = pg_query($this->db_conn,$query);
		while ($report = pg_fetch_array($result, NULL, PGSQL_ASSOC))
		{
			 $ret .= $report['product_name'];
			 $ret .= "|";
			 $ret .= $report['ip'];
			 $ret .= "|";
			 $ret .= $report['amount'];
			 $ret .= "\n";
		}
		
		return $ret;
	}
	
	public function getProductStickiness($prod, $from, $ito, $uninstAfter)
	{
		$minDate = strtotime("-30 days");
		
		$prod = str_replace('defice', '–', $prod);
		if($prod!="All")
		{
			$result = pg_query($this->db_conn, 'SELECT id FROM product WHERE "name" = \''. $prod . '\' GROUP BY id;');
			$prod = pg_fetch_result($result, 0);
			$query .= " AND product_id = '".$prod."'";
		}
		fileLog::log($minDate);
		//validate the dates
		if($ito > $minDate)
			return "Please check that the 'End date' is at least 30 days back.\n";
		$from = date("Y-m-d", mktime(0, 0, 0, date("m", $from), date("d", $from), date("Y", $from))); 
		$ito  = date("Y-m-d", mktime(0, 0, 0, date("m", $ito), date("d", $ito), date("Y", $ito)));
		
		$query = "SELECT * FROM report.get_ui_product_stickiness('".$from."', '".$ito."', '".$uninstAfter."', '".$prod."') AS countstick";				
		fileLog::log($query);
		$result = pg_query($this->db_conn,$query);
		$report = pg_fetch_result($result,0);

		 $ret .= $report;
		 $ret .= "\n";
	
	 	return $ret;	
	}
	
	public function getUninstallFeedback($prod, $duration, $dateCustom, $step)
	{
		$validity; $dim;
		
		$prod = str_replace('defice', '–', $prod);
		switch($duration) {
			case "Yesterday":
				$validity = date("Y-m-d", strtotime("-1 day"))." 00:00:00";
				$dim = date("Y-m-d", strtotime("now"))." 00:00:00";
			break;
			case "Last Week":
				$validity = date("Y-m-d", strtotime("last Monday -7 days"))." 00:00:00";
				$dim = date("Y-m-d", strtotime("+7 days",strtotime($validity)))." 00:00:00";
			break;
			case "Last Month":
				$validity = date("Y-m", strtotime("-1 month"))."-01 00:00:00";
				$dim = date("Y-m", strtotime("now"))."-01 00:00:00";
			break;
			case "Last Year":
				$validity = date("Y", strtotime("-1 year"))."-01-01 00:00:00";
				$dim = date("Y", strtotime("now"))."-01-01 00:00:00";
			break;
			case "Custom":
				switch($step) {
					case "day":
						$validity = date("Y-m-d", strtotime($dateCustom))." 00:00:00";
						$dim      = date("Y-m-d", strtotime("+1 day", strtotime($dateCustom)))." 00:00:00";
					break;
					case "week":
						$day = date("D",strtotime($dateCustom));
						if($day=="Mon")
						{
						$validity = date("Y-m-d", strtotime($dateCustom))." 00:00:00";
						}
						else
						{
						$validity = date("Y-m-d", strtotime("last Monday",strtotime($dateCustom)))." 00:00:00";
						}
						$dim = date("Y-m-d", strtotime("+7 days",strtotime($validity)))." 00:00:00";
					break;
					case "month":
						$validity = date("Y-m", strtotime($dateCustom))."-01 00:00:00";
						$dim      = date("Y-m", strtotime("+1 month", strtotime($validity)))."-01 00:00:00";
					break;
					case "year":
						$validity = date("Y", strtotime($dateCustom))."-01-01 00:00:00";
						$dim      = date("Y", strtotime("+1 year", strtotime($validity)))."-01-01 00:00:00";
					break;
	
				}
			break;
		}
		
		if($prod == "All")
		{
			//retrieve products
			$res = pg_query($this->db_conn,"SELECT * FROM product;");
			while($row=pg_fetch_assoc($res))
			{
				$products[] = array('id'=>$row['id'], 'name'=>$row['name']);
			}

			//for each product retrieve the number of feedbacks during period
			for($i=0; $i < count($products); $i++)
			{
				$ret .= $products[$i]['name'];
				$ret .= "|";
				$ret .= date("d-m-Y", strtotime($validity) );
				$ret .= "|";
				$ret .= date("d-m-Y", strtotime($dim) );
				$ret .= "|";
				
				$query = "SELECT * FROM report.view_la_uninstall_feedback WHERE req_received_ts BETWEEN '". $validity ."' AND '". $dim ."' AND product_id = '".$products[$i]['id']."'";

				$result = pg_num_rows(pg_query($this->db_conn, $query) );
				
				if($result > 0)
				{
					$link = "uninstall-feedback-report.php?p=". $products[$i]['id'] ."-". $duration ."-". $dateCustom ."-". $step ."-". str_replace(" ","_", $products[$i]['name']);
					$ret .= $result;
					$ret .= "|";
					$ret .= "<a href='$link'>Generate Report</a>";
					$ret .= "\n";
				}else{
					$ret .= "No feedback";
					$ret .= "|";
					$ret .= "No feedback";
					$ret .= "\n";
				}
			}

			return $ret;
		}else{
			
			$prod = str_replace('defice', '–', $prod);
			$query = "SELECT * FROM report.view_la_uninstall_feedback WHERE req_received_ts BETWEEN '". $validity ."' AND '". $dim ."'";
			
			$ret .= $prod;
			$ret .= "|";
			$ret .= date("d-m-Y", strtotime($validity) );
			$ret .= "|";
			$ret .= date("d-m-Y", strtotime($dim) );
			$ret .= "|";
			
			if($prod!="All")
			{
				$result = pg_query($this->db_conn, 'SELECT * FROM product WHERE "name" = \''. $prod . '\' GROUP BY id, name;');
				$prod = pg_fetch_assoc($result);
				$query .= " AND product_id = '".$prod['id']."'";
			}
		
			$result = pg_num_rows(pg_query($this->db_conn, $query) );

			if($result > 0)
			{
				$link = "uninstall-feedback-report.php?p=". $prod['id'] ."-". $duration ."-". $dateCustom ."-". $step ."-". str_replace(" ","_", $prod['name']);
				$ret .= $result;
				$ret .= "|";
				$ret .= "<a href='$link'>Generate Report</a>";
				$ret .= "\n";
			}else{
				$ret .= "No feedback";
				$ret .= "|";
				$ret .= "No feedback";
				$ret .= "\n";
			}

			return $ret;
		}
				
	}

	public function getNetworkUsersDistributions($prod, $duration, $date_custom, $step)
	{
		//fileLog::dump(array($prod, $duration, $date_custom, $step));
		$dates = $this->getDateRange($duration, $date_custom, $step);
		
		$query = "SELECT * FROM report.view_ui_network_users_distribution WHERE validity = '".$dates['validity']."' AND dim='".$dates['dim']."'";
		if($prod!="All")
		{
			$prod = str_replace('defice', '–', $prod);
			$result = pg_query($this->db_conn, 'SELECT id FROM product WHERE "name" = \''. $prod . '\' GROUP BY id;');
			$prod = pg_fetch_result($result, 0);
			$query .= " AND product_id = '".$prod."'";
		}

		$result = pg_query($this->db_conn,$query);
		$suma1 = $suma2 = $suma3 = $suma4 = $suma5 = $suma6 = $suma7 = $suma8 = 0;
		
		while($row = pg_fetch_array($result, NULL, PGSQL_ASSOC))
		{
			$suma1 += $row['1'];
			$suma2 += $row['2_3'];
			$suma3 += $row['4_10'];
			$suma4 += $row['11_25'];
			$suma5 += $row['26-100'];
			$suma6 += $row['101_250'];
			$suma7 += $row['251_500'];
			$suma8 += $row['501_'];	
		}
		
		$ret .= "1";
		$ret .= "|";
		$ret .= $suma1;
		$ret .= "\n";
		$ret .= "2-3";
		$ret .= "|";
		$ret .= $suma2;
		$ret .= "\n";
		$ret .= "4-10";
		$ret .= "|";
		$ret .= $suma3;
		$ret .= "\n";	
		$ret .= "11-25";
		$ret .= "|";
		$ret .= $suma4;
		$ret .= "\n";	
		$ret .= "26-100";
		$ret .= "|";
		$ret .= $suma5;
		$ret .= "\n";	
		$ret .= "101-250";
		$ret .= "|";
		$ret .= $suma6;
		$ret .= "\n";	
		$ret .= "251-500";
		$ret .= "|";
		$ret .= $suma7;
		$ret .= "\n";	
		$ret .= "500-above";
		$ret .= "|";
		$ret .= $suma8;
		$ret .= "\n";

		return $ret;
	}
	
	public function getReportAffiliateDistIU($prod, $duration, $date_custom, $step, $activ)
	{
		$fileName = 'tmp/RepAfDisIU_'. rand() .'.csv'; //temporary filenanme to be used to write the report to
		$fh	= '';  //file handle
		$affiliates = $products = $sum = $total = $report = array();
		
		//fileLog::dump(array($prod, $duration, $date_custom, $step, $activ));
		$dates = $this->getDateRange($duration, $date_custom, $step);
		
		$query = "SELECT affiliate, product_name, SUM(amount) FROM report.view_la_affiliate_distribution WHERE validity = '".$dates['validity']."' AND dim='".$dates['dim']."'";
		$prod = str_replace('defice', '–', $prod);
		if($prod!="All")
		{
			$result = pg_query($this->db_conn, 'SELECT id FROM product WHERE "name" = \''. $prod . '\' GROUP BY id;');
			$prod = pg_fetch_result($result, 0);
			$query .= " AND product_id = '".$prod."'";
		}
		if($activ!=0) $query .= " AND action_id = '".$activ."'";
		$query .= " GROUP BY product_name, affiliate";
		
		$res = pg_query($this->db_conn, $query);
		//retrieve results
		while($row = pg_fetch_array($res, NULL, PGSQL_ASSOC) )
			array_push($report, $row);
	
		
		for($i=0;$i<count($report);$i++) {
			$affiliates[] = $report[$i]['affiliate'];
			if(!array_key_exists($report[$i]['product_name'], $products))
				$products[$report[$i]['product_name']] = array();
			if(!array_key_exists($report[$i]['affiliate'], $products[$report[$i]['product_name']]))
				$products[$report[$i]['product_name']][$report[$i]['affiliate']] = $report[$i]['sum'];
		}
		
		$affiliates_uniques = array_values(array_unique($affiliates));
	
		$fh = fopen($fileName, 'w');
		fwrite($fh, "Product\Affiliate;");
		for($i=0;$i<count($affiliates_uniques);$i++) {
			$total[$affiliates_uniques[$i]] = 0;
			fwrite($fh, $affiliates_uniques[$i] .";");
		}
		fwrite($fh, "Total\r\n");
			
		$totaltot = 0;
			
		foreach($products as $k => $v) {
			fwrite($fh, $k.';');
			$totalr = 0;
			for($i=0;$i<count($affiliates_uniques);$i++) {
				if(isset($v[$affiliates_uniques[$i]])) {
					fwrite($fh, $v[$affiliates_uniques[$i]].';');
					$total[$affiliates_uniques[$i]] += $v[$affiliates_uniques[$i]];
					$totalr += $v[$affiliates_uniques[$i]];
				}
				else fwrite($fh,"--;");
			}
			fwrite($fh, $totalr."\r\n");
			$totaltot += $totalr;
		}
		fwrite($fh, "Total;");
		for($i=0;$i<count($affiliates_uniques);$i++)
			fwrite($fh, $total[$affiliates_uniques[$i]].";"); 
		fwrite($fh, $totaltot."\r\n");
		
		fclose($fh);
		return $fileName;
	}
	
	public function getReportAffiliateDistBDA($prod, $duration, $date_custom, $step)
	{
		$fileName = 'tmp/RepAfDisBDA_'. rand() .'.csv'; //temporary filenanme to be used to write the report to
		$fh	= '';  //file handle
		$affiliates = $products = $sum = $total = $report = array();
		
		//fileLog::dump(array($prod, $duration, $date_custom, $step, $activ));
		$dates = $this->getDateRange($duration, $date_custom, $step);
		
		$query = "SELECT affiliate, product_name, SUM(amount) FROM report.view_laui_affiliate_distribution WHERE validity = '".$dates['validity']."' AND dim='".$dates['dim']."'";
		$prod = str_replace('defice', '–', $prod);
		if($prod!="All")
		{
			$result = pg_query($this->db_conn, 'SELECT id FROM product WHERE "name" = \''. $prod . '\' GROUP BY id;');
			$prod = pg_fetch_result($result, 0);
			$query .= " AND product_id = '".$prod."'";
		}
		$query .= " GROUP BY product_name, affiliate";
		
		$res = pg_query($this->db_conn, $query);
		//retrieve results and insert in array
		while($row = pg_fetch_array($res, NULL, PGSQL_ASSOC) )
			array_push($report, $row);
	
		for($i=0;$i<count($report);$i++) {
			$affiliates[] = $report[$i]['affiliate'];
			if(!array_key_exists($report[$i]['product_name'], $products))
				$products[$report[$i]['product_name']] = array();
			if(!array_key_exists($report[$i]['affiliate'], $products[$report[$i]['product_name']]))
				$products[$report[$i]['product_name']][$report[$i]['affiliate']] = $report[$i]['sum'];
		}
		
		$affiliates_uniques = array_values(array_unique($affiliates));
		
		$fh = fopen($fileName, 'w');
		
		fwrite($fh, "Product\Affiliate;");
			for($i=0;$i<count($affiliates_uniques);$i++) {
				$total[$affiliates_uniques[$i]] = 0;
				fwrite($fh, $affiliates_uniques[$i].";");
			}
			fwrite($fh, "Total\r\n");
			
			$totaltot = 0;
			
			foreach($products as $k => $v) {
				fwrite($fh, $k.";");
				$totalr = 0;
				for($i=0;$i<count($affiliates_uniques);$i++) {
					if(isset($v[$affiliates_uniques[$i]])) {
						fwrite($fh, $v[$affiliates_uniques[$i]].";");
						$total[$affiliates_uniques[$i]] += $v[$affiliates_uniques[$i]];
						$totalr += $v[$affiliates_uniques[$i]];
					}
					else fwrite($fh, "--;");
				}
				fwrite($fh, $totalr."\r\n");
				$totaltot += $totalr;
			}
			fwrite($fh, "Total;");
			for($i=0;$i<count($affiliates_uniques);$i++)
				fwrite($fh, $total[$affiliates_uniques[$i]].";"); 
			fwrite($fh, $totaltot."\r\n");

		fclose($fh);
		
		return $fileName;	
	}
	
	
	public function getInteractions($prod, $duration, $dateCustom, $step)
	{
		$val = "";
		
		$dates = $this->getDateRange($duration, $dateCustom, $step);
		$query = "select distinct(interaction_name), interaction_id from report.view_ui_active_users where validity = '". $dates['validity'] ."' and dim = '1 day'";
		if($prod!="All")
		{
			$prod = str_replace('defice', '–', $prod);
			$result = pg_query($this->db_conn, 'SELECT id FROM product WHERE "name" = \''. $prod . '\' GROUP BY id;');
			$prod = pg_fetch_result($result, 0);
			$query .= " AND product_id = '".$prod."'";
		}
		$res = pg_query($this->db_conn,$query);
		
		while($row=pg_fetch_assoc($res)){
			$val  .= $row['interaction_name'].";";
			$val  .= $row['interaction_id']."|";			
		}

		return $val;
	}
	
	public function getReportVersionBasedDistr($prod, $duration, $dateCustom, $step, $interAct)
	{//fileLog::dump(array($prod, $duration, $dateCustom, $step, $interAct));
		$report = array();

		$dates = $this->getDateRange($duration, $dateCustom, $step);
		
		$query = "SELECT * FROM report.view_ui_active_users WHERE validity = '".$dates['validity']."' AND dim = '".$dates['dim']."'";
		if($prod!="All")
		{
			$prod = str_replace('defice', '–', $prod);
			$result = pg_query($this->db_conn, 'SELECT id FROM product WHERE "name" = \''. $prod . '\' GROUP BY id;');
			$prod = pg_fetch_result($result, 0);
			$query .= " AND product_id = '".$prod."'";
		}
		if($interAct!='All') $query .= " AND interaction_id = ".$interAct;
			
		$res = pg_query($this->db_conn, $query);
		//retrieve results and insert in array
		while($row = pg_fetch_array($res, NULL, PGSQL_ASSOC) )
			array_push($report, $row);	

		ob_start();
		echo '<table class="camdpams-grid">';
		echo '<tr class="header"><th>Product name</th><th>Version</th><th>Count</th><th>Count</th></tr>';	

		if(empty($report) ) 
			echo '<tr><td colspan="4" style="text-align: center;">There is no data to display.</td></tr></table>';
		else
		{ 
			foreach($report as $k=>$v)
			{
				$data[$report[$k]["product_name"]][] = $v;
				$totalUsers[$report[$k]["product_name"]] += $v['active_users'];
			}
			$odd = 0;
			foreach($data as $key=>$value)
			{	$odd++;
				$nrRows = count($data[$key])+1;
				$cls = $odd%2 ? 'odd':'even';
				echo '<tr><td class="'. $cls .'" rowspan="'. $nrRows .'">'. $key . '</td>';
				
				foreach($value as $k=>$v)
				{	
					echo '<tr><td style="text-align: center;">'. $data[$key][$k]["product_ver"] .'</td>
						  <td style="text-align: center;">'. $data[$key][$k]["active_users"];
					if($k == 0) {
						echo '<td rowspan="'. $nrRows .'" style="text-align: center;">'. $totalUsers[$key] .'</td></tr>';
					}else{echo '</td></tr>';}
				}
				
			}
			
			echo '</table>';
		}
		$html = ob_get_contents();
		ob_end_clean();
		return $html;
	}
	
	function __destruct() 
	{ 
		pg_close($this->db_conn);
	}


}

?>