[insert_php]
$protocol = (!empty($_SERVER[‘HTTPS’]) && $_SERVER[‘HTTPS’] !== ‘off’ || $_SERVER[‘SERVER_PORT’] == 443) ? “https://” : “http://”;
$domainName = $_SERVER[‘HTTP_HOST’];
date_default_timezone_set(‘US/Central’);
if(!empty($_SERVER[‘HTTP_CLIENT_IP’])){
//ip from share internet
$ip = $_SERVER[‘HTTP_CLIENT_IP’];
}elseif(!empty($_SERVER[‘HTTP_X_FORWARDED_FOR’])){
//ip pass from proxy
$ip = $_SERVER[‘HTTP_X_FORWARDED_FOR’];
}else{
$ip = $_SERVER[‘REMOTE_ADDR’];
}
$hubId = $_COOKIE[‘hubspotutk’];
if (!hubId){
$hubId = “xyz”;
}
$hubJson = json_decode(file_get_contents(‘https://api.hubapi.com/contacts/v1/contact/utk/’.$hubId.’/profile?hapikey=5f3bb9c9-86e9-4bf5-999c-d6625e63f9fa’));
$showLeads = $hubJson->{‘is-contact’};
$urlArr = explode(‘/’, $_SERVER[‘REQUEST_URI’]);
$page = $urlArr[count($urlArr) – 2];
$serverRef = (strpos($_SERVER[‘HTTP_REFERER’], “combined”) !== false) ? “” : $_SERVER[‘HTTP_REFERER’];
$ref = “|***|”.$urlArr[count($urlArr) – 2].”||”.$serverRef;
$connArr = explode(‘:’, DB_HOST);
$conn = new mysqli($connArr[0], DB_USER, DB_PASSWORD, DB_NAME);
mysqli_set_charset($conn,”utf8″);
$connArr2 = explode(‘:’, DB_HOST2);
$conn2 = new mysqli($connArr2[0], DB_USER2, DB_PASSWORD2, DB_NAME2);
mysqli_set_charset($conn2,”utf8″);
$now = strtotime(date(‘Y-m-d H:00:00’));
$fourAm = strtotime(‘today 4am’);
if ($now < $fourAm) {
$dateStart = date('Y-m-d H:00:00', strtotime('yesterday 4am'));
$dateEnd = date('Y-m-d H:00:00', $fourAm);
} else {
$dateStart = date('Y-m-d H:00:00', $fourAm);
$dateEnd = date('Y-m-d H:00:00', strtotime('tomorrow 4am'));
}
$pplArr = array();
$pplArr2 = array();
if ($showLeads){
$rotatorSql = "
SELECT DISTINCT v.*, CONCAT(i.sha1, '.', i.extension) as logo, ppl.bid, 1 as is_ppl,
(SELECT count(*) FROM vendor_material WHERE vendor_id = v.id) as has_docs,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot1_id) AS snapshot1_id,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot2_id) AS snapshot2_id,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot3_id) AS snapshot3_id,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN v.max_per_day_budget - (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
)
ELSE 0
END AS budget_remaining,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN 1
ELSE 0
END AS has_budget
FROM vendor_ppl ppl
JOIN products p
ON p.id = ppl.product_id
AND p.public_slug LIKE '".$page."'
AND ppl.is_active = 1
JOIN vendor v
ON v.id = ppl.vendor_id
JOIN image i
ON i.id = v.logo_id
UNION
SELECT DISTINCT v.*, CONCAT(i.sha1, '.', i.extension) as logo, ppl.bid, 1 as is_ppl,
(SELECT count(*) FROM vendor_material WHERE vendor_id = v.id) as has_docs,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot1_id) AS snapshot1_id,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot2_id) AS snapshot2_id,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot3_id) AS snapshot3_id,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN v.max_per_day_budget - (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
)
ELSE 0
END AS budget_remaining,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN 1
ELSE 0
END AS has_budget
FROM vendor_ppl ppl
JOIN sub_products p
ON p.id = ppl.product_id
AND p.public_slug LIKE '".$page."'
AND ppl.is_active = 1
JOIN vendor v
ON v.id = ppl.vendor_id
JOIN image i
ON i.id = v.logo_id
ORDER BY has_budget DESC, bid DESC";
$rotatorResults = $conn2->query($rotatorSql);
if ($rotatorResults->num_rows > 0){
$r1 = mysqli_fetch_all($rotatorResults,MYSQLI_ASSOC);
foreach ($r1 as $tr1){
if (!in_array($tr1[‘id’], $pplArr)){
$pplArr[] = $tr1[‘id’];
}
}
$pplStr = implode(‘,’, $pplArr);
}
if (!$pplStr){
$pplStr = “0”;
}
$rotatorSql = “
SELECT DISTINCT v.*, CONCAT(i.sha1, ‘.’, i.extension) as logo, ppc.bid, 0 as is_ppl,
(SELECT count(*) FROM vendor_material WHERE vendor_id = v.id) as has_docs,
(SELECT concat(sha1, ‘.’, extension) FROM image WHERE id = v.snapshot1_id) AS snapshot1_id,
(SELECT concat(sha1, ‘.’, extension) FROM image WHERE id = v.snapshot2_id) AS snapshot2_id,
(SELECT concat(sha1, ‘.’, extension) FROM image WHERE id = v.snapshot3_id) AS snapshot3_id,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN v.max_per_day_budget - (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
)
ELSE 0
END AS budget_remaining,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN 1
ELSE 0
END AS has_budget
FROM vendor_ppc ppc
JOIN products p
ON p.id = ppc.product_id
AND p.public_slug LIKE '".$page."'
AND ppc.is_active = 1
JOIN vendor v
ON v.id = ppc.vendor_id
JOIN image i
ON i.id = v.logo_id
WHERE ppc.vendor_id NOT IN ($pplStr)
UNION
SELECT DISTINCT v.*, CONCAT(i.sha1, '.', i.extension) as logo, ppc.bid, 0 as is_ppl,
(SELECT count(*) FROM vendor_material WHERE vendor_id = v.id) as has_docs,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot1_id) AS snapshot1_id,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot2_id) AS snapshot2_id,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot3_id) AS snapshot3_id,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN v.max_per_day_budget - (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
)
ELSE 0
END AS budget_remaining,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN 1
ELSE 0
END AS has_budget
FROM vendor_ppc ppc
JOIN sub_products p
ON p.id = ppc.product_id
AND p.public_slug LIKE '".$page."'
AND ppc.is_active = 1
JOIN vendor v
ON v.id = ppc.vendor_id
JOIN image i
ON i.id = v.logo_id
WHERE ppc.vendor_id NOT IN ($pplStr)
ORDER BY has_budget DESC, bid DESC";
$rotatorResults2 = $conn2->query($rotatorSql);
if ($rotatorResults2->num_rows > 0){
$r2 = mysqli_fetch_all($rotatorResults2,MYSQLI_ASSOC);
foreach ($r2 as $tr2){
if (!in_array($tr2[‘id’], $pplArr2) && !in_array($tr2[‘id’], $pplArr)){
$pplArr2[] = $tr2[‘id’];
}
}
$pplStr2 = implode(‘,’, $pplArr2);
}
if ($pplStr2){
$pplStr = $pplStr.”,”.$pplStr2;
}
if ($r1 && $r2){
$ppResults = array_merge($r1, $r2);
array_multisort(array_column($ppResults, ‘has_budget’), SORT_DESC,
array_column($ppResults, ‘bid’), SORT_DESC,
$ppResults);
}
else if ($r1){
$ppResults = $r1;
}
else if ($r2){
$ppResults = $r2;
}
}
else{
$rotatorSql = “
SELECT DISTINCT v.*, CONCAT(i.sha1, ‘.’, i.extension) as logo, ppc.bid, 0 as is_ppl,
(SELECT count(*) FROM vendor_material WHERE vendor_id = v.id) as has_docs,
(SELECT concat(sha1, ‘.’, extension) FROM image WHERE id = v.snapshot1_id) AS snapshot1_id,
(SELECT concat(sha1, ‘.’, extension) FROM image WHERE id = v.snapshot2_id) AS snapshot2_id,
(SELECT concat(sha1, ‘.’, extension) FROM image WHERE id = v.snapshot3_id) AS snapshot3_id,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN v.max_per_day_budget - (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
)
ELSE 0
END AS budget_remaining,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN 1
ELSE 0
END AS has_budget
FROM vendor_ppc ppc
JOIN products p
ON p.id = ppc.product_id
AND p.public_slug LIKE '".$page."'
AND ppc.is_active = 1
JOIN vendor v
ON v.id = ppc.vendor_id
JOIN image i
ON i.id = v.logo_id
UNION
SELECT DISTINCT v.*, CONCAT(i.sha1, '.', i.extension) as logo, ppc.bid, 0 as is_ppl,
(SELECT count(*) FROM vendor_material WHERE vendor_id = v.id) as has_docs,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot1_id) AS snapshot1_id,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot2_id) AS snapshot2_id,
(SELECT concat(sha1, '.', extension) FROM image WHERE id = v.snapshot3_id) AS snapshot3_id,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN v.max_per_day_budget - (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
)
ELSE 0
END AS budget_remaining,
CASE WHEN (
SELECT IFNULL(SUM(price), 0) as price
FROM(
SELECT IFNULL(SUM(ll.price), 0) as price, ll.vendor_id
FROM ppl_log ll
where ll.created_dt >= ‘”.$dateStart.”‘
AND ll.created_dt <= '".$dateEnd."'
GROUP BY ll.vendor_id
UNION
SELECT SUM(IFNULL(cl.price, 0)) as price, cl.vendor_id
FROM ppc_log cl
WHERE cl.created_dt >= ‘”.$dateStart.”‘
AND cl.created_dt <= '".$dateEnd."'
GROUP BY cl.vendor_id
) a
WHERE v.id = a.vendor_id
) < v.max_per_day_budget
THEN 1
ELSE 0
END AS has_budget
FROM vendor_ppc ppc
JOIN sub_products p
ON p.id = ppc.product_id
AND p.public_slug LIKE '".$page."'
AND ppc.is_active = 1
JOIN vendor v
ON v.id = ppc.vendor_id
JOIN image i
ON i.id = v.logo_id
ORDER BY has_budget DESC, bid DESC";
$rotatorResults = $conn2->query($rotatorSql);
if ($rotatorResults->num_rows > 0){
$ppResults = mysqli_fetch_all($rotatorResults,MYSQLI_ASSOC);
foreach ($ppResults as $tr1){
if (!in_array($tr1[‘id’], $pplArr)){
$pplArr[] = $tr1[‘id’];
}
}
$pplStr = implode(‘,’, $pplArr);
}
if (!$pplStr){
$pplStr = “0”;
}
}
$prodSql = “SELECT *
FROM products
WHERE slug = ‘$page'”;
$prodResult = $conn->query($prodSql);
if ($prodRow = $prodResult->fetch_assoc()){
$spid = $prodRow[‘sid’];
$pid = $prodRow[‘pid’];
$prodName = $prodRow[‘menu_title’];
$prodName2 = $prodRow[‘menu_title2’];
$prodText = “vp.product_id”;
}
if ($spid){
$pid = $spid;
$prodText = “vp.sub_product_id”;
}
[/insert_php]