<?php
require_once($_SERVER["DOCUMENT_ROOT"].'/library/functions.php'); 
set_time_limit(3600); 
openDBLIPlayground3311("plextrac");
echo '1';
$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;
}
*/
echo '2';
//$airtableBase = "appRZplVZ2Trjhwz5";
//$airtableKey = "keylYt16XcQzCi9kE"


// 6sense Campaigns
$completed = 0;
$totalUpdated = 0;
$offsetParam = "";
$ct = 0;
while ($completed==0)
{
$ct++;
if ($ct>1000)
{
	break;
}
$token = "patlt5A9nh6P5f8wd.1bd28a5a640f47c2656fec2e30c5b5644c429e46881ee29c8d65b7c99134df20";
$appID = "appRZplVZ2Trjhwz5";
$tableID = "tbl3BTaUVanIwQR55";
$view = "viwuiTUby1vdDHGQ9";
$url = "https://api.airtable.com/v0/$appID/$tableID?view=$view&filterByFormula={Ticker}%3C144000";
$loadURL = $url.$offsetParam;

echo $loadURL."<BR>";
$response = queryAirtable($loadURL,$token);

//$url = "https://api.airtable.com/v0/appK5rWONNDAmxxzK/Client%20Scoring?api_key=keylYt16XcQzCi9kE";
//$url = "https://api.airtable.com/v0/".$airtableBase."/6sense%20Campaign?&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;
}
echo '3';
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'] : "");
	
	$campaignID = (isset($arr2["fields"]["Campaign ID"]) ? trim($arr2["fields"]["Campaign ID"]) : "");
	$adGroupID = (isset($arr2["fields"]["Ad Group ID"]) ? trim($arr2["fields"]["Ad Group ID"]) : "");
	$adVariationID = (isset($arr2["fields"]["Ad Variation ID"]) ? trim($arr2["fields"]["Ad Variation ID"]) : "");
	$segmentName= (isset($arr2["fields"]["Segment Name"]) ? trim($arr2["fields"]["Segment Name"]) : "");
	$campaignName = (isset($arr2["fields"]["Campaign Name"]) ? trim($arr2["fields"]["Campaign Name"]) : "");
	$adGroup = (isset($arr2["fields"]["Ad Group"]) ? trim($arr2["fields"]["Ad Group"]) : "");
	$adVariation = (isset($arr2["fields"]["Ad Variation"]) ? trim($arr2["fields"]["Ad Variation"]) : "");
	$screenshotArr = (isset($arr2["fields"]["Ad Screenshot"]) ? $arr2["fields"]["Ad Screenshot"] : "");
	
	$screenshot = "";
	if (is_array($screenshotArr))
	{
	 $screenshot =  $screenshotArr[0]["url"];
	 $filetype =  $screenshotArr[0]["type"];
	 $filename = $screenshotArr[0]["filename"];
	 
	 $screenshot =  backupFile($screenshot,$filetype,$filename);
	}
	

	if (strlen($adVariationID)>0)
	{
	$strSQL = "INSERT INTO db_airtable_6sense_campaign (_campaignID,_adGroupID,_adVariationID,_segmentName,_campaignName,_adGroup,_adVariation,_adScreenshot) VALUES ('$campaignID','$adGroupID','$adVariationID','$segmentName','$campaignName','$adGroup','$adVariation','$screenshot') ON DUPLICATE KEY UPDATE _segmentName = VALUES(_segmentName),_campaignName = VALUES(_campaignName),_adGroup = VALUES(_adGroup),_adVariation = VALUES(_adVariation),_adScreenshot = VALUES(_adScreenshot)";
	$strSQL = escapeSQL($strSQL);
	echo $strSQL.'<BR>';
	$objConn->query($strSQL);
	}
	
	}
	//var_dump($arr);
}

}


// 6sense Segment
$completed = 0;
$totalUpdated = 0;
$offsetParam = "";
$ct = 0;
while ($completed==0)
{
$ct++;
if ($ct>1000)
{
	break;
}
$token = "patlt5A9nh6P5f8wd.1bd28a5a640f47c2656fec2e30c5b5644c429e46881ee29c8d65b7c99134df20";
$appID = "appRZplVZ2Trjhwz5";
$tableID = "tblJ0oeDgnMikb9yj";
$view = "viwUpeyHZ0h23HyLk";
$url = "https://api.airtable.com/v0/$appID/$tableID?view=$view&filterByFormula={Ticker}%3C1440";
$loadURL = $url.$offsetParam;

echo $loadURL."<BR>";
$response = queryAirtable($loadURL,$token);

//$url = "https://api.airtable.com/v0/appK5rWONNDAmxxzK/Client%20Scoring?api_key=keylYt16XcQzCi9kE";
//$url = "https://api.airtable.com/v0/".$airtableBase."/6sense%20Campaign?&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'] : "");
	
	$campaignID = (isset($arr2["fields"]["_campaignID"]) ? trim($arr2["fields"]["_campaignID"]) : "");
	$campaignName = (isset($arr2["fields"]["_campaignName"]) ? trim($arr2["fields"]["_campaignName"]) : "");
	$campaignType = (isset($arr2["fields"]["_campaignType"]) ? trim($arr2["fields"]["_campaignType"]) : "");
	$endDate = (isset($arr2["fields"]["_endDate"]) ? trim($arr2["fields"]["_endDate"]) : "");
	$startDate= (isset($arr2["fields"]["_startDate"]) ? trim($arr2["fields"]["_startDate"]) : "");	
	$segmentName = (isset($arr2["fields"]["_segmentName"]) ? trim($arr2["fields"]["_segmentName"]) : "");
	
	if (strlen($campaignID)>0)
	{
	$strSQL = "INSERT INTO db_airtable_6sense_segment (_campaignID,_campaignName,_campaignType,_endDate,_startDate,_segmentName) VALUES ('$campaignID','$campaignName','$campaignType','$endDate','$startDate','$segmentName') ON DUPLICATE KEY UPDATE _campaignID = VALUES(_campaignID),_campaignName = VALUES(_campaignName),_campaignType = VALUES(_campaignType),_endDate = VALUES(_endDate),_startDate = VALUES(_startDate),_segmentName = VALUES(_segmentName)";
	$strSQL = escapeSQL($strSQL);
	echo $strSQL.'<BR>';
	$objConn->query($strSQL);
	}
	
	}
	//var_dump($arr);
}

}


/*
$token = "patlt5A9nh6P5f8wd.1bd28a5a640f47c2656fec2e30c5b5644c429e46881ee29c8d65b7c99134df20";
$appID = "appRZplVZ2Trjhwz5";
$tableID = "";
$view = "";
$url = "https://api.airtable.com/v0/$appID/$tableID?view=$view&filterByFormula={Ticker}%3C1440";
$loadURL = $url.$offsetParam;

echo $loadURL."<BR>";
$response = queryAirtable($loadURL,$token);
//$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;
}

$token = "patlt5A9nh6P5f8wd.1bd28a5a640f47c2656fec2e30c5b5644c429e46881ee29c8d65b7c99134df20";
$appID = "appRZplVZ2Trjhwz5";
$tableID = "";
$view = "";
$url = "https://api.airtable.com/v0/$appID/$tableID?view=$view&filterByFormula={Ticker}%3C1440";
$loadURL = $url.$offsetParam;

echo $loadURL."<BR>";
$response = queryAirtable($loadURL,$token);

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

?>