<?
ini_set('error_reporting', E_ERROR | E_WARNING);
include_once 'lib/php/include/dbConstants.php';

/**
 * advDetailLog.php
 * 
 * 
 * @author     Kushal Khatri
 */
 
class advDetailLog {
	
	/**
     * Static limit of number of rows to be kept in memory. Insert into DB once exceeded
     *
     * @var int
     */
	 const LOG_LIMIT = 2000;
	 
	 /**
     * Database IP to insert the stats
     *
     * @var string
     */
	 private $db_server = "192.168.17.101";
	 
	 /**
     * Database IP to get campaign stats
     *
     * @var string
     */
	 private $admin_db_server = MSACOMMON_DB_HOST_ADMIN;
	 
	 /**
     * MySQL Resource containing main DB identifier
     *
     * @var resource
     */
	 private $db_write;
	 
	 /**
     * MySQL Resource containing Admin DB identifier
     *
     * @var resource
     */
	 private $db_admin;
	 
	 /**
     * Date corresponding to the stats
     *
     * @var string
     */
	 private $date;
	 
	 /**
     * Hour corresponding to the stats
     *
     * @var int
     */
	 private $hour;
	 
	 /**
     * Year month corresponding to the stats
     *
     * @var string
     */
	 private $dateYm;
	 
	 /**
     * Type of data => CPM, CPC, etc
     *
     * @var string
     */
	 private $type;
   
   /**
     * Type of insert => increment or update || increment does on duplicate key update column = column + value, update does update column = value
     *
     * @var string
     */
	 private $insertType;
	 
	 /**
     * System abbr where data is coming from
     *
     * @var string
     */
	 private $system;
	 
	 /**
     * System abbr to be appended to the source to identify it separately from other sources
     *
     * @var string
     */
	 private $source_append;
	 
	 /**
     * String containing errors
     *
     * @var string
     */
	 private $error_str = "";
	 
	 /**
     * Unique Campaigns Array
     *
     * @var array
     */
	 private $campaignsArr = array();
	 
	 /**
     * Unique Affiliate IDs Array
     *
     * @var array
     */
	 private $affiliateIDsArr = array();
	 
	 /**
     * Unique Advertiser IDs Array
     *
     * @var array
     */
	 private $advIdsArr = array();
	 
	 /**
     * Array containing detail data
     *
     * @var array
     */
	 private $detailData = array();
	 
	 /**
     * Array containing domains data
     *
     * @var array
     */
	 private $domainsData = array();
	 
	 /**
     * Array containing keywords data
     *
     * @var array
     */
	 private $keywordsData = array();
	 
	 /**
     * Count maintaining inserts
     *
     * @var int
     */
	 private $count = 0;
	 
	 /**
     * Count maintaining total rows inserted
     *
     * @var int
     */
	 private $rows_inserted = 0;
	 
	 /**
     * Count maintaining total entries
     *
     * @var int
     */
	 private $total_entries = 0;
	 
	 /**
     * Total media cost
     *
     * @var int
     */
	 private $total_media_cost = 0;
	 
	 /**
     * Total media cost calculated by flushing domains
     *
     * @var int
     */
	 private $domains_media_cost = 0;
	 
	/*
	 *Constructor initializes the date, hour and system where the stats were generated
	 *
	 *@param string $date Date corresponding to the stats
	 *@param int $hour Hour corresponding to the stats
	 *@param string $system System where the stats were generated
	*/
	private $redis_server = '192.168.17.103'; 
	private $redis_write;
	public function __construct($date = "", $hour = "", $system = "", $type = "", $insertType = "increment", $db_admin = null)
	{
		register_shutdown_function(array(&$this, "shutdown"));
		$this->date = trim($date) != '' ? $date : date("Y-m-d");
		$this->hour = trim($hour) != '' && intval($hour) ? $hour : date("G");
		$this->system = trim($system) != '' ? strtoupper($system) : "";
		$this->source_append = (isset($this->system) && trim($this->system) != '') ? $this->system."_" : "";
		$this->type = $type;
		$this->insertType = $insertType;
		if($this->insertType != "update" && $this->insertType != "increment")
			$this->insertType = "increment";
      
		$this->dateYm = date("Ym", strtotime($this->date));
		$this->db_write = $this->db_connect();
		if($db_admin)
			$this->db_admin = $db_admin;
		else
			$this->db_admin = $this->admin_db_connect();
		
		if(!$this->db_write)
		{
			$this->error_str .= "Failed connecting to ".$this->db_server."\n";
			exit();
		}
		$this->redis_write = new Redis();
		$this->redis_write->pconnect($this->redis_server);
	}
	
