<?php
set_time_limit(3600); 
require_once($_SERVER["DOCUMENT_ROOT"].'/library/functions.php'); 
  
openDBLIPlayground3313("sandler");

global $backupArray;
$backupArray = array();
$strSQL = "SELECT _source, _backup FROM airtable_file_backup";
$result = getArrayResult($strSQL);

foreach ($result as $row)
{
	$backupArray[$row[0]] = $row[1];;
}

// 6sense Ads Database
$appID = "apppYdeqICWOcMZZH";
$tableID = "tblPwzKZThLqzjx74";
$view = "viwRP0KYChL50b0Ze";
$url = "https://api.airtable.com/v0/$appID/$tableID?view=$view&filterByFormula={Ticker}%3C1440";
$token = "patlt5A9nh6P5f8wd.1bd28a5a640f47c2656fec2e30c5b5644c429e46881ee29c8d65b7c99134df20";
$tableName = "optimization_airtable_ads_6sense";
$fields = array();
//$fields["_adID"] = "Ad ID";
$fields["_adName"] = "Ad title";
//$fields["_screenshot"] = "Ad Images";
$fields["_segment"] = "Business segment";
//$fields["_designBackground"] = "Ad visual";
$fields["_screenshot"] = "Ad visual";
$fields["_adCopy"] = "Text on image";
$fields["_bodyText"] = "Body text";
$fields["_sponsoredText"] = "Sponsored text";
$fields["_ctaCopy"] = "CTA on image";
$fields["_platform"] = "Platform";
$fields["_size"] = "Size";
$fields["_designTemplate"] = "A/B: Template";
$fields["_designColor"] = "A/B: Color";
$fields["_designImages"] = "A/B: Image";
$fields["_designBlurp"] = "A/B: Blurb";
$fields["_logos"] = "A/B: Logo";
$fields["_copyMessaging"] = "A/B: Messaging";
$fields["_copyAssetType"] = "A/B: Asset type";
$fields["_copyTone"] = "A/B: Tone";
$fields["_copyProductCompanyName"] = "A/B: Product/company name";
$fields["_copyStatisticProofPoint"] = "A/B: Statistic/proof point";
$fields["_ctaCopySoftHard"] = "A/B: CTA copy";
$fields["_stage"] = "Stage";
$fields["_campaignID"] = "Campaign ID";
$fields["_campaignName"] = "Campaign name";

global $isImage;
global $isArray;

$isImage = array();
$isImage["Ad visual"] = 1;

$isArray = array();
$isArray["Stage"] = 1;


loadAirtable($url,$token,$tableName,$fields,$isImage);

// LinkedIn Ads
$appID = "apppYdeqICWOcMZZH";
$tableID = "tblU0vnWMZW78EQ9d";
$view = "viw5owDYwuKjLgV3C";
$url = "https://api.airtable.com/v0/$appID/$tableID?view=$view&filterByFormula={Ticker}%3C1440";
$token = "patlt5A9nh6P5f8wd.1bd28a5a640f47c2656fec2e30c5b5644c429e46881ee29c8d65b7c99134df20";
$tableName = "optimization_airtable_ads_linkedin";
$fields = array();
//$fields["_adID"] = "Ad ID";
$fields["_adName"] = "Ad Name";
//$fields["_screenshot"] = "Ad Images";
$fields["_segment"] = "Business segment";
//$fields["_designBackground"] = "Ad visual";
$fields["_screenshot"] = "Ad visual";
$fields["_adCopy"] = "Text on image";
$fields["_bodyText"] = "Body text";
$fields["_sponsoredText"] = "Sponsored text";
$fields["_ctaCopy"] = "CTA on image";
$fields["_platform"] = "Platform";
$fields["_size"] = "Size";
$fields["_designTemplate"] = "A/B: Template";
$fields["_designColor"] = "A/B: Color";
$fields["_designImages"] = "A/B: Image";
$fields["_designBlurp"] = "A/B: Blurb";
$fields["_logos"] = "A/B: Logo";
$fields["_copyMessaging"] = "A/B: Messaging";
$fields["_copyAssetType"] = "A/B: Asset type";
$fields["_copyTone"] = "A/B: Tone";
$fields["_copyProductCompanyName"] = "A/B: Product/company name";
$fields["_copyStatisticProofPoint"] = "A/B: Statistic/proof point";
$fields["_ctaCopySoftHard"] = "A/B: CTA copy";
$fields["_stage"] = "Stage";

global $isImage;
global $isArray;

$isImage = array();
$isImage["Ad visual"] = 1;

