<?php
require_once($_SERVER["DOCUMENT_ROOT"].'/library/functions.php'); 
set_time_limit(3600); 
openDBLIPlayground3313("prospiant");

$min = date("i");

if (my_bcmod($min,1)==0)
{

	global $backupArray;
	$backupArray = array();
	$strSQL = "SELECT _source, _backup FROM airtable_file_backup";
	$result = getArrayResult($strSQL);
	
	foreach ($result as $row)
	{
		$backupArray[$row[0]] = $row[1];;
	}

$completed = 0;
$totalUpdated = 0;
$offsetParam = "";
$ct = 0;
while ($completed==0)
{
$ct++;
if ($ct>1000)
{
	break;
}

$airtableBase = "appjBenVA8OomuQWF";
$airtableKey = "keylYt16XcQzCi9kE";

//$url = "https://api.airtable.com/v0/appK5rWONNDAmxxzK/Client%20Scoring?api_key=keylYt16XcQzCi9kE";
$url = "https://api.airtable.com/v0/".$airtableBase."/Email%20Campaign%20Database?&filterByFormula={Ticker}%3C1440&api_key=".$airtableKey.$offsetParam;
$response = file_get_contents($url);

$array = json_decode($response,true);


if (isset($array["offset"]))
{
	$offsetParam = "&offset=".$array["offset"];
}
else
{
	$completed=1;
}

foreach ($array as $key=>$arr)
{
	//echo "<textarea>";
	//var_dump($arr);
	//echo "</textarea>";
	
	foreach ($arr as $key2=>$arr2)
	{
	//$arr2 = $arr[0];
	
	//echo "<textarea>";
	//var_dump($arr2);
	//echo "</textarea>";
	
	//$client = (isset($_POST['client']) ? $_POST['client'] : "");
	
	$code = (isset($arr2["fields"]["Campaign Code"]) ? trim($arr2["fields"]["Campaign Code"]) : "");
	$pardotID = (isset($arr2["fields"]["Campaign ID"]) ? trim($arr2["fields"]["Campaign ID"]) : "");
	$landingPage = (isset($arr2["fields"]["Landing Page URL"]) ? trim($arr2["fields"]["Landing Page URL"]) : "");
	$subject = (isset($arr2["fields"]["Subject"]) ? trim($arr2["fields"]["Subject"]) : "");
	$whatWeDo = (isset($arr2["fields"]["What did we do?"]) ? trim($arr2["fields"]["What did we do?"]) : "");
	$liveDate = (isset($arr2["fields"]["Planned Live Date"]) ? trim($arr2["fields"]["Planned Live Date"]) : "");
	$screenshotArr = (isset($arr2["fields"]["Screenshot"]) ? $arr2["fields"]["Screenshot"] : "");
	$assetTitle = (isset($arr2["fields"]["Asset Title"]) ? $arr2["fields"]["Asset Title"] : "");
	
	$screenshot = "";
	if (is_array($screenshotArr))
	{
	 $screenshot =  $screenshotArr[0]["url"];
	 $filetype =  $screenshotArr[0]["type"];
	 $filename = $screenshotArr[0]["filename"];
	 
	 $screenshot =  backupFile($screenshot,$filetype,$filename);
	}


	// Extract UTM parameters
	$campaign = "";
	$medium = "";
	$source = "";
	
	   $break = explode("?",$landingPage);
	   $url = $break[0];
	   $params = str_replace($break[0]."?","",$landingPage);
	   $break = explode("&",$params);
	   foreach ($break as $param)
	   {
		   $break2 = explode("=",$param);
		   $key = (isset($break2[0]) ? $break2[0] : "");
		   $val = (isset($break2[1]) ? $break2[1] : "");
		   switch ($key)
		   {
			   case "utm_campaign":
				$campaign = $val;
			   break;
			   case "utm_medium":
				$medium = $val;
			   break;
			   case "utm_source":
				$source = $val;
			   break;	   
		   }
	   }


	$trimCode = trimCode($code);
	
	if (strlen($code)>0)
	{
	$strSQL = "INSERT INTO db_airtable_email (_code,_landingPage,_subject,_whatWeDo,_liveDate,_trimCode,_screenshot,_utm_campaign, _utm_medium,_utm_source,_pardotID,_assetTitle) VALUES ('$code','$landingPage','$subject','$whatWeDo','$liveDate','$trimCode','$screenshot','$campaign','$medium','$source','$pardotID','$assetTitle') ON DUPLICATE KEY UPDATE _landingPage = VALUES(_landingPage),_subject = VALUES(_subject),_whatWeDo = VALUES(_whatWeDo),_liveDate = VALUES(_liveDate),_trimCode = VALUES(_trimCode),_screenshot = VALUES(_screenshot),_utm_medium = VALUES(_utm_medium),_utm_campaign = VALUES(_utm_campaign),_utm_source = VALUES(_utm_source),_pardotID = VALUES(_pardotID),_assetTitle = VALUES(_assetTitle)";
	$strSQL = escapeSQL($strSQL);
	echo $strSQL.'<BR>';
	$objConn->query($strSQL);
	}
	
	}
	//var_dump($arr);
}

}


$completed = 0;
$totalUpdated = 0;
$offsetParam = "";
$ct = 0;
while ($completed==0)
{
$ct++;
if ($ct>1000)
{
	break;
}

//$url = "https://api.airtable.com/v0/appK5rWONNDAmxxzK/Client%20Scoring?api_key=keylYt16XcQzCi9kE";
$url = "https://api.airtable.com/v0/".$airtableBase."/Ads%20Database?&filterByFormula={Ticker}%3C144000&api_key=".$airtableKey.$offsetParam;
$response = file_get_contents($url);

$array = json_decode($response,true);


if (isset($array["offset"]))
{
	$offsetParam = "&offset=".$array["offset"];
}
else
{
	$completed=1;
}

foreach ($array as $key=>$arr)
{
	//echo "<textarea>";
	//var_dump($arr);
	//echo "</textarea>";
	
	foreach ($arr as $key2=>$arr2)
	{
	//$arr2 = $arr[0];
	
	//echo "<textarea>";
	//var_dump($arr2);
	//echo "</textarea>";
	
	//$client = (isset($_POST['client']) ? $_POST['client'] : "");
	
	$adID = (isset($arr2["fields"]["Ad ID"]) ? trim($arr2["fields"]["Ad ID"]) : "");
	$adType = (isset($arr2["fields"]["Ad Type"]) ? trim($arr2["fields"]["Ad Type"]) : "");
	$platform = (isset($arr2["fields"]["Platform"]) ? trim($arr2["fields"]["Platform"]) : "");
	$landingPage= (isset($arr2["fields"]["Landing Page URL"]) ? trim($arr2["fields"]["Landing Page URL"]) : "");
	$reportingGroup = (isset($arr2["fields"]["Reporting Group"]) ? trim($arr2["fields"]["Reporting Group"]) : "");
	$liveDate = (isset($arr2["fields"]["Live Date"]) ? trim($arr2["fields"]["Live Date"]) : "");
	$status = (isset($arr2["fields"]["Status"]) ? trim($arr2["fields"]["Status"]) : "");
	$screenshotArr = (isset($arr2["fields"]["Screenshot"]) ? $arr2["fields"]["Screenshot"] : "");
	
	$screenshot = "";
	if (is_array($screenshotArr))
	{
	 $screenshot =  $screenshotArr[0]["url"];
	 $filetype =  $screenshotArr[0]["type"];
	 $filename = $screenshotArr[0]["filename"];
	 
	 $screenshot =  backupFile($screenshot,$filetype,$filename);
	}

	// Extract UTM parameters
	$campaign = "";
	$medium = "";
	$source = "";
	
	   $break = explode("?",$landingPage);
	   $url = $break[0];
	   $params = str_replace($break[0]."?","",$landingPage);
	   $break = explode("&",$params);
	   foreach ($break as $param)
	   {
		   $break2 = explode("=",$param);
		   $key = (isset($break2[0]) ? $break2[0] : "");
		   $val = (isset($break2[1]) ? $break2[1] : "");
		   switch ($key)
		   {
			   case "utm_campaign":
				$campaign = $val;
			   break;
			   case "utm_medium":
				$medium = $val;
			   break;
			   case "utm_source":
				$source = $val;
			   break;	   
		   }
	   }

	
	if (strlen($adID)>0)
	{
	$strSQL = "INSERT INTO db_airtable_ads (_adID,_adType,_platform,_reportingGroup,_landingPageURL,_screenshot,_source,_medium,_campaign,_content,_liveDate,_status) VALUES ($adID,'$adType','$platform','$reportingGroup','$landingPage','$screenshot','$source','$medium','$campaign','$content','$liveDate','$status') ON DUPLICATE KEY UPDATE _adType = VALUES(_adType),_platform = VALUES(_platform),_reportingGroup = VALUES(_reportingGroup),_landingPageURL = VALUES(_landingPageURL),_screenshot = VALUES(_screenshot),_source = VALUES(_source),_medium = VALUES(_medium),_campaign = VALUES(_campaign),_content = VALUES(_content),_liveDate = VALUES(_liveDate),_status = VALUES(_status)";
	$strSQL = escapeSQL($strSQL);
	echo $strSQL.'<BR>';
	$objConn->query($strSQL);
	}
	
	}
	//var_dump($arr);
}

}


// Ads Intent Engagement - Start
$completed = 0;
$totalUpdated = 0;
$offsetParam = "";
$ct = 0;
while ($completed==0)
{
$ct++;
if ($ct>1000)
{
	break;
}

//$url = "https://api.airtable.com/v0/appK5rWONNDAmxxzK/Client%20Scoring?api_key=keylYt16XcQzCi9kE";
//$url = "https://api.airtable.com/v0/".$airtableBase."/High%20Intent%20Contacts%20Performance?api_key=".$airtableKey.$offsetParam;
$url = "https://api.airtable.com/v0/".$airtableBase."/Ads%20Intent%20Engagement?&filterByFormula={Ticker}%3C1440&api_key=".$airtableKey.$offsetParam;
$response = file_get_contents($url);

$array = json_decode($response,true);


if (isset($array["offset"]))
{
	$offsetParam = "&offset=".$array["offset"];
}
else
{
	$completed=1;
}

foreach ($array as $key=>$arr)
{
	//echo "<textarea>";
	//var_dump($arr);
	//echo "</textarea>";
	
	foreach ($arr as $key2=>$arr2)
	{
	 //$arr2 = $arr[0];
	
	 //echo "<textarea>";
	 //var_dump($arr2);
	 //echo "</textarea>";
	
	//$client = (isset($_POST['client']) ? $_POST['client'] : "");
	
	$date = (isset($arr2["fields"]["Date"]) ? trim($arr2["fields"]["Date"]) : "");
	$campaignName = (isset($arr2["fields"]["Campaign Name"]) ? trim($arr2["fields"]["Campaign Name"]) : "");
	$segmentArr = (isset($arr2["fields"]["Segment"]) ? $arr2["fields"]["Segment"] : "");
	$accountName = (isset($arr2["fields"]["Account Name"]) ? trim($arr2["fields"]["Account Name"]) : "");
	$accountDomain = (isset($arr2["fields"]["Account Domain"]) ? trim($arr2["fields"]["Account Domain"]) : "");
	$accountLinkedIn = (isset($arr2["fields"]["Account LinkedIn URL"]) ? trim($arr2["fields"]["Account LinkedIn URL"]) : "");
	$medium = (isset($arr2["fields"]["Medium"]) ? trim($arr2["fields"]["Medium"]) : "");
	$engagementType = (isset($arr2["fields"]["Engagement Type"]) ? $arr2["fields"]["Engagement Type"] : "");
	$frequency = (isset($arr2["fields"]["Frequency"]) ? trim($arr2["fields"]["Frequency"]) : "");
	$pages = (isset($arr2["fields"]["Pages"]) ? trim($arr2["fields"]["Pages"]) : "");
	$duration = (isset($arr2["fields"]["Duration"]) ? trim($arr2["fields"]["Duration"]) : "");
	$contactName = (isset($arr2["fields"]["Contact Name"]) ? trim($arr2["fields"]["Contact Name"]) : "");
	$contactTitle = (isset($arr2["fields"]["Contact Title"]) ? trim($arr2["fields"]["Contact Title"]) : "");
	$contactEmail = (isset($arr2["fields"]["Contact Email"]) ? trim($arr2["fields"]["Contact Email"]) : "");
	$contactLinkedIn = (isset($arr2["fields"]["Contact LinkedIn URL"]) ? trim($arr2["fields"]["Contact LinkedIn URL"]) : "");
	$contactCountry = (isset($arr2["fields"]["Contact Country"]) ? trim($arr2["fields"]["Contact Country"]) : "");
	$notes = (isset($arr2["fields"]["Notes"]) ? trim($arr2["fields"]["Notes"]) : "");

	$segment = "";

	if (is_array($segmentArr))
	{
	 $segment =  implode(",",$segmentArr);
	}
	
	if (strlen($date)>0)
	{
	$strSQL = "INSERT INTO db_airtable_ads_intent_engagement (_date,_campaignName,_segment,_accountName,_accountDomain,_accountLinkedIn,_medium,_engagementType,_frequency,_pages,_duration,_contactName,_contactTitle,_contactEmail,_contactLinkedIn,_contactCountry,_notes) VALUES ('$date','$campaignName','$segment','$accountName','$accountDomain','$accountLinkedIn','$medium','$engagementType','$frequency','$pages','$duration','$contactName','$contactTitle','$contactEmail','$contactLinkedIn','$contactCountry','$notes') ON DUPLICATE KEY UPDATE _campaignName = VALUES(_campaignName),_segment = VALUES(_segment),_accountName = VALUES(_accountName),_accountLinkedIn = VALUES(_accountLinkedIn),_frequency = VALUES(_frequency),_pages = VALUES(_pages),_duration = VALUES(_duration),_contactName = VALUES(_contactName),_contactTitle = VALUES(_contactTitle),_contactLinkedIn = VALUES(_contactLinkedIn),_contactCountry = VALUES(_contactCountry),_notes = VALUES(_notes)";
	$strSQL = escapeSQL($strSQL);
	echo $strSQL.'<BR>';
	$objConn->query($strSQL);
	}
	
	}
	//var_dump($arr);
}
}
// Ads Intent Engagement - End


$strSQL = "UPDATE db_airtable_email SET _download_url = REPLACE(LEFT(_landingPage,POSITION('?' IN _landingPage)-1),'https://go.hrsoft.com/','')";
$objConn->query($strSQL);

$strSQL = "UPDATE db_airtable_email SET _url_param = RIGHT(_landingPage,LENGTH(_landingPage)-POSITION('?' IN _landingPage))";
$objConn->query($strSQL);

// Content Table

/*
//$url = "https://api.airtable.com/v0/appK5rWONNDAmxxzK/Client%20Scoring?api_key=keylYt16XcQzCi9kE";
$url = "https://api.airtable.com/v0/appk3IO4ktpMP9MdW/Content?api_key=keylYt16XcQzCi9kE";
$response = file_get_contents($url);

$array = json_decode($response,true);
//echo "<textarea>";
	//var_dump($array);
	//echo "</textarea>";

foreach ($array as $key=>$arr)
{
	//echo "<textarea>";
	//var_dump($arr);
	//echo "</textarea>";
	
	foreach ($arr as $key2=>$arr2)
	{
	//$arr2 = $arr[0];
	
	//echo "<textarea>";
	//var_dump($arr2);
	//echo "</textarea>";
	
	//$client = (isset($_POST['client']) ? $_POST['client'] : "");
	
	$baseCode = (isset($arr2["fields"]["Base Code"]) ? trim($arr2["fields"]["Base Code"]) : "");
	$productSolution = (isset($arr2["fields"]["Product/Solution"]) ? trim($arr2["fields"]["Product/Solution"]) : "");
	$target = (isset($arr2["fields"]["Target"]) ? trim($arr2["fields"]["Target"]) : "");
	$title = (isset($arr2["fields"]["Title"]) ? trim($arr2["fields"]["Title"]) : "");
	$completeDate = (isset($arr2["fields"]["Completed Date"]) ? trim($arr2["fields"]["Completed Date"]) : "");
	$fileArr = (isset($arr2["fields"]["File"]) ? $arr2["fields"]["File"] : "");
	
	$file = "";
	if (is_array($fileArr))
	{
	 $file =  $fileArr[0]["url"];
	}


	if (strlen($baseCode)>0)
	{
	$strSQL = "INSERT INTO abm_content (_baseCode,_title,_target,_productSolution,_completedDate,_file) VALUES ('$baseCode','$title','$target','$productSolution','$completeDate','$file') ON DUPLICATE KEY UPDATE _baseCode = VALUES(_baseCode),_target = VALUES(_target),_productSolution = VALUES(_productSolution),_completedDate = VALUES(_completedDate),_file = VALUES(_file)";
	$strSQL = escapeSQL($strSQL);
	echo $strSQL.'<BR>';
	$objConn->query($strSQL);
	}
	
	}
	//var_dump($arr);
}
*/
// End Content


}

function trimCode($code)
{
	$code = str_replace("-","",$code);
	$code = str_replace("_","",$code);
	$code = str_replace("(","",$code);
	$code = str_replace(")","",$code);
	return $code;
}

function printValues($arr) {
    global $count;
    global $values;
    
    // Check input is an array
    if(!is_array($arr)){
        die("ERROR: Input is not an array");
    }
    
    /*
    Loop through array, if value is itself an array recursively call the
    function else add the value found to the output items array,
    and increment counter by 1 for each value found
    */
    foreach($arr as $key=>$value){
        if(is_array($value)){
            printValues($value);
        } else{
            $values[] = $value;
            $count++;
        }
    }
    
    // Return total count and values found in array
    return array('total' => $count, 'values' => $values);
}

?>