	/*
	 *Shutdown function
	*/
	function shutdown()
	{
		$this->flushDataIntoDB();
		
		if($this->error_str != '')
		{
			mail("noc@admedia.com", "Errors on Advertiser Detail Log advDetailLog.php", $this->error_str);
			$this->debug($this->error_str, true);
		}
		/*else{
			mail("kushal.khatri@admedia.com", "Success on Advertiser Detail Log advDetailLog.php", $this->error_str);
		}*/
		
		//$this->debug("Total Entries: ".$this->total_entries);
		//$this->debug("Total Rows Inserted: ".$this->rows_inserted);
		
		$this->debug("Total Media Cost from Entries: ".$this->total_media_cost);
		$this->debug("Total Media Cost from Domains: ".$this->domains_media_cost);
	}
	
	/*
	 *Connect to Reporting DB
	 *
	*/
	private function db_connect()
	{
		$db_conn = null;
		
		try{
			$db_conn = mysqli_connect($this->db_server, "c3slave", "RstG496u*#rky2", 'adcenter');
		}
		catch(Exception $e)
		{
			$this->error_str .= "Failed connecting to ".$this->db_server."! Message: ".$e->getMessage()."\n";
			exit();
		}
		
		return $db_conn;
	}
	
	/*
	 *Connect to Admin DB
	 *
	*/
	private function admin_db_connect()
	{
		$db_conn = null;
		
		try{
			$db_conn = mysqli_connect($this->admin_db_server, MSACOMMON_DB_USER_ADMIN, MSACOMMON_DB_PASS_ADMIN,"Admin");
		}
		catch(Exception $e)
		{
			$this->error_str .= "Failed connecting to ".$this->admin_db_server."! Message: ".$e->getMessage()."\n";
			exit();
		}
		
		return $db_conn;
	}
	
	/*
	 * Insert data into an array and flush if crosses log limit
	 *@param mixed data All data to be inserted
	*/
	public function insert($data, $metric_to_update = "", $insertValue = 1)
	{
		$this->count++;						// Update the count for flushing the data into DB
		$this->total_entries++;
		
		/////////// Data //////////////
		$campaign_id = (isset($data->cid) && trim($data->cid) != '') ? intval($data->cid) : 0;
		$creative_id = (isset($data->crid) && trim($data->crid) != '') ? intval($data->crid): 0;
		$source = $this->source_append.$data->pid;
		$country = (isset($data->country) && trim($data->country) != '') ? strtoupper($data->country): "USA";
		$os = (isset($data->os) && trim($data->os) != '') ? strtolower($data->os): "others";
		$browser = (isset($data->browser) && trim($data->browser) != '') ? strtolower($data->browser): "others";
		
		
		$metric_to_update = trim($metric_to_update) != '' ? $metric_to_update : "impressions";
		
		// Add to unique array of campaigns //
		if(!isset($this->campaignsArr[$campaign_id]))
			$this->campaignsArr[$campaign_id] = array();
		//////////////////////////////////////
		
		/************* Processing Detailed data (For all Types) *********************/
		if(isset($this->detailData[$campaign_id][$creative_id][$source][$country][$os][$browser]))
		{
			$this->detailData[$campaign_id][$creative_id][$source][$country][$os][$browser][$metric_to_update] += $insertValue;
		} else {
			$this->detailData[$campaign_id][$creative_id][$source][$country][$os][$browser][$metric_to_update] = $insertValue;
		}
		
		// Add to media_cost or spend if CPM and impressions or CPC and clicks //
		if(($this->type == "CPM" && $metric_to_update == "impressions") || ($this->type == "CPC" && $metric_to_update == "clicks"))
		{
			if(isset($data->win_price))			// Win_price exists (Media cost exists)
			{
				$price = floatval($data->win_price);
        if($this->type == "CPM" && $insertValue == 1)             // When $insertValue == 1 or nothing is passed, its passed as CPM, otherwise total amount
          $price = floatval($price/1000);
          
				$this->detailData[$campaign_id][$creative_id][$source][$country][$os][$browser]["media_cost"] += $price;
				$this->total_media_cost += ($price/1000);
			}
			else if(isset($data->bid_price))	// bid_price exists (Spent amount exists)
			{
				// Add to unique array of affiliate IDs (Will be used to get rev share) //
				if(!isset($this->affiliateIDsArr[$data->pid]))
					$this->affiliateIDsArr[$data->pid] = array();
				
				$bid_price = floatval($data->bid_price);
        if($this->type == "CPM" && $insertValue == 1)             // When $insertValue == 1 or nothing is passed, its passed as CPM, otherwise total amount
          $bid_price = floatval($bid_price/1000);
          
				$this->detailData[$campaign_id][$creative_id][$source][$country][$os][$browser]["spent"] += $bid_price;
			}
		}
		/*****************************************************************************/
		
		/************ Domains or Keywords Data Processing **************/
		if($this->type == "CPM")
		{
			$this->insertDomainsData($data, $metric_to_update, $insertValue);
		} 
		else if($this->type == "CPC" && $metric_to_update != "impressions")			//Don't Insert keywords data for impressions
		{
			$this->insertKeywordsData($data, $metric_to_update, $insertValue);
		}
		/***************************************************************/
		
		/************ Flush if count crosses LOG_LIMIT (Don't flush for update as it has to be updated, not incremented, when all values are inserted) *****************/
		if($this->count >= self::LOG_LIMIT && $this->insertType != "update")
			$this->flushDataIntoDB();
		/***************************************************************************************************************************************************************/
	}
	