$isArray = array();
$isArray["Stage"] = 1;


loadAirtable($url,$token,$tableName,$fields,$isImage);


// Google Display Ads
$appID = "apppYdeqICWOcMZZH";
$tableID = "tblGPclszXsNtH8o6";
$view = "viwktzlvuESn4cXIA";
$url = "https://api.airtable.com/v0/$appID/$tableID?view=$view&filterByFormula={Ticker}%3C1440";
$token = "patlt5A9nh6P5f8wd.1bd28a5a640f47c2656fec2e30c5b5644c429e46881ee29c8d65b7c99134df20";
$tableName = "db_airtable_google_display_ads";
$fields = array();
//$fields["_adID"] = "Ad ID";
$fields["_adName"] = "Ad Name";
$fields["_campaignName"] = "Campaign Name";
$fields["_campaignID"] = "Campaign ID";
$fields["_adGroupName"] = "Ad Group Name";
$fields["_adGroupID"] = "Ad Group ID";
$fields["_adType"] = "Ad Type";
$fields["_adSize"] = "Ad Size";
$fields["_screenshot"] = "Ads Screenshot";
$fields["_finalURL"] = "Final URL";

global $isImage;
global $isArray;

$isImage = array();
$isImage["Ads Screenshot"] = 1;

$isArray = array();
$isArray["Ad Size"] = 1;


loadAirtable($url,$token,$tableName,$fields,$isImage);


// 6sense Segments to Campaign
$appID = "apppYdeqICWOcMZZH";
$tableID = "tbl9obFsg0kwujgiJ";
$view = "viwANGyIPrnUNdqat";
$url = "https://api.airtable.com/v0/$appID/$tableID?view=$view&filterByFormula={Ticker}%3C1440";
$token = "patlt5A9nh6P5f8wd.1bd28a5a640f47c2656fec2e30c5b5644c429e46881ee29c8d65b7c99134df20";
$tableName = "db_airtable_6sense_segment";
$fields = array();
//$fields["_adID"] = "Ad ID";
$fields["_name"] = "Name";
//$fields["_campaignID"] = "Campaign ID";
$fields["_campaignType"] = "Campaign Type";
$fields["_segmentName"] = "Segment Name";
$fields["_startDate"] = "Start Date";
$fields["_endDate"] = "End Date";

global $isImage;
global $isArray;

$isImage = array();
//$isImage["Ads Screenshot"] = 1;

$isArray = array();
//$isArray["Ad Size"] = 1;


loadAirtable2($url,$token,$tableName,$fields,$isImage,'_campaignID','Campaign ID');



function loadAirtable($url,$token,$tableName,$fields,$isImage=array())
{
	
global $objConn;
global $isImage;
global $isArray;
$completed = 0;
$totalUpdated = 0;
$offsetParam = "";
$ct = 0;
while ($completed==0)
{
$ct++;
if ($ct>1000)
{
	break;
}

$loadURL = $url.$offsetParam;

echo $loadURL."<BR>";
$response = queryAirtable($loadURL,$token);
//echo $response;
$array = json_decode($response,true);

//var_dump($array);

if (isset($array["offset"]))
{
	$offsetParam = "&offset=".$array["offset"];
}
else
{
	$completed=1;
}

foreach ($array as $key=>$arr)
{

		
	foreach ($arr as $key2=>$arr2)
	{
		
		$airtableID = (isset($arr2["id"]) ? trim($arr2["id"]) : "");
		$adID = (isset($arr2["fields"]["Ad ID"]) ? trim($arr2["fields"]["Ad ID"]) : "0");
		
		$sqlVal = "'$airtableID','$adID'";
		$headerVal = "_airtableID,_adID";
		$dupeVal = "";
		//echo $arr2["fields"]["Ad ID"]."<BR>";
		foreach ($fields as $dbcol=>$airtableCol)
		{
			//echo "Checking $airtableCol<BR>";
			if (isset($isImage[$airtableCol]))
			{
				 $imageArr = (isset($arr2["fields"][$airtableCol]) ? $arr2["fields"][$airtableCol] : "");
				 $val =  $imageArr[0]["url"];
				 if (strlen($val)>0)
				 {
				  $filetype =  $imageArr[0]["type"];
				  $filename = $imageArr[0]["filename"];

				  $val = backupFile($val,$filetype,$filename);
				 }		
			}
			else
			{
				//echo "Checking $airtableCol<BR>";
				//echo "What is ".$isArray[trim($airtableCol)]."<BR>";
					if (isset($isArray[$airtableCol]))
					{
						$val = implode(",",$arr2["fields"][$airtableCol]);
					////	echo "This is an Array!<BR>";
						//var_dump($arr2["fields"][$airtableCol]);
//						
					}
					else
					{
						$val = (isset($arr2["fields"][$airtableCol]) ? trim($arr2["fields"][$airtableCol]) : "");
					}
				
			}
			
			$sqlVal = (strlen($sqlVal)>0 ? $sqlVal.",'".$objConn->real_escape_string($val)."'" : "'".$objConn->real_escape_string($val)."'");
			$headerVal = (strlen($headerVal)>0 ? $headerVal.",".$dbcol : $dbcol); 
			$dupeVal = ( strlen($dupeVal)>0 ? $dupeVal.",".$dbcol." = VALUES(".$dbcol.")" : $dbcol." = VALUES(".$dbcol.")" );
		}
	
	
	$strSQL = "INSERT INTO $tableName ($headerVal) VALUES ($sqlVal) ON DUPLICATE KEY UPDATE $dupeVal";
	//echo $strSQL."<BR>";
	$objConn->query($strSQL);
	
	}
	//var_dump($arr);
}

	
}

}



