[insert_php]echo strtoupper($prodName);[/insert_php]
Shortlister is a vendor matching technology used by several Fortune 1000 companies and most employee benefits consultants.
$m = date(‘n’);
if ($m >= 1 && $m < 4){ $qMo = "January"; $q = 1; } else if ($m >= 4 && $m < 7){ $q = 2; $qMo = "April"; } else if ($m >= 7 && $m < 10){ $q = 3; $qMo = "July"; } else{ $q = 4; $qMo = "October"; } $qtr = $q."-".date("Y"); $qDate = $qMo." ".date("Y"); $t20Sql = " SELECT vt.*,v.name, prods.public_menu_title FROM vendor v JOIN vendor_top20_historic vt ON v.id = vt.vendor_id JOIN (SELECT id, public_menu_title FROM products WHERE id = $pid UNION SELECT id, public_menu_title FROM sub_products WHERE id = $pid) as prods on vt.product_id = prods.id WHERE quarter = '$qtr' AND final = 1 AND status = 1 AND product_id = $pid"; $t20Results = $conn2->query($t20Sql);
$t20Ids = “0”;
if ($t20Results->num_rows > 0){
$t20Rows = mysqli_fetch_all($t20Results,MYSQLI_ASSOC);
$t20Arr = array();
foreach ($t20Rows as $t20){
$t20Arr[(string)$t20[‘vendor_id’]] = $t20;
$t20Ids .= (string)$t20[‘vendor_id’].”,”;
}
$t20Ids = trim ($t20Ids, “,”);
$numPerCol = ceil(count($t20Arr) / 2);
}
echo ‘
echo ‘‘;
if (count($t20Arr) > 0){
echo ‘
Top ‘.count($t20Arr).’ ‘.$prodName.’ – Q’.$q.’ as of ‘.$qDate.’
‘;
}
[/insert_php]
Get Important Updates On These Vendors Monthly
[insert_php]
$orderArr = array();
$top20LinkArr = array();
$noBudgetArr = array();
if ($ppResults){
foreach ($ppResults as $row){
$vendorSql = “SELECT vp.prod_co_desc
FROM vendor_survey_ab vs
JOIN vendor_survey_product_ab vp
ON vs.id = vp.vendor_survey_ab_id AND vp.product_id = “.$pid.”
WHERE vs.vendor_id = “.$row[‘id’].”
UNION
SELECT vp.prod_co_desc
FROM vendor_survey_ab vs
JOIN vendor_survey_sub_product_ab vp
ON vs.id = vp.vendor_survey_ab_id AND vp.sub_product_id = “.$pid.”
WHERE vs.vendor_id = “.$row[‘id’];
$prodDesc = $conn2->query($vendorSql);
$desc = $prodDesc->fetch_assoc();
if ($desc[‘prod_co_desc’]){
if(strlen($desc[‘prod_co_desc’]) > 415){
$summary = substr($desc[‘prod_co_desc’], 0, 375).’…’;
}
else{
$summary = $desc[‘prod_co_desc’];
}
}
else{
if(strlen($row[‘summary’]) > 415){
$summary = substr($row[‘summary’], 0, 375).’…’;
}
else{
$summary = $row[‘summary’];
}
}
if ($row[‘budget_remaining’] > 0){
$budgetPPCSql = “
SELECT bid
FROM vendor_ppc ppc
JOIN (SELECT id, public_slug FROM products UNION SELECT id, public_slug FROM sub_products) p
ON p.id = ppc.product_id
AND p.public_slug LIKE ‘”.$page.”‘
WHERE ppc.vendor_id = “.$row[‘id’].”
AND ppc.is_active = 1″;
$budgetPPCResults = $conn2->query($budgetPPCSql);
$minBidAmount = 0;
if ($budgetPPCResults->num_rows > 0){
$ppcRows = mysqli_fetch_all($budgetPPCResults,MYSQLI_ASSOC);
$minBidAmount = $ppcRows[0][‘bid’];
}
$budgetPPLSql = “
SELECT bid
FROM vendor_ppl ppl
JOIN (SELECT id, public_slug FROM products UNION SELECT id, public_slug FROM sub_products) p
ON p.id = ppl.product_id
AND p.public_slug LIKE ‘”.$page.”‘
WHERE ppl.vendor_id = “.$row[‘id’].”
AND ppl.is_active = 1″;
$budgetPPLResults = $conn2->query($budgetPPLSql);
$minBidAmount2 = 0;
if ($budgetPPLResults->num_rows > 0){
$pplRows = mysqli_fetch_all($budgetPPLResults,MYSQLI_ASSOC);
$minBidAmount2 = $pplRows[0][‘bid’];
}
if ($minBidAmount > 0 && $minBidAmount2 > 0){
$minBidAmount = MIN(array($minBidAmount, $minBidAmount2));
}
else if ($minBidAmount2){
$minBidAmount = $minBidAmount2;
}
$orderArr[] = (string)$row[‘id’];
if ($row[‘budget_remaining’] > $minBidAmount){
echo ‘
if ($showLeads){
echo ‘
‘;
echo ‘
‘;
$top20LinkArr[$row[‘name’]] = ‘‘.$row[‘name’].’‘;
}
else{
echo ‘
‘;
echo ‘
‘;
$top20LinkArr[$row[‘name’]] = ‘‘.$row[‘name’].’‘;
}
echo ‘
‘;
if ( $showLeads){
echo ‘‘.$row[‘name’].’‘;
}
else{
echo ‘‘.$row[‘name’].’‘;
}
echo ‘
- ‘;
-
‘.$score.’/5 (‘.$numRatings.’ reviews)
$score = $row[‘rating’];
$numRatings = $row[‘num_reviews’];
if ($score >= 0 && $score < .5){ echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= .5 && $score < 1){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 1 && $score < 1.5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 1.5 && $score < 2){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 2 && $score < 2.5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 2.5 && $score < 3){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 3 && $score < 3.5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 3.5 && $score < 4){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 4 && $score < 4.5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 4.5 && $score < 5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score == 5){
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
echo ‘
‘;
if ($showLeads){
echo ‘
‘;
}
else{
echo ‘
‘;
}
echo ‘
‘;
}
else{
$noBudgetArr[] = $row[‘id’];
}
}
else{
$noBudgetArr[] = $row[‘id’];
}
}
}
$vidArr = array_merge($pplArr,$pplArr2);
if ($noBudgetArr && $vidArr){
foreach ($noBudgetArr as $v){
if (($key = array_search($v, $vidArr)) !== false) {
unset($vidArr[$key]);
}
}
foreach ($noBudgetArr as $v){
if (($key = array_search($v, $vidArr)) !== false) {
unset($vidArr[$key]);
}
}
}
if ($vidArr){
$ppStr = implode(‘,’, $vidArr);
}
else{
$ppStr = “0”;
}
[/insert_php]
[insert_php]
$sql = “SELECT DISTINCT a.*, (prod_points + pref_points + mav_points + link_points + vid_points + snap_points + doc_points + mo3_points + mo1_points) as points
FROM(
SELECT vd.*,
CASE WHEN $prodText = “.$pid.” AND vp.is_primary = 1 THEN 7 ELSE 0 END as prod_points,
CASE WHEN vd.preferred = 1 THEN 4 ELSE 0 END as pref_points,
CASE WHEN vd.MAV = 1 THEN 3 ELSE 0 END as mav_points,
CASE WHEN vd.linkback = 1 THEN 3 ELSE 0 END as link_points,
CASE WHEN (vd.media1_link != ” AND vd.media1_link != ‘NULL’) OR (vd.media2_link != ” AND vd.media2_link != ‘NULL’) OR (vd.media3_link != ” AND vd.media3_link != ‘NULL’) THEN 1 ELSE 0 END as vid_points,
CASE WHEN (vd.snapshot1_id != ” AND vd.snapshot1_id != ‘NULL’) OR (vd.snapshot2_id != ” AND vd.snapshot2_id != ‘NULL’) OR (vd.snapshot3_id != ” AND vd.snapshot3_id != ‘NULL’) THEN 1 ELSE 0 END as snap_points,
CASE WHEN vd.has_docs > 0 THEN 1 ELSE 0 END as doc_points,
CASE WHEN vd.last_updated > now()-interval 3 month THEN 1 ELSE 0 END as mo3_points,
CASE WHEN vd.last_updated > now()-interval 1 month THEN 1 ELSE 0 END as mo1_points
FROM vendor_data_ab vd
JOIN vendor_products vp
ON vd.vendor_id = vp.vendor_id AND $prodText = “.$pid.”
WHERE (is_active = 1 || (vd.website IS NOT NULL AND vd.website != ”))
AND is_public <> 1
AND
(
(
vd.vendor_id IN (
SELECT MIN(B.vendor_id) as vendor_id FROM vendor_data_ab B
WHERE B.website = vd.website AND (B.is_active = 1 || (B.website IS NOT NULL AND B.website != ”))
)
)
OR
vd.vendor_id in ($t20Ids)
)
AND vd.vendor_id NOT IN ($ppStr)
)
a
ORDER BY points DESC, name ASC”;
$result = $conn->query($sql);
if ($result){
while($row = $result->fetch_assoc()){
$vendorSql = “SELECT vp.prod_co_desc
FROM vendor_survey_ab vs
JOIN vendor_survey_product_ab vp
ON vs.id = vp.vendor_survey_ab_id AND vp.product_id = “.$pid.”
WHERE vs.vendor_id = “.$row[‘vendor_id’].”
UNION
SELECT vp.prod_co_desc
FROM vendor_survey_ab vs
JOIN vendor_survey_sub_product_ab vp
ON vs.id = vp.vendor_survey_ab_id AND vp.sub_product_id = “.$pid.”
WHERE vs.vendor_id = “.$row[‘vendor_id’];
$prodDesc = $conn2->query($vendorSql);
$desc = $prodDesc->fetch_assoc();
if ($desc[‘prod_co_desc’]){
if(strlen($desc[‘prod_co_desc’]) > 415){
$summary = substr($desc[‘prod_co_desc’], 0, 415).’…’;
}
else{
$summary = $desc[‘prod_co_desc’];
}
}
else{
if(strlen($row[‘summary’]) > 415){
$summary = substr($row[‘summary’], 0, 415).’…’;
}
else{
$summary = $row[‘summary’];
}
}
$orderArr[] = (string)$row[‘vendor_id’];
$overbudget = “”;
if (in_array($row[‘vendor_id’], $noBudgetArr)){
$overbudget = ‘overbudget’;
}
$top20LinkArr[$row[‘name’]] = ‘‘.$row[‘name’].’‘;
echo ‘
if ($showLeads) {
echo ‘
echo ‘ ‘;
} else {
echo ‘
echo ‘ ‘;
}
echo ‘
if ($showLeads) {
echo ‘
‘.$row[‘name’].’
‘;} else{
echo ‘
‘.$row[‘name’].’
‘;}
echo ‘
- ‘;
-
‘.$score.’/5 (‘.$numRatings.’ reviews)
$score = $row[‘rating’];
$numRatings = $row[‘num_reviews’];
if ($score >= 0 && $score < .5){ echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= .5 && $score < 1){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 1 && $score < 1.5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 1.5 && $score < 2){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 2 && $score < 2.5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 2.5 && $score < 3){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 3 && $score < 3.5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 3.5 && $score < 4){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 4 && $score < 4.5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score >= 4.5 && $score < 5){
echo '
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
else if ($score == 5){
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
echo ‘
‘;
}
echo ‘
‘;
}
}
[/insert_php]
Get Important Updates On These Vendors Monthly