	/*
	 *Insert Domains Data in array
	 *@param mixed data All data to be inserted
	 *
	*/
	private function insertDomainsData($data, $metric_to_update = "", $insertValue = 1)
	{
		$campaign_id = (isset($data->cid) && trim($data->cid) != '') ? intval($data->cid) : 0;
		$creative_id = (isset($data->crid) && trim($data->crid) != '') ? intval($data->crid): 0;
		$source = $this->source_append.$data->pid;
		$domain = isset($data->domain) ? addslashes(trim($data->domain)) : "";
		
		if(isset($this->domainsData[$campaign_id][$creative_id][$source][$domain]))
		{
			$this->domainsData[$campaign_id][$creative_id][$source][$domain][$metric_to_update] += $insertValue;
		} else {
			$this->domainsData[$campaign_id][$creative_id][$source][$domain][$metric_to_update] = $insertValue;
		}
		
		// Add to media_cost if CPM and impressions or CPC and clicks
		if(($this->type == "CPM" && $metric_to_update == "impressions") || ($this->type == "CPC" && $metric_to_update == "clicks"))
		{
			if(isset($data->win_price))			// Win_price exists (Media cost exists)
			{
				$price = floatval($data->win_price);
        if($this->type == "CPM" && $insertValue == 1)         // When $insertValue == 1 or nothing is passed, its passed as CPM, otherwise total amount
          $price = floatval($price/1000);
          
				$this->domainsData[$campaign_id][$creative_id][$source][$domain]["media_cost"] += $price;
			}
			else if(isset($data->bid_price))	// bid_price exists (Spent amount exists)
			{
				$bid_price = floatval($data->bid_price);
        if($this->type == "CPM" && $insertValue == 1)         // When $insertValue == 1 or nothing is passed, its passed as CPM, otherwise total amount
          $bid_price = floatval($bid_price/1000);
          
				$this->domainsData[$campaign_id][$creative_id][$source][$domain]["spent"] += $bid_price;
			}
		}
	}
	
	/*
	 *Insert Keywords Data in array
	 *@param mixed data All data to be inserted
	 *
	*/
	private function insertKeywordsData($data, $metric_to_update = "", $insertValue = 1)
	{
		$campaign_id = (isset($data->cid) && trim($data->cid) != '') ? intval($data->cid) : 0;
		$creative_id = (isset($data->crid) && trim($data->crid) != '') ? intval($data->crid): 0;
		$source = $this->source_append.$data->pid;
		$keyword = isset($data->keyword)? addslashes(trim($data->keyword)) : "";
		if(strlen($keyword) > 200)
			$keyword = substr($keyword, 0, 200);
		
		if(isset($this->keywordsData[$campaign_id][$creative_id][$source][$keyword]))
		{
			$this->keywordsData[$campaign_id][$creative_id][$source][$keyword][$metric_to_update] += $insertValue;
		} else {
			$this->keywordsData[$campaign_id][$creative_id][$source][$keyword][$metric_to_update] = $insertValue;
		}
		
		// Add to media_cost if CPM and impressions or CPC and clicks
		if(($this->type == "CPM" && $metric_to_update == "impressions") || ($this->type == "CPC" && $metric_to_update == "clicks"))
		{
			if(isset($data->win_price))			// Win_price exists (Media cost exists)
			{
				$price = floatval($data->win_price);
        if($this->type == "CPM" && $insertValue == 1)           // When $insertValue == 1 or nothing is passed, its passed as CPM, otherwise total amount
          $price = floatval($price/1000);
          
				$this->keywordsData[$campaign_id][$creative_id][$source][$keyword]["media_cost"] += $price;
			}
			else if(isset($data->bid_price))	// bid_price exists (Spent amount exists)
			{
				$bid_price = floatval($data->bid_price);
        if($this->type == "CPM" && $insertValue == 1)           // When $insertValue == 1 or nothing is passed, its passed as CPM, otherwise total amount
          $bid_price = floatval($bid_price/1000);
          
				$this->keywordsData[$campaign_id][$creative_id][$source][$keyword]["spent"] += $bid_price;
			}
		}
	}
	