function loadAirtable2($url,$token,$tableName,$fields,$isImage=array(),$headerSQL,$headerAirtable)
{
	
global $objConn;
global $isImage;
global $isArray;
$completed = 0;
$totalUpdated = 0;
$offsetParam = "";
$ct = 0;
while ($completed==0)
{
$ct++;
if ($ct>1000)
{
	break;
}

$loadURL = $url.$offsetParam;

echo $loadURL."<BR>";
$response = queryAirtable($loadURL,$token);
//echo $response;
$array = json_decode($response,true);

//var_dump($array);

if (isset($array["offset"]))
{
	$offsetParam = "&offset=".$array["offset"];
}
else
{
	$completed=1;
}

foreach ($array as $key=>$arr)
{

		
	foreach ($arr as $key2=>$arr2)
	{
		
		$airtableID = (isset($arr2["id"]) ? trim($arr2["id"]) : "");

		/* z modified
		$adID = (isset($arr2["fields"]["Ad ID"]) ? trim($arr2["fields"]["Ad ID"]) : "0");
		
		$sqlVal = "'$airtableID','$adID'";
		$headerVal = "_airtableID,_adID";
		*/
		$headerVal2 = (isset($arr2["fields"][$headerAirtable]) ? trim($arr2["fields"][$headerAirtable]) : "0");
		$sqlVal = "'$airtableID','$headerVal2'";
		$headerVal = "_airtableID,$headerSQL";
		$dupeVal = "";
		//echo $arr2["fields"]["Ad ID"]."<BR>";
		foreach ($fields as $dbcol=>$airtableCol)
		{
			//echo "Checking $airtableCol<BR>";
			if (isset($isImage[$airtableCol]))
			{
				 $imageArr = (isset($arr2["fields"][$airtableCol]) ? $arr2["fields"][$airtableCol] : "");
				 $val =  $imageArr[0]["url"];
				 if (strlen($val)>0)
				 {
				  $filetype =  $imageArr[0]["type"];
				  $filename = $imageArr[0]["filename"];

				  $val = backupFile($val,$filetype,$filename);
				 }		
			}
			else
			{
				//echo "Checking $airtableCol<BR>";
				//echo "What is ".$isArray[trim($airtableCol)]."<BR>";
					if (isset($isArray[$airtableCol]))
					{
						$val = implode(",",$arr2["fields"][$airtableCol]);
					////	echo "This is an Array!<BR>";
						//var_dump($arr2["fields"][$airtableCol]);
//						
					}
					else
					{
						$val = (isset($arr2["fields"][$airtableCol]) ? trim($arr2["fields"][$airtableCol]) : "");
					}
				
			}
			
			$sqlVal = (strlen($sqlVal)>0 ? $sqlVal.",'".$objConn->real_escape_string($val)."'" : "'".$objConn->real_escape_string($val)."'");
			$headerVal = (strlen($headerVal)>0 ? $headerVal.",".$dbcol : $dbcol); 
			$dupeVal = ( strlen($dupeVal)>0 ? $dupeVal.",".$dbcol." = VALUES(".$dbcol.")" : $dbcol." = VALUES(".$dbcol.")" );
		}
	
	
	$strSQL = "INSERT INTO $tableName ($headerVal) VALUES ($sqlVal) ON DUPLICATE KEY UPDATE $dupeVal";
	//echo $strSQL."<BR>";
	$objConn->query($strSQL);
	
	}
	//var_dump($arr);
}

	
}

}


?>