<?php 
	//------------------------------------------------------//
	//                      FUNCTIONS                       //
	//------------------------------------------------------//
	
	//------------------------------------------------------//
	function get_app_data($val)
	//------------------------------------------------------//
	{
		global $mysqli;
		$q = 'SELECT '.$val.' FROM apps WHERE id = "'.get_app_info('app').'" AND userID = '.get_app_info('main_userID');
		$r = mysqli_query($mysqli, $q);
		if ($r && mysqli_num_rows($r) > 0)
		{
		    while($row = mysqli_fetch_array($r))
		    {
				return $row[$val];
		    }  
		}
	}
	
	//------------------------------------------------------//
	function get_lists_data($val, $lid)
	//------------------------------------------------------//
	{
		global $mysqli;
		$q = 'SELECT '.$val.' FROM lists WHERE app = "'.get_app_info('app').'" AND id = '.$lid.' AND userID = '.get_app_info('main_userID');
		$r = mysqli_query($mysqli, $q);
		if ($r && mysqli_num_rows($r) > 0)
		{
		    while($row = mysqli_fetch_array($r))
		    {
				return $row[$val]=='' ? '' : stripslashes($row[$val]);
		    }  
		}
	}
	
	//------------------------------------------------------//
	function get_lists($except_list_id)
	//------------------------------------------------------//
	{
		global $mysqli;
		
		//Get list sorting preference
		$q = 'SELECT templates_lists_sorting FROM apps WHERE id = '.get_app_info('app');
		$r = mysqli_query($mysqli, $q);
		if ($r) while($row = mysqli_fetch_array($r)) $templates_lists_sorting = $row['templates_lists_sorting'];
		$sortby = $templates_lists_sorting=='date' ? 'id DESC' : 'name ASC';
		
		//Get lists from the brand
		$q = 'SELECT id, name FROM lists WHERE app = '.get_app_info('app').' AND id NOT IN '.$except_list_id.' AND userID = '.get_app_info('main_userID').' ORDER BY '.$sortby;
		$r = mysqli_query($mysqli, $q);
		if ($r)
		{
			$options = '';
			
		    while($row = mysqli_fetch_array($r))
		    {
				$id = $row['id'];
				$name = $row['name'];
				
				$options .= '<option value="'.$id.'">'.$name.'</option>';
		    }  
		    
		    return $options;
		}
	}
	
	//------------------------------------------------------//
	function get_subscribers_count($lid)
	//------------------------------------------------------//
	{
		global $mysqli;
		
		$pending_import_count = get_pending_import_subscribers_count($lid);
		if($pending_import_count!==false) return $pending_import_count;

		//if not, just return the subscriber count
		$q = 'SELECT COUNT(list) FROM subscribers WHERE list = '.$lid.' AND unsubscribed = 0 AND bounced = 0 AND complaint = 0 AND confirmed = 1';
		$r = mysqli_query($mysqli, $q);
		if ($r)
		{
			while($row = mysqli_fetch_array($r))
		    {
				return number_format($row['COUNT(list)']);
		    }
		}
	}

	//------------------------------------------------------//
	function get_pending_import_subscribers_count($lid)
	//------------------------------------------------------//
	{
		//Check if the list has a pending CSV for importing via cron
		$server_path_array = explode('list.php', $_SERVER['SCRIPT_FILENAME']);
		$server_path = $server_path_array[0];
		
		$csvs_path = sendy_existing_uploads_path(get_app_info('app'), 'csvs', $server_path.'uploads');
		if (file_exists($csvs_path) && $handle = opendir($csvs_path)) 
		{
		    while (false !== ($file = readdir($handle))) 
		    {
				if($file=='.' || $file=='..' || $file=='.DS_Store' || $file=='.svn') continue;

				$file_array = explode('-', $file);
				if(count($file_array) < 2 || str_replace('.csv', '', $file_array[1])!=$lid) continue;

				closedir($handle);
				return _('Checking..').'
					<script type="text/javascript">
						$(document).ready(function() {

							list_interval = setInterval(function(){get_list_count('.$lid.')}, 2000);

							function get_list_count(lid)
							{
								clearInterval(list_interval);
								$.post("includes/list/progress.php", { list_id: lid, user_id: '.get_app_info('main_userID').' },
								  function(data) {
									  if(data)
									  {
									  if(data.indexOf("%)") != -1)
									  list_interval = setInterval(function(){get_list_count('.$lid.')}, 2000);

									  $("#progress'.$lid.'").html(data);
									  }
									  else
									  {
									  $("#progress'.$lid.'").html("'._('Error retrieving count').'");
									  }
								  }
								);
							}

						});
					</script>';
		    }
		    closedir($handle);
		}
		
		return false;
	}

	//------------------------------------------------------//
	function get_list_subscriber_counts($list_ids)
	//------------------------------------------------------//
	{
		global $mysqli;

		$ids = array();
		foreach($list_ids as $list_id)
		{
			$list_id = (int)$list_id;
			if($list_id > 0) $ids[$list_id] = $list_id;
		}
		if(count($ids)==0) return array();

		$counts = array();
		foreach($ids as $list_id)
		{
			$counts[$list_id] = array(
				'active' => '0',
				'unsubscribed' => '0',
				'bounced' => '0',
				'gdpr' => '0'
			);
		}

		$q = 'SELECT list,
				SUM(CASE WHEN unsubscribed = 0 AND bounced = 0 AND complaint = 0 AND confirmed = 1 THEN 1 ELSE 0 END) AS active_count,
				SUM(CASE WHEN unsubscribed = 1 AND bounced = 0 THEN 1 ELSE 0 END) AS unsubscribed_count,
				SUM(CASE WHEN bounced = 1 THEN 1 ELSE 0 END) AS bounced_count,
				SUM(CASE WHEN unsubscribed = 0 AND bounced = 0 AND complaint = 0 AND confirmed = 1 AND gdpr = 1 THEN 1 ELSE 0 END) AS gdpr_count
			  FROM subscribers
			  WHERE list IN ('.implode(',', $ids).')
			  GROUP BY list';
		$r = mysqli_query($mysqli, $q);
		if ($r)
		{
			while($row = mysqli_fetch_array($r))
		    {
				$list_id = (int)$row['list'];
				$counts[$list_id] = array(
					'active' => number_format((int)$row['active_count']),
					'unsubscribed' => number_format((int)$row['unsubscribed_count']),
					'bounced' => number_format((int)$row['bounced_count']),
					'gdpr' => number_format((int)$row['gdpr_count'])
				);
		    } 
		}

		foreach($ids as $list_id)
		{
			$pending_import_count = get_pending_import_subscribers_count($list_id);
			if($pending_import_count!==false) $counts[$list_id]['active'] = $pending_import_count;
		}
	
		return $counts;
	}

	//------------------------------------------------------//
	function get_unsubscribers_count($lid)
	//------------------------------------------------------//
	{
		global $mysqli;
		$q = 'SELECT COUNT(list) FROM subscribers WHERE list = '.$lid.' AND unsubscribed = 1 AND bounced = 0';
		$r = mysqli_query($mysqli, $q);
		if ($r) while($row = mysqli_fetch_array($r)) return number_format($row['COUNT(list)']);
	}
	
	//------------------------------------------------------//
	function get_unsubscribers_percentage($subscribers, $unsubscribers)
	//------------------------------------------------------//
	{
		$subscribers = str_replace(',', '', $subscribers);
		$subscribers = !is_numeric($subscribers) ? 0 : $subscribers;
		$unsubscribers = str_replace(',', '', $unsubscribers);
		$unsubscribers = !is_numeric($unsubscribers) ? 0 : $unsubscribers;
		$sub_unsub_total = $subscribers+$unsubscribers;
		$unsub_percentage = $sub_unsub_total==0 ? round($unsubscribers * 100, 2) : round($unsubscribers / ($sub_unsub_total) * 100, 2);
		return $unsub_percentage;
	}
	
	//------------------------------------------------------//
	function get_bounced_count($lid)
	//------------------------------------------------------//
	{
		global $mysqli;
		$q = 'SELECT COUNT(list) FROM subscribers WHERE list = '.$lid.' AND bounced = 1';
		$r = mysqli_query($mysqli, $q);
		if ($r) while($row = mysqli_fetch_array($r)) return number_format($row['COUNT(list)']);
	}
	
	//------------------------------------------------------//
	function get_bounced_percentage($bouncers, $subscribers)
	//------------------------------------------------------//
	{
		$subscribers = str_replace(',', '', $subscribers);
		$subscribers = !is_numeric($subscribers) ? 0 : $subscribers;
		$bouncers = str_replace(',', '', $bouncers);
		$bouncers = !is_numeric($bouncers) ? 0 : $bouncers;
		$bounce_subs_total = $subscribers+$bouncers;
		$bounce_percentage = $bounce_subs_total==0 ? round($bouncers * 100, 2) : round($bouncers / ($bounce_subs_total) * 100, 2);
		return $bounce_percentage;
	}
	
	//------------------------------------------------------//
	function get_segment_count($lid)
	//------------------------------------------------------//
	{
		global $mysqli;
		$q = 'SELECT COUNT(id) FROM seg WHERE list = '.$lid;
		$r = mysqli_query($mysqli, $q);
		if ($r) while($row = mysqli_fetch_array($r)) return $row['COUNT(id)'];
	}
	
	//------------------------------------------------------//
	function get_ar_count($lid)
	//------------------------------------------------------//
	{
		global $mysqli;
		$q = 'SELECT COUNT(id) FROM ares WHERE list = '.$lid;
		$r = mysqli_query($mysqli, $q);
		if ($r) while($row = mysqli_fetch_array($r)) return $row['COUNT(id)'];
	}
	
	//------------------------------------------------------//
	function get_gdpr_count($lid)
	//------------------------------------------------------//
	{
		global $mysqli;
		$q = 'SELECT COUNT(id) FROM subscribers WHERE unsubscribed = 0 AND bounced = 0 AND complaint = 0 AND confirmed = 1 AND gdpr = 1 AND list = '.$lid;
		$r = mysqli_query($mysqli, $q);
		if ($r) while($row = mysqli_fetch_array($r)) return number_format($row['COUNT(id)']);
	}
	
	//------------------------------------------------------//
	function get_gdpr_percentage($lid, $gdpr_subs)
	//------------------------------------------------------//
	{
		global $mysqli;
		
		//Get subscriber count
		$q = "SELECT COUNT(*) FROM subscribers WHERE list = '$lid' AND unsubscribed = 0 AND bounced = 0 AND complaint = 0 AND confirmed = 1";
		$r = mysqli_query($mysqli, $q);
		if ($r) while($row = mysqli_fetch_array($r)) $subscribers = $row['COUNT(*)'];
		
		$subscribers = str_replace(',', '', $subscribers);
		$gdpr_subs = str_replace(',', '', $gdpr_subs);
		$gdpr_percentage = $gdpr_subs==0 ? 0 : round($gdpr_subs / ($subscribers) * 100, 2);
		return $gdpr_percentage;
	}
	
	//------------------------------------------------------//
	function has_gdpr_subscribers()
	//------------------------------------------------------//
	{
		global $mysqli;
		$q = 'SELECT COUNT(subscribers.gdpr) as gdpr_subs_no FROM subscribers, lists, apps WHERE subscribers.list = lists.id AND lists.app = apps.id AND subscribers.gdpr = 1 AND apps.id = '.get_app_info('app');
		$r = mysqli_query($mysqli, $q);
		if ($r) while($row = mysqli_fetch_array($r)) $gdpr_subs_no = $row['gdpr_subs_no'];
		if($gdpr_subs_no > 0) return true;
		else return false;
	}
	
	//------------------------------------------------------//
	function totals($app)
	//------------------------------------------------------//
	{
		global $mysqli;
			
		$q = 'SELECT hide_lists FROM apps WHERE id = '.$app;
		$r = mysqli_query($mysqli, $q);
		if ($r)
		{
			while($row = mysqli_fetch_array($r))
			{
				$hide_lists = $row['hide_lists'];
			}  
		}
		
		if($hide_lists)
			$q = 'SELECT id FROM lists WHERE app = '.$app.' AND userID = '.get_app_info('main_userID').' AND hide = 0';
		else
			$q = 'SELECT id FROM lists WHERE app = '.$app.' AND userID = '.get_app_info('main_userID');
		$r = mysqli_query($mysqli, $q);
		if ($r) return mysqli_num_rows($r);
	}
	
	//------------------------------------------------------//
	function pagination($limit)
	//------------------------------------------------------//
	{		
		global $p;
		
		$total_lists = totals(get_app_info('app'));
		$base_url = get_app_info('path').'/list?i='.get_app_info('app');
		
		sendy_render_pagination($total_lists, $limit, $base_url, $p);
	}
?>