	/*
	 *Flush all data into DB
	 *
	*/
	private function flushDataIntoDB()
	{
		$flush_start_time = microtime(true);
		$this->debug("Flushing data now || Total Rows inserted: ".$this->rows_inserted);
		
		// Get Campaign CPM and advertiser ID for all unique campaigns //
		$this->fetchCampaignsInfo();
		
		// Get rev_share for all unique affiliates //
		$this->fetchAffiliateInfo();
		
		// Create tables for advertisers //
		$this->createAdvTables();
		
		// Flush data
		$this->flushDetailDataIntoDB();
		$this->flushDomainsDataIntoDB();
		$this->flushKeywordsDataIntoDB();
		
		// Clean up arrays and variables after inserting data//
		$this->cleanUp();
		
		$flush_end_time = microtime(true);
		$this->debug("Total flush time => ".($flush_end_time - $flush_start_time)." secs\n");
	}
	
	/*
	 *Fetch Campaign Info
	 *
	*/
	private function fetchCampaignsInfo()
	{
		mysqli_ping($this->db_admin);
		if(!$this->db_admin)
			$this->db_admin = $this->admin_db_connect();
		
		foreach($this->campaignsArr as $campaign_id => $infoArr)
		{
			if(!isset($infoArr['advertiser_id']) || !isset($infoArr['bid']))
			{
				$dataFromDB = false;
				
				$cSql = "select campaign_mode, advertiser_id, bid from campaign where id=".$campaign_id." limit 1";
				$cRes = mysqli_query($this->db_admin, $cSql);
				if($cRes)
				{
					$cRow = mysqli_fetch_assoc($cRes);
					if($cRow && isset($cRow['advertiser_id']))
					{
						$infoArr['advertiser_id'] = intval($cRow['advertiser_id']);
						$infoArr['campaign_mode'] = trim($cRow['campaign_mode']);
						$infoArr['bid'] = floatval($cRow['bid']);
						
						// Populate unique advertiser ID array for creating advertiser tables //
						if(!isset($this->advIdsArr[$infoArr['advertiser_id']]))
							$this->advIdsArr[$infoArr['advertiser_id']] = false;		// Set to false as table hasn't been created yet.
						
						// Populate campaign array
						$this->campaignsArr[$campaign_id] = $infoArr;
						
						$dataFromDB = true;
					}
				}
				
				if(!$dataFromDB)
					$this->error_str .= "Failed to get Campaign Data from DB for ID: ".$campaign_id."<br>";
			}
		}
		//mysql_close($admin_db);
	}
	
	/*
	 *Fetch Affiliate Info
	 *
	*/
	private function fetchAffiliateInfo()
	{
		mysqli_ping($this->db_admin);
		if(!$this->db_admin)
			$this->db_admin = $this->admin_db_connect();
		
		foreach($this->affiliateIDsArr as $affiliateID => $infoArr)
		{
			if(!isset($infoArr['rev_share']) && is_numeric($affiliateID))
			{
				$dataFromDB = false;
				
				$cSql = "select ai.rev_share from Affiliate_info ai, AffiliateIDs aid where aid.Affiliate = ai.Affiliate and aid.id=".$affiliateID." limit 1";
				$cRes = mysqli_query($this->db_admin, $cSql);
				if($cRes)
				{
					$cRow = mysqli_fetch_assoc($cRes);
					if($cRow && isset($cRow['rev_share']))
					{
						$infoArr['rev_share'] = floatval($cRow['rev_share']);
						
						// Populate affiliate IDs array
						$this->affiliateIDsArr[$affiliateID] = $infoArr;
						
						$dataFromDB = true;
					}
				}
				
				if(!$dataFromDB)
					$this->error_str .= "Failed to get Affiliate Info from DB for ID: ".$affiliateID."<br>";
			}
		}
	}
	
