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

$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 = "appvpWU4Zcs5P9QZc";
$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}%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'] : "");
	
	$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"] : "");

	// Blend360
	$emailName = (isset($arr2["fields"]["Email Name"]) ? $arr2["fields"]["Email Name"] : "");
	$emailProof = (isset($arr2["fields"]["Email Proof"]) ? $arr2["fields"]["Email Proof"] : "");
	$previewText = (isset($arr2["fields"]["Preview Text"]) ? $arr2["fields"]["Preview Text"] : "");
	$sendTime = (isset($arr2["fields"]["Send Time"]) ? $arr2["fields"]["Send Time"] : "");
	$businessUnit = (isset($arr2["fields"]["Business Unit"]) ? $arr2["fields"]["Business Unit"] : "");
	$sends = (isset($arr2["fields"]["Sends"]) ? $arr2["fields"]["Sends"] : "");
	$emailFilters = (isset($arr2["fields"]["Email Filters"]) ? $arr2["fields"]["Email Filters"] : "");

	
	$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 = "INSERT INTO db_airtable_email (_code,_landingPage,_subject,_whatWeDo,_liveDate,_trimCode,_screenshot,_utm_campaign, _utm_medium,_utm_source,_pardotID,_assetTitle,_emailName,_emailProof,_previewText,_sendTime,_businessUnit,_sends,_emailFilters) VALUES ('$code','$landingPage','$subject','$whatWeDo','$liveDate','$trimCode','$screenshot','$campaign','$medium','$source','$pardotID','$assetTitle','$emailName','$emailProof','$previewText','$sendTime','$businessUnit','$sends','$emailFilters') 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),_emailName = VALUES(_emailName),_emailProof = VALUES(_emailProof),_previewText = VALUES(_previewText),_sendTime = VALUES(_sendTime),_businessUnit = VALUES(_businessUnit),_sends = VALUES(_sends),_emailFilters = VALUES(_emailFilters)";
	$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}%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'] : "");
	
	$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"] : "");
	$adVariation = (isset($arr2["fields"]["Ad Variation"]) ? $arr2["fields"]["Ad Variation"] : "");
	
	$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,_adVariation,_landingPageURL,_screenshot,_source,_medium,_campaign,_content,_liveDate,_status) VALUES ($adID,'$adType','$platform','$adVariation','$landingPage','$screenshot','$source','$medium','$campaign','$content','$liveDate','$status') ON DUPLICATE KEY UPDATE _adType = VALUES(_adType),_platform = VALUES(_platform),_adVariation = VALUES(_adVariation),_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);
}

}

$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);
}

?>