	/*
	 *Flush detail data into DB
	 *
	*/
	private function flushDetailDataIntoDB()
	{
		$flush_start_time = microtime(true);
		
		$tablePre = "adv_details_";
		mysqli_ping($this->db_write);
		
		if(is_array($this->detailData) && count($this->detailData) > 0)
		{
			foreach($this->detailData as $campaign_id => $creativeArr)
			{
				$advertiser_id = $this->campaignsArr[$campaign_id]['advertiser_id'];
				$campaign_bid = $this->campaignsArr[$campaign_id]['bid'];
				
				if($advertiser_id)
				{
					$tableName = $tablePre.$advertiser_id."_".$this->dateYm;
					
					foreach($creativeArr as $creative_id => $sourceArr)
					{
						foreach($sourceArr as $source_id => $countryArr)
						{
							// Get Source ID (Will exist for Affiliates only) //
							$source_rev_share = $this->getSourceRevShare($source_id);
							////////////////////////////////////////////////////
							
							foreach($countryArr as $country => $osArr)
							{
								if(strlen($country) > 3)
									$country = substr($country, 0, 3);
								
								foreach($osArr as $os => $browserArr)
								{
									foreach($browserArr as $browser => &$dataArr)
									{
										// Populate media_cost or spent (depending on what's not present) in dataArr //
										$dataArr = $this->calculateMediaCostOrSpent($dataArr, $campaign_bid, $source_rev_share);
										///////////////////////////////////////////////////////////////////////////////
										$key = $campaign_id."_".str_replace("-","_",$this->date);
                                                                                $this->redis_write->incrByFloat($key, $dataArr['spent']);
                                                                                $this->redis_write->expire($key, 86400);
										$ins = false;
										foreach($dataArr as $columnName => $columnData)
										{
											if($columnName != '')
											{
												$sql = "insert into {$tableName} (date, hour, campaign_id, creative_id, source, country, os, browser, {$columnName}) values ('{$this->date}', {$this->hour}, {$campaign_id}, {$creative_id}, '{$source_id}', '{$country}', '{$os}', '{$browser}', {$columnData}) on duplicate key update ";
                        
                        if($this->insertType == "increment")
                          $sql .= " {$columnName} = {$columnName} + {$columnData}";
                        elseif($this->insertType == "update")
                          $sql .= " {$columnName} = {$columnData}";
                          
												if(!mysqli_query($this->db_write, $sql))
													$this->error_str .= "Failed executing query in flushDetailDataIntoDB: ".$sql."\n".mysqli_error($this->db_write)."\n"; 
												else
													$ins = true;
											}
										}
										
										if($ins)
											$this->rows_inserted++;
									}
								}
							}
						}
					}
				}
			}
		}
		
		$flush_end_time = microtime(true);
		$this->debug("Details flushed in ".($flush_end_time - $flush_start_time)." secs\n");
	}
	
	/*
	 *Flush domains data into DB
	 *
	*/
	private function flushDomainsDataIntoDB()
	{
		$flush_start_time = microtime(true);
		$tablePre = "adv_domains_";
		$sql_arr = array();						// Array containing sql queries for different column names
		$sql_values_arr = array();				// Array containing sql query values for different column names
		$sc = 0;
		
		if(is_array($this->domainsData) && count($this->domainsData) > 0)
		{
			foreach($this->domainsData as $campaign_id => $creativeArr)
			{
				$advertiser_id = $this->campaignsArr[$campaign_id]['advertiser_id'];
				$campaign_bid = $this->campaignsArr[$campaign_id]['bid'];
				
				if($advertiser_id)
				{
					$tableName = $tablePre.$advertiser_id."_".$this->dateYm;
					
					foreach($creativeArr as $creative_id => $sourceArr)
					{
						foreach($sourceArr as $source_id => $domainsArr)
						{
							// Get Source ID (Will exist for Affiliates only) //
							$source_rev_share = $this->getSourceRevShare($source_id);
							////////////////////////////////////////////////////
							
							foreach($domainsArr as $domain => &$dataArr)
							{
								// Populate media_cost or spent (depending on what's not present) in dataArr //
								$dataArr = $this->calculateMediaCostOrSpent($dataArr, $campaign_bid, $source_rev_share);
								///////////////////////////////////////////////////////////////////////////////
								
								foreach($dataArr as $columnName => $columnData)
								{
									if($columnName != '')
									{
										// Each combination of tableName and column name will have its own Query //
										$query_key = $columnName."_".$tableName;
										///////////////////////////////////////////////////////////////////////////
										
										if(isset($sql_arr[$query_key]))
										{
											$sql_values_arr[$query_key] .= ", ('{$this->date}', {$campaign_id}, {$creative_id}, '{$source_id}', '{$domain}', {$columnData})";
											$sql_arr[$query_key]['count']++;
										}
										else{
											$sql_arr[$query_key]['query'] = "insert into {$tableName} (date, campaign_id, creative_id, source, domain, {$columnName}) values ";
											
                      $sql_arr[$query_key]['duplicateQry'] = " on duplicate key update ";
                      if($this->insertType == "increment")
                        $sql_arr[$query_key]['duplicateQry'] .= "  {$columnName} = {$columnName} + VALUES({$columnName})";
                      elseif($this->insertType == "update")
                        $sql_arr[$query_key]['duplicateQry'] .= " {$columnName} = VALUES({$columnName})";
                        
											$sql_values_arr[$query_key] = "('{$this->date}', {$campaign_id}, {$creative_id}, '{$source_id}', '{$domain}', {$columnData})";
										}
										
										if($columnName == 'media_cost')
											$this->domains_media_cost += $columnData;
									}
								}
							}
						}
					}
				}
			}
		}
		
		// Execute all column queries now //
		mysqli_ping($this->db_write);
		foreach($sql_arr as $query_key => $qry_arr)
		{
			$query = $qry_arr['query'];
			$duplicateQry = $qry_arr['duplicateQry'];
			
			$sql = $query.$sql_values_arr[$query_key].$duplicateQry;
			
			$this->execute_query($sql, $this->db_write, 10);
		}
		
		$flush_end_time = microtime(true);
		$this->debug("Domains flushed in ".($flush_end_time - $flush_start_time)." secs\n");
	}
	
	/*
	 *Flush domains data into DB
	 *
	*/
	private function flushKeywordsDataIntoDB()
	{
		$flush_start_time = microtime(true);
		$tablePre = "adv_keywords_";
		$sql_arr = array();						// Array containing sql queries for different column names
		$sql_values_arr = array();				// Array containing sql query values for different column names
		$sc = 0;
		
		if(is_array($this->keywordsData) && count($this->keywordsData) > 0)
		{
			foreach($this->keywordsData as $campaign_id => $creativeArr)
			{
				$advertiser_id = $this->campaignsArr[$campaign_id]['advertiser_id'];
				$campaign_bid = $this->campaignsArr[$campaign_id]['bid'];
				
				if($advertiser_id)
				{
					$tableName = $tablePre.$advertiser_id."_".$this->dateYm;
					
					foreach($creativeArr as $creative_id => $sourceArr)
					{
						foreach($sourceArr as $source_id => $keywordsArr)
						{
							// Get Source ID (Will exist for Affiliates only) //
							$source_rev_share = $this->getSourceRevShare($source_id);
							////////////////////////////////////////////////////
							
							foreach($keywordsArr as $keyword => &$dataArr)
							{
								// Populate media_cost or spent (depending on what's not present) in dataArr //
								$dataArr = $this->calculateMediaCostOrSpent($dataArr, $campaign_bid, $source_rev_share);
								///////////////////////////////////////////////////////////////////////////////
								
								foreach($dataArr as $columnName => $columnData)
								{
									if($columnName != '')
									{
										// Each combination of tableName and column name will have its own Query //
										$query_key = $columnName."_".$tableName;
										///////////////////////////////////////////////////////////////////////////
										
										if(isset($sql_arr[$query_key]))
										{
											$sql_values_arr[$query_key] .= ", ('{$this->date}', {$this->hour}, {$campaign_id}, {$creative_id}, '{$source_id}', '{$keyword}', {$columnData})";
											$sql_arr[$query_key]['count']++;
										}
										else{
											$sql_arr[$query_key]['query'] = "insert into {$tableName} (date, hour, campaign_id, creative_id, source, keyword, {$columnName}) values ";
                      
											$sql_arr[$query_key]['duplicateQry'] = " on duplicate key update ";
											if($this->insertType == "increment")
												$sql_arr[$query_key]['duplicateQry'] .= "  {$columnName} = {$columnName} + VALUES({$columnName})";
											elseif($this->insertType == "update")
												$sql_arr[$query_key]['duplicateQry'] .= " {$columnName} = VALUES({$columnName})";
                        
											$sql_values_arr[$query_key] = "('{$this->date}', {$this->hour}, {$campaign_id}, {$creative_id}, '{$source_id}', '{$keyword}', {$columnData})";
										}
									}
								}
							}
						}
					}
				}
			}
		}
		
		// Execute all column queries now //
		mysql_ping($this->db_write);
		foreach($sql_arr as $query_key => $qry_arr)
		{
			$query = $qry_arr['query'];
			$duplicateQry = $qry_arr['duplicateQry'];
			
			$sql = $query.$sql_values_arr[$query_key].$duplicateQry;
			
			$this->execute_query($sql, $this->db_write, 10);
		}
		
		$flush_end_time = microtime(true);
		$this->debug("Keywords flushed in ".($flush_end_time - $flush_start_time)." secs\n");
	}
	
	/*
	 * Get Source's rev share (if it exists - In case of Affiliates only)
	 *
	*/
	private function getSourceRevShare($source_id)
	{
		// Get Affiliate ID (if exists) from source and rev_share if it exists //
		$source_id_ex = explode("_", $source_id);
		$source_rev_share = 0;
		if(isset($source_id_ex[1]))
		{
			if(isset($this->affiliateIDsArr[$source_id_ex[1]]) && isset($this->affiliateIDsArr[$source_id_ex[1]]['rev_share']))
				$source_rev_share = $this->affiliateIDsArr[$source_id_ex[1]]['rev_share'];
		}
		/////////////////////////////////////////////////////////////////////////
		
		return $source_rev_share;
	}
	
	/*
	 * Calculate and return media_cost (if spent amount exists) or spent amount (if media_cost exists) 
	 *
	*/
	private function calculateMediaCostOrSpent($dataArr, $campaign_bid, $source_rev_share)
	{
		// Calculate advertiser spent amount or media cost (whichever doesn't exist) //
		if($this->type == "CPM" && isset($dataArr["impressions"]) && $dataArr['impressions'] > 0)
		{
			if(isset($dataArr['media_cost']))
			{
				$campaign_spent = ( $campaign_bid * $dataArr["impressions"] ) / 1000;
				$dataArr['spent'] = $campaign_spent;
			}
			else if(isset($dataArr['spent']))
			{
				$dataArr['media_cost'] = $source_rev_share * $dataArr['spent'];
				
				// Source rev share should not be 0 at this point. Check for that condition //
				if(isset($source_id) && trim($source_id) != '' && $source_rev_share == 0)
					$this->error_str .= "Rev share for Source ".$source_id." is 0\n";
			}
		}
		
		if($this->type == "CPC" && isset($dataArr["clicks"]) && $dataArr['clicks'] > 0)
		{
			if(isset($dataArr['media_cost']))
			{
				$campaign_spent = ( $campaign_bid * $dataArr["clicks"] );
				$dataArr['spent'] = $campaign_spent;
			}
			else if(isset($dataArr['spent']))
			{
				$dataArr['media_cost'] = $source_rev_share * $dataArr['spent'];
				
				// Source rev share should not be 0 at this point. Check for that condition //
				if(isset($source_id) && trim($source_id) != '' && $source_rev_share == 0)
					$this->error_str .= "Rev share for Source ".$source_id." is 0\n";
			}
		}
		////////////////////////////////////////////////////////////////////////////////////////
		
		return $dataArr;
	}
	
	/*
	 *Creates advertiser monthly tables for all unique advertiser ids through this iteration
	 *
	*/
	private function createAdvTables()
	{
		if(is_array($this->advIdsArr))
		{
			foreach($this->advIdsArr as $adv_id => $tablesCreated)
			{
				if(!$tablesCreated)
				{
					$this->create_details_table($adv_id);
					if($this->type == "CPM")
						$this->create_domains_table($adv_id);
					else if($this->type == "CPC")
						$this->create_keywords_table($adv_id);
					
					$this->advIdsArr[$adv_id] = true;
				}
			}
		}else{
			$this->error_str .= "advIdsArr is not an array\n";
		}
	}
	
	/*
	 *Clean up arrays and variables
	 *
	*/
	private function cleanUp()
	{
		$this->detailData = array();
		$this->domainsData = array();
		$this->keywordsData = array();
		$this->count = 0;
	}
	
	/*
	 *Display debug info
	 *@param Info to be displayed
	*/
	private function debug($info = '', $error = false)
	{
	    return;
	    
		echo __CLASS__;
		if(!$error)
			echo " Debug: ";
		else
			echo " Error: ";
		echo $info."\n";
	}
	
	/*
	 *Execute query with multiple retries (To counter deadlock issue)
	 *@param string query to be executed
	 *@param resource db DB connection
	 *@param int retries Number of retries
	*/
	private function execute_query($query, $db, $retries = 3)
	{
		$query_id = rand(10000, 100000);
		if($retries <= 0)
			$retries = 1;
		
		$r = 0;				// Count of total retries
		$exec = false; 		// If query executed successfully or not
		
		while($r < $retries && !$exec)
		{
			$r++;
			
			$this->debug("Retry #".$r." on query ID ".$query_id);
			if(mysqli_query($db, $query))
				$exec = true;
			else
				usleep(50000);
		}
		
		if(!$exec)
		{
			$this->error_str .= "Failed executing query in execute_query: ".$query."\n".mysqli_error($db)."\n"; 
			$this->debug("Failed executing query in execute_query => ".$query."\n".mysqli_error($db)."\n");
		}
	}
	
	/*
	 *Creates advertiser details table for passed advertiser ID
	 *
	 *@param int adv_id Advertiser ID
	*/
	private function create_details_table($adv_id)
	{
		if($adv_id)
		{
			mysqli_ping($this->db_write);
			$tableName = "adv_details_".$adv_id."_".$this->dateYm;

			$tableExists = false;
			$tSql = "SELECT 1 FROM {$tableName} LIMIT 1";
			$tRes = mysqli_query($this->db_write, $tSql);
			if ($tRes) {
				$tRow = mysqli_fetch_assoc($tRes);
				if ($tRow)
					$tableExists = true;
			}
			
			if(!$tableExists)
			{
				$cSql = "create table if not exists {$tableName} (
						date date not null,
						hour tinyint not null,
						campaign_id int(8) not null,
						creative_id int(8) not null,
						source varchar(100) not null default '',
						country varchar(20) not null default '',
						os varchar(100) not null default '',
						browser varchar(100) not null default '',
						impressions int(11) not null default 0,
						clicks int(11) not null default 0,
						conversions int(11) not null default 0,
						spent decimal(15, 7) not null default 0,
						media_cost decimal(15, 7) not null default 0,
						UNIQUE KEY un (date, hour, campaign_id, creative_id, source, country, os, browser)
						) ENGINE=InnoDB;";
				if(!mysqli_query($this->db_write, $cSql))
					$this->error_str .= "Error creating table {$tableName}. Query: ".$cSql."\n".mysqli_error($this->db_write)."\n";
				else
					$this->debug("Table {$tableName} created");
			}
		}
	}
	
	/*
	 *Creates advertiser domains table for passed advertiser ID
	 *
	 *@param int adv_id Advertiser ID
	*/
	private function create_domains_table($adv_id)
	{
		if($adv_id)
		{
			mysqli_ping($this->db_write);
			$tableName = "adv_domains_".$adv_id."_".$this->dateYm;

			$tableExists = false;
			$tSql = "SELECT 1 FROM {$tableName} LIMIT 1";
			$tRes = mysqli_query($this->db_write, $tSql);
			if ($tRes) {
				$tRow = mysqli_fetch_assoc($tRes);
				if ($tRow)
					$tableExists = true;
			}
			
			if(!$tableExists)
			{
				$cSql = "create table if not exists {$tableName} (
						date date not null,
						campaign_id int(8) not null,
						creative_id int(8) not null,
						source varchar(100) not null default '',
						domain varchar(200) not null default '',
						impressions int(11) not null default 0,
						clicks int(11) not null default 0,
						conversions int(11) not null default 0,
						spent decimal(15, 7) not null default 0,
						media_cost decimal(15, 7) not null default 0,
						UNIQUE KEY un (date, campaign_id, creative_id, source, domain)
						) ENGINE=InnoDB;";
				if(!mysqli_query($this->db_write, $cSql))
					$this->error_str .= "Error creating table {$tableName}. Query: ".$cSql."\n".mysqli_error($this->db_write)."\n";
				else
					$this->debug("Table {$tableName} created");
			}
		}
	}
	
	/*
	 *Creates advertiser keywords table (CPC clicks) for passed advertiser ID
	 *
	 *@param int adv_id Advertiser ID
	*/
	private function create_keywords_table($adv_id)
	{
		if($adv_id)
		{
			mysqli_ping($this->db_write);
			$tableName = "adv_keywords_".$adv_id."_".$this->dateYm;

			$tableExists = false;
			$tSql = "SELECT 1 FROM {$tableName} LIMIT 1";
			$tRes = mysqli_query($this->db_write, $tSql);
			if ($tRes) {
				$tRow = mysqli_fetch_assoc($tRes);
				if ($tRow)
					$tableExists = true;
			}
			
			if(!$tableExists)
			{
				$cSql = "create table if not exists {$tableName} (
						date date not null,
						hour tinyint not null default 0,
						campaign_id int(8) not null,
						creative_id int(8) not null,
						source varchar(100) not null default '',
						keyword varchar(200) not null default '',
						impressions int(11) not null default 0,
						clicks int(11) not null default 0,
						conversions int(11) not null default 0,
						spent decimal(15, 7) not null default 0,
						media_cost decimal(15, 7) not null default 0,
						UNIQUE KEY un (date, hour, campaign_id, creative_id, source, keyword)
						) ENGINE=InnoDB;";
				if(!mysqli_query($this->db_write, $cSql))
					$this->error_str .= "Error creating table {$tableName}. Query: ".$cSql."\n".mysqli_error($this->db_write)."\n";
				else
					$this->debug("Table {$tableName} created");
			}
		}
	}
}


?>