Symfony Profiler

<?php
namespace App\Utils;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\Security\Core\Authentication\Token\Storage\TokenStorageInterface;
use App\Exception\ResourceNotFoundException;
use Symfony\Component\HttpKernel\Exception\NotFoundHttpException;
use App\Entity\Content;
use App\Entity\Category;
use App\Entity\Customer;
use App\Entity\User;
use App\Entity\Site;
use Doctrine\Common\Collections\Criteria;
use \Datetime;
use \DateTimeZone;
use App\Utils\ICS;
use App\Utils\EmailHelper;
use Symfony\Component\Security\Core\Security;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\Cache\Adapter\FilesystemAdapter;
use App\Entity\Traffic;
use App\Entity\TrafficData;
use App\Entity\ContentImpression;
use App\Entity\ContentView;
use App\Entity\AdImpression;
use App\Entity\AdClick;
use App\Entity\ContentLinkClick;
use App\Entity\Product;
use App\Entity\ContentAnalyticsDaily;
class ContentHelper
{
const DEFAULT_LIMIT = 15;
const FORUM_LIMIT = 15;
protected $entityManager;
protected $emailHelper;
protected $security;
protected $requestStack;
protected $tokenStorage;
protected $categoryHelper;
public function __construct (
EntityManagerInterface $entityManager,
EmailHelper $emailHelper,
Security $security,
RequestStack $requestStack,
TokenStorageInterface $tokenStorage,
CategoryHelper $categoryHelper
) {
$this->entityManager = $entityManager;
$this->emailHelper = $emailHelper;
$this->security = $security;
$this->requestStack = $requestStack;
$this->tokenStorage = $tokenStorage;
$this->categoryHelper = $categoryHelper;
}
public function getSiteCode() {
$site_code = null;
$request = $this->requestStack->getCurrentRequest();
if($request) {
$session = $request->getSession();
if($session) {
$site_code = $session->get("site");
}
}
return $site_code;
}
public function slugIsUnique ($slug = "", $content = "", $site = "")
{
return $this->entityManager
->getRepository(Content::class)
->slugIsUnique($slug, $content, $site);
}
/*
* takes a page title or a slug and returns a unique slug
*/
public function getUniqueSlug ($title = "", $content = "", $site = "")
{
return $this->entityManager
->getRepository(Content::class)
->getUniqueSlug($title, $content, $site);
}
/*public function getStaticPageBySlug ($slug = "")
{
try{
return $this->getContentBySlug(Content::STATIC_PAGE, $slug);
} catch(\Exception $e) {
return null;
}
}*/
public function getStaticPageBySlug ($slug = "") {
$type = 0;
$content="";
$site_code = $this->getSiteCode();
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from("App\Entity\Content", "c")
->where("c.slug = :slug")
->andWhere("c.type = :type")
->andWhere("c.status = 1"); //allow 'customer in review' to be linkable
if($site_code) {
$qb->join("c.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code);
}
$content = $qb
->setParameter("slug", $slug)
->setParameter("type", $type)
->setMaxResults(1)->getQuery()->getResult();
if($content) {
$content = $content[0];
return $content;
}
return null;
}
public function getPageBySlug ($slug = "")
{
//Adding this try catch temporarily to deal with social posts using the old format for posts (/{slug} instead of /post/{slug})
try{
return $this->getContentBySlug(Content::PAGE, $slug);
} catch(\Exception $e) {
}
return $this->getContentBySlug(Content::POST, $slug);
}
public function getPostBySlug ($slug = "")
{
return $this->getContentBySlug(Content::POST, $slug);
}
public function getClassifiedBySlug ($slug = "")
{
return $this->getContentBySlug(Content::CLASSIFIED, $slug);
}
public function getDirectoryBySlug ($slug = "")
{
return $this->getContentBySlug(Content::DIRECTORY, $slug);
}
public function getEventBySlug ($slug = "")
{
//Exception for webinars
try{
return $this->getContentBySlug(Content::EVENT, $slug);
} catch(\Exception $e) {
}
return $this->getContentBySlug(Content::WEBINAR, $slug);
//return $this->getContentBySlug(Content::EVENT, $slug);
}
public function getForumBySlug ($slug = "")
{
return $this->getContentBySlug(Content::FORUM, $slug);
}
public function getTopicBySlug ($slug = "")
{
return $this->getContentBySlug(Content::FORUM_TOPIC, $slug);
}
public function getLandingPageBySlug ($slug = "") {
return $this->getContentBySlug(Content::LANDING_PAGE, $slug);
}
public function getThankYouPageBySlug ($slug = "") {
return $this->getContentBySlug(Content::THANK_YOU_LANDING_PAGE, $slug);
}
public function getContentBySlug ($type = "", $slug = "") {
$content="";
$user = $this->security->getUser();
$site_code = $this->getSiteCode();
if($user) {
//if the user is an admin, return the content regardless of its status
if($user->isAdmin()) {
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from("App\Entity\Content", "c")
->where("c.slug = :slug")
->andWhere("c.type = :type");
if($site_code && (!in_array($type, [Content::DIRECTORY, Content::CLASSIFIED]))) { //todo: remove these exceptions when purchasing on MCS is finished.
$qb->join("c.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code);
}
$content = $qb
->setParameter("slug", $slug)
->setParameter("type", $type)
->setMaxResults(1)->getQuery()->getResult();
if($content) {
$content = $content[0];
}
}
}
if(!$content) {
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from("App\Entity\Content", "c")
->where("c.slug = :slug")
->andWhere("c.type = :type")
->andWhere("c.status = 1 OR c.status = -5"); //allow 'customer in review' to be linkable
if($site_code && $type != Content::DIRECTORY) { //todo: update directory content w/ site_code
$qb->join("c.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code);
}
$content = $qb
->setParameter("slug", $slug)
->setParameter("type", $type)
->setMaxResults(1)->getQuery()->getResult();
if($content) {
$content = $content[0];
}
}
if ($content) {
return $content;
}
throw new NotFoundHttpException("Content Not Found: {$slug}");
/*
//Old way of checking if page is members only. Now, returning the content and handling in the controller.
$content="";
$user = $this->security->getUser();
if($user) {
//if the user is an admin, return the content regardless of its status
if($user->isAdmin()) {
$content = $this->entityManager
->getRepository(Content::class)
->findOneBy([
"slug" => $slug,
"type" => $type,
]);
}
//if the user is a member, return active content
elseif($user->isMember()) {
$content = $this->entityManager
->getRepository(Content::class)
->findOneBy([
"slug" => $slug,
"type" => $type,
"status" => 1,
]);
}
}
//otherwise, only return active, non-members only content
if(!$content) {
$content = $this->entityManager
->getRepository(Content::class)
->findOneBy([
"slug" => $slug,
"type" => $type,
"status" => 1,
"members_only" => 0,
]);
}
if ($content) {
return $content;
}
else {
$m_content = $this->entityManager
->getRepository(Content::class)
->findOneBy([
"slug" => $slug,
"type" => $type,
"status" => 1,
]);
if($m_content) {
//return the "members only" page
}
}
throw new ResourceNotFoundException ("Content Not Found: {$slug}");
*/
}
public function getContentBySlugRaw ($slug = "") {
$site_code = $this->getSiteCode();
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from("App\Entity\Content", "c")
->where("c.slug = :slug");
if($site_code) {
$qb->join("c.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code);
}
$content = $qb
->setParameter("slug", $slug)
->setMaxResults(1)->getQuery()->getResult();
if($content) {
$content = $content[0];
return $content;
}
throw new ResourceNotFoundException ("Content with Slug {$slug} - Not Found");
}
// not site specific (TODO: confirm unique when setting slugs)
public function getDirectoryBySlugRaw($slug = "") {
$qb = $this->entityManager->createQueryBuilder();
$content = $qb->select('c')
->from("App\Entity\Content", "c")
->where("c.slug = :slug")
->andWhere("c.type = :type")
->setParameter("slug", $slug)
->setParameter("type", Content::DIRECTORY)
->setMaxResults(1)->getQuery()->getResult();
if($content) {
$content = $content[0];
return $content;
}
throw new ResourceNotFoundException ("Content with Slug {$slug} - Not Found");
}
public function getContentByType ($type = "") {
$site_code = $this->getSiteCode();
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from("App\Entity\Content", "c")
->where("c.type = :type")
->andWhere("c.status = 1 ");
if($site_code) {
$qb->join("c.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code);
}
$content = $qb
->setParameter("type", $type)
->getQuery()->getResult();
if($content) {
return $content;
}
return [];
}
public function getContentByOldId ($old_id = "") {
$site_code = $this->getSiteCode();
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from("App\Entity\Content", "c")
->where("c.old_id = :old_id");
if($site_code) {
$qb->join("c.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code);
}
$content = $qb
->setParameter("old_id", $old_id)
->setMaxResults(1)->getQuery()->getResult();
if($content) {
$content = $content[0];
return $content;
}
throw new ResourceNotFoundException ("Content Not Found - Old ID: {{$old_id}");
}
public function updateExpired () {
//TODO: Update for AAR (emails etc. if necessary)
$contents = $this->entityManager
->createQuery(implode(" ", array (
"SELECT content",
"FROM",
"App\Entity\Content content",
"WHERE",
"content.expires_at < :now",
"AND content.type != 7",
)))
->setParameter("now", new \DateTime())
->getResult();
$results = $this->entityManager
->createQuery(implode(" ", array (
"UPDATE",
"App\Entity\Content content",
"SET",
"content.status = -2,",
"content.expires_at = null",
"WHERE",
"content.expires_at < :now",
"AND content.type != 7",
)))
->setParameter("now", new \DateTime())
->getResult();
if($contents) {
foreach($contents as $content) {
$type = $content->getType();
//only send the email for expired classified / directory listings
if($type == Content::CLASSIFIED || $type == Content::DIRECTORY) {
$id = $content->getId();
$title = $content->getTitle();
$type_text = $content->getTypeText();
$published = $content->getPublishedAt()->format("Y-m-d H:i:s");
$expired = new \DateTime();
$expired = $expired->format("Y-m-d H:i:s");
$user = $content->getAuthor();
if($user) { //should change to send to admins even if no author is set
$author_name = $user->getDisplayname();
$this->emailHelper->sendEmail(
[$user->getEmail()],
"RoofersCoffeeShop.com: {$type_text} \"{$title}\" Has Expired",
[
["p" => "Dear {$author_name},"],
["p" => "Your {$type_text} \"{$title}\" on <a href='https://rooferscoffeeshop.com/'>RoofersCoffeeShop.com</a> has Expired."],
["p" => "To make changes, cancel or renew your {$type_text} please log in and find Your Account in the drop down menu."],
["p" => "Please contact us at <a href='mailto:info@rooferscoffeshop.com'>info@rooferscoffeshop.com</a> or call us a (877) 419-0477 with any questions"],
],
[],
"",
"",
"",
[],
Content::SITE_RCS
);
}
}
}
}
// Do the same for ads
$ads = $this->entityManager
->createQuery(implode(" ", array (
"SELECT ad",
"FROM",
"App\Entity\MediaGroupItem ad",
"JOIN",
"ad.mediaGroup mgroup",
"WHERE",
"ad.expires_at < :now",
"AND mgroup.type = 2",
)))
->setParameter("now", new \DateTime())
->getResult();
$results = $this->entityManager
->createQuery(implode(" ", array (
"UPDATE",
"App\Entity\MediaGroupItem ad",
"SET",
"ad.status = -2,",
"ad.expires_at = null",
"WHERE",
"ad.expires_at < :now",
)))
->setParameter("now", new \DateTime())
->getResult();
if($ads) {
foreach($ads as $ad) {
$id = $ad->getId();
$title = $ad->getTitle();
$published = $ad->getPublishedAt()->format("Y-m-d H:i:s");
$expired = new \DateTime();
$expired = $expired->format("Y-m-d H:i:s");
$this->emailHelper->sendEmail(
["rcs@rooferscoffeeshop.com"],
"Ad Expired",
[
["p" => "The following ad has expired:"],
["table" => [
"Id" => $id,
"Title" => $title,
"Published Date" => $published,
"Expired Date" => $expired,
]],
],
[],
"",
"",
"",
[],
Content::SITE_RCS
);
}
}
}
//Not site specific
public function updatePublished ()
{
//Select all going live per site
//For RCS:
//Get current content on home page - latest Up to the Minute, RCS Original, RCS influencer (no duplicates)
//After the update query, get the same
//Considered new on home page if just showing up on the home page now but not previously (AND just published? to prevent issues with expiring old ones etc.)
//If new - Grab the customers associated with the contact and send email to each contact from directory
//MCS: MCS Influencers, From the Newsroom
//AAR: Roofing Blog (Add Roofing Blog to top of "Featured")
//Todo: hook up with home page so that it doesn't need changed in both places
//Excluded in dupes
//{{ show_module({'cache_time': '180', 'type': 'content', 'ctype': '2', 'limit': '2', 'dupes': 'yes', 'exclude_in_dupes': 'yes' })}}
$content = $this->entityManager
->createQuery(implode(" ", array (
"SELECT content",
"FROM",
"App\Entity\Content content",
"WHERE",
"content.status = 2",
"AND content.published_at < :now",
)))
->setParameter("now", new \DateTime())
->getResult();
$this->entityManager
->createQuery(implode(" ", array (
"UPDATE",
"App\Entity\Content content",
"SET",
"content.status = 1",
"WHERE",
"content.status = 2",
"AND content.published_at < :now",
)))
->setParameter("now", new \DateTime())
->getResult();
try {
//-----------------RCS Start-----------------
//Careful with dupes from these
//{{ show_module({'type': 'content', 'ctype': '3', 'limit': '3', 'dupes': 'yes'})}}
//{{ show_module({'type': 'content', 'only_announcements': 'yes', 'limit': '1', 'dupes': 'no' }) }}
$announcements = $this->getContent(
"*", //type
"*", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
true, //only_announcements,
false, //excludeInDupes,
Content::SITE_RCS //site_override
);
//Up to the Minute!
//{{ show_module({'type': 'content', 'ctype': '2', 'category': 'newsroom', 'limit': '1', 'dupes': 'no'})}}
$up_to_the_minute = $this->getContent(
Content::POST, //type
"newsroom", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_RCS //site_override
);
//RCS Original
//{{ show_module({'type': 'content', 'ctype': '2', 'category': 'rcs-blog', 'limit': '1', 'dupes': 'no'})}}
$rcs_original = $this->getContent(
Content::POST, //type
"rcs-blog", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_RCS //site_override
);
//RCS Influencers
//{{ show_module({'type': 'content', 'ctype': '2', 'category': 'rcs-influencers', 'limit': '1','dupes': 'no'})}}
$rcs_influencers = $this->getContent(
Content::POST, //type
"rcs-influencers", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_RCS, //site_override
true //clear dupes after (To make sure the next site doesn't limit posts based on dupes of previous site)
);
$rcs_home_page_contents = array_merge($up_to_the_minute["content"], $rcs_original["content"], $rcs_influencers["content"]);
//[content type="18" category="partner-podcast" col_image="12" col_text="12" limit="1" class="featured-single" show_full="no" show_dates="no" show_hub_categories="yes"]
//Latest RCS podcast:
$rcs_partner_podcasts = $this->getContent(
Content::PODCAST, //type
"partner-podcast", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
true, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_RCS, //site_override
true //clear dupes after (To make sure the next site doesn't limit posts based on dupes of previous site)
);
$rcs_partner_podcasts = array_merge($rcs_partner_podcasts["content"]);
/*
$content = $this->getContent(
Content::POST, //type
"*", //category
"", //order
"", //dir
0, //page
// * 3, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_RCS //site_override
);
*/
////-----------------RCS End-----------------
////-----------------MCS Start-----------------
// show_module({'type': 'content', 'ctype': '2', 'category': 'mcs-influencers', 'intro_length': '24', 'intro-length': '5', 'limit': '1', 'class': 'home-articles-big white', 'dupes': 'no'})
$mcs_influencers = $this->getContent(
Content::POST, //type
"mcs-influencers", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_MCS, //site_override
false //clear dupes after (To make sure the next site doesn't limit posts based on dupes of previous site)
);
// show_module({'type': 'content', 'ctype': '2', 'intro_length': '24', 'limit': '1', 'class': 'mcs-home-module', 'title': 'FROM THE NEWSROOM', 'col_image': 12, 'col_text': 12, 'show_dates': 'no', 'visit_text': 'More From The Newsroom', 'visit_link': '/news-room', 'show_readmores': 'no', 'dupes': 'no'})}}
$mcs_newsroom = $this->getContent(
Content::POST, //type
"*", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_MCS, //site_override
true //clear dupes after (To make sure the next site doesn't limit posts based on dupes of previous site)
);
$mcs_home_page_contents = array_merge($mcs_influencers["content"], $mcs_newsroom["content"]);
////-----------------MCS End-----------------
////-----------------CCS Start-----------------
// show_module({'type': 'content', 'ctype': '2', 'category': 'ccs-influencers', 'intro_length': '24', 'intro-length': '5', 'limit': '1', 'class': 'home-articles-big white', 'dupes': 'no'})
$ccs_influencers = $this->getContent(
Content::POST, //type
"ccs-influencers", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_CCS, //site_override
false //clear dupes after (To make sure the next site doesn't limit posts based on dupes of previous site)
);
// show_module({'type': 'content', 'ctype': '2', 'intro_length': '24', 'limit': '1', 'class': 'ccs-home-module', 'title': 'FROM THE NEWSROOM', 'col_image': 12, 'col_text': 12, 'show_dates': 'no', 'visit_text': 'More From The Newsroom', 'visit_link': '/news-room', 'show_readmores': 'no', 'dupes': 'no'})}}
$ccs_newsroom = $this->getContent(
Content::POST, //type
"*", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_CCS, //site_override
true //clear dupes after (To make sure the next site doesn't limit posts based on dupes of previous site)
);
$ccs_home_page_contents = array_merge($ccs_influencers["content"], $ccs_newsroom["content"]);
////-----------------CCS End-----------------
////-----------------AAR Start-----------------
//show_module({'type': 'content', 'ctype': '2', 'show_intros': 'no', 'readmore_text': '', 'limit': '1', 'class': 'home-articles-big', 'dupes': 'no'})
$aar_roofing_blog = $this->getContent(
Content::POST, //type
"*", //category
"", //order
"", //dir
0, //page
1, //limit
false, //allowDupes
"*", //taxonomy
false, //user_secondary_categories
false, //members_only,
false, //$as_gallery,
false, //$only_featured,
"", //customer_id
"", //customer_level_or_higher,
false, //only_announcements,
false, //excludeInDupes,
Content::SITE_AAR, //site_override
true //clear dupes after (To make sure the next site doesn't limit posts based on dupes of previous site)
);
$aar_home_page_contents = array_merge($aar_roofing_blog["content"]);
////-----------------AAR End-----------------
$cids = [];
$cObjs = [];
$cidsString = "";
foreach($content as $c) {
$cidsString .= $c->getId() . " ";
$cids[] = $c->getId();
$cObjs[$c->getId()] = $c;
}
//TODO: consolidate this so there aren't three copies
//RCS
$rcsHcObjs = [];
$rcsHcidsString = "";
foreach($rcs_home_page_contents as $hc) {
$rcsHcidsString .= $hc->getId() . " ";
if(in_array($hc->getId(), $cids)) {
$rcsHcObjs[] = $hc;
}
}
$rcsHcObjsStr = "";
foreach($rcsHcObjs as $home_content) {
$rcsHcObjsStr .= $home_content->getId() . " ";
foreach($home_content->getCustomers() as $hc_cust) {
if($hc_cust->getDirectoryContent()) {
$recipients = [];
$valid_recipients = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryEmailRecipients()) as $recipient) {
if ($recipient && filter_var(trim($recipient), FILTER_VALIDATE_EMAIL)) {
$recipients[] = trim($recipient);
$valid_recipients = true;
}
}
$recipients_text = "none";
if($valid_recipients) {
$recipients_text = implode(", ", $recipients);
}
$account_managers = [];
$valid_account_managers = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryAccountManagers()) as $account_manager) {
if ($account_manager && filter_var(trim($account_manager), FILTER_VALIDATE_EMAIL)) {
$account_managers[] = trim($account_manager);
$valid_account_managers = true;
}
}
$ac_text = "none";
if($valid_account_managers) {
$ac_text = implode(", ", $account_managers);
}
if(!$valid_recipients) {
continue;
}
//send email
$this->emailHelper->sendEmail(
$recipients,
//["egreco@intradatech.com", "apost@intradatech.com"],
"Your article is now live on the RoofersCoffeeShop home page!",
[
["p" => "Hello " . $hc_cust->getTitle() . "!" ],
["p" => "Your article is now live on RCS!"],
["p" => "
<div>
<a href='".$home_content->getFullURL(Content::SITE_RCS)."'>
<img width=300 style='border:0;' src='https://www.rooferscoffeeshop.com/".$home_content->getFeaturedImageURL()."'>
</a>
</div>
<div>
<div>
<a href='".$home_content->getFullURL(Content::SITE_RCS)."' style='color:#043056;font-weight: 700; font-size:16px;text-decoration:none;'>
".$home_content->getTitle()."
</a>
</div>
<i style='color:#26679e;'>".str_replace(["AM", "PM"], ["a.m.", "p.m."], date("F j, Y \\a\\t g:i A", strtotime($home_content->getPublishedAt()->format("Y-m-d H:i:s"))))."</i>
<div class='my-3'>
<span class='sideintrocontent'>".$home_content->getIntroText()."</span>
<a href='".$home_content->getFullURL(Content::SITE_RCS)."' style='color:#26679e;'>
Read More
</a>
</div>
</div>
"],
["p" => "Please share!"],
],
[], //attachments
$account_managers, //cc
"", //bcc
"", //logo
[], //media_attachments
Content::SITE_RCS
);
}
}
}
//RCS podcasts
$rcsPpObjs = [];
$rcsPpidsString = "";
foreach($rcs_partner_podcasts as $pp) {
$rcsPpidsString .= $pp->getId() . " ";
if(in_array($pp->getId(), $cids)) {
$rcsPpObjs[] = $pp;
}
}
$rcsPpObjsStr = "";
foreach($rcsPpObjs as $home_content) {
$rcsPpObjsStr .= $home_content->getId() . " ";
foreach($home_content->getCustomers() as $hc_cust) {
if($hc_cust->getDirectoryContent()) {
$recipients = [];
$valid_recipients = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryEmailRecipients()) as $recipient) {
if ($recipient && filter_var(trim($recipient), FILTER_VALIDATE_EMAIL)) {
$recipients[] = trim($recipient);
$valid_recipients = true;
}
}
$recipients_text = "none";
if($valid_recipients) {
$recipients_text = implode(", ", $recipients);
}
$account_managers = [];
$valid_account_managers = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryAccountManagers()) as $account_manager) {
if ($account_manager && filter_var(trim($account_manager), FILTER_VALIDATE_EMAIL)) {
$account_managers[] = trim($account_manager);
$valid_account_managers = true;
}
}
$ac_text = "none";
if($valid_account_managers) {
$ac_text = implode(", ", $account_managers);
}
if(!$valid_recipients) {
continue;
}
//send email
$this->emailHelper->sendEmail(
$recipients,
//["egreco@intradatech.com", "apost@intradatech.com"],
"Your podcast is now live on the RoofersCoffeeShop partner podcasts page!",
[
["p" => "Hello " . $hc_cust->getTitle() . "!" ],
["p" => "Your podcast is now live on the RCS <a href='https://www.rooferscoffeeshop.com/podcasts/partner-podcast'>Partner Podcasts page</a>!"],
["p" => "
<div>
<a href='".$home_content->getFullURL(Content::SITE_RCS)."'>
<img width=300 style='border:0;' src='https://www.rooferscoffeeshop.com/".$home_content->getFeaturedImageURL()."'>
</a>
</div>
<div>
<div>
<a href='".$home_content->getFullURL(Content::SITE_RCS)."' style='color:#043056;font-weight: 700; font-size:16px;text-decoration:none;'>
".$home_content->getTitle()."
</a>
</div>
<i style='color:#26679e;'>".str_replace(["AM", "PM"], ["a.m.", "p.m."], date("F j, Y \\a\\t g:i A", strtotime($home_content->getPublishedAt()->format("Y-m-d H:i:s"))))."</i>
<div class='my-3'>
<span class='sideintrocontent'>".$home_content->getIntroText()."</span>
<a href='".$home_content->getFullURL(Content::SITE_RCS)."' style='color:#26679e;'>
Read More
</a>
</div>
</div>
"],
["p" => "Please share!"],
],
[], //attachments
$account_managers, //cc
"", //bcc
"", //logo
[], //media_attachments
Content::SITE_RCS
);
}
}
}
//dev info
/*
["p" => "."],
["p" => "."],
["p" => "Dev info:"],
["p" => "Content ids: " . $cidsString],
["p" => "The content ids are on the home page:"],
["p" => $rcsHcidsString],
["p" => "Recipients: " . $recipients_text],
["p" => "Account Managers: " . $ac_text],
["p" => "Customer: #" . $hc_cust->getID() . " " . $hc_cust->getTitle()],
["p" => "Content: #" . $home_content->getID() . " " . $home_content->getTitle()],
*/
//MCS
$mcsHcObjs = [];
$mcsHcidsString = "";
foreach($mcs_home_page_contents as $hc) {
$mcsHcidsString .= $hc->getId() . " ";
if(in_array($hc->getId(), $cids)) {
$mcsHcObjs[] = $hc;
}
}
$mcsHcObjsStr = "";
foreach($mcsHcObjs as $home_content) {
$mcsHcObjsStr .= $home_content->getId() . " ";
foreach($home_content->getCustomers() as $hc_cust) {
if($hc_cust->getDirectoryContent()) {
$recipients = [];
$valid_recipients = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryEmailRecipients()) as $recipient) {
if ($recipient && filter_var(trim($recipient), FILTER_VALIDATE_EMAIL)) {
$recipients[] = trim($recipient);
$valid_recipients = true;
}
}
$recipients_text = "none";
if($valid_recipients) {
$recipients_text = implode(", ", $recipients);
}
$account_managers = [];
$valid_account_managers = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryAccountManagers()) as $account_manager) {
if ($account_manager && filter_var(trim($account_manager), FILTER_VALIDATE_EMAIL)) {
$account_managers[] = trim($account_manager);
$valid_account_managers = true;
}
}
$ac_text = "none";
if($valid_account_managers) {
$ac_text = implode(", ", $account_managers);
}
if(!$valid_recipients) {
continue;
}
//send email
$this->emailHelper->sendEmail(
$recipients,
//["egreco@intradatech.com", "apost@intradatech.com"],
"Your article is now live on the MetalCoffeeShop home page!",
[
["p" => "Hello " . $hc_cust->getTitle() . "!" ],
["p" => "Your article is now live on MCS!"],
["p" => "
<div>
<a href='".$home_content->getFullURL(Content::SITE_MCS)."'>
<img width=300 style='border:0;' src='https://www.metalcoffeeshop.com/".$home_content->getFeaturedImageURL()."'>
</a>
</div>
<div>
<div>
<a href='".$home_content->getFullURL(Content::SITE_MCS)."' style='color:#043056;font-weight: 700; font-size:16px;text-decoration:none;'>
".$home_content->getTitle()."
</a>
</div>
<i style='color:#26679e;'>".str_replace(["AM", "PM"], ["a.m.", "p.m."], date("F j, Y \\a\\t g:i A", strtotime($home_content->getPublishedAt()->format("Y-m-d H:i:s"))))."</i>
<div class='my-3'>
<span class='sideintrocontent'>".$home_content->getIntroText()."</span>
<a href='".$home_content->getFullURL(Content::SITE_MCS)."' style='color:#26679e;'>
Read More
</a>
</div>
</div>
"],
["p" => "Please share!"],
],
[], //attachments
$account_managers, //cc
"", //bcc
"", //logo
[], //media_attachments
Content::SITE_MCS
);
}
}
}
//CCS
$ccsHcObjs = [];
$ccsHcidsString = "";
foreach($ccs_home_page_contents as $hc) {
$ccsHcidsString .= $hc->getId() . " ";
if(in_array($hc->getId(), $cids)) {
$ccsHcObjs[] = $hc;
}
}
$ccsHcObjsStr = "";
foreach($ccsHcObjs as $home_content) {
$ccsHcObjsStr .= $home_content->getId() . " ";
foreach($home_content->getCustomers() as $hc_cust) {
if($hc_cust->getDirectoryContent()) {
$recipients = [];
$valid_recipients = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryEmailRecipients()) as $recipient) {
if ($recipient && filter_var(trim($recipient), FILTER_VALIDATE_EMAIL)) {
$recipients[] = trim($recipient);
$valid_recipients = true;
}
}
$recipients_text = "none";
if($valid_recipients) {
$recipients_text = implode(", ", $recipients);
}
$account_managers = [];
$valid_account_managers = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryAccountManagers()) as $account_manager) {
if ($account_manager && filter_var(trim($account_manager), FILTER_VALIDATE_EMAIL)) {
$account_managers[] = trim($account_manager);
$valid_account_managers = true;
}
}
$ac_text = "none";
if($valid_account_managers) {
$ac_text = implode(", ", $account_managers);
}
if(!$valid_recipients) {
continue;
}
//send email
$this->emailHelper->sendEmail(
$recipients,
//["egreco@intradatech.com", "apost@intradatech.com"],
"Your article is now live on the CoatingsCoffeeShop home page!",
[
["p" => "Hello " . $hc_cust->getTitle() . "!" ],
["p" => "Your article is now live on CCS!"],
["p" => "
<div>
<a href='".$home_content->getFullURL(Content::SITE_CCS)."'>
<img width=300 style='border:0;' src='https://www.coatingscoffeeshop.com/".$home_content->getFeaturedImageURL()."'>
</a>
</div>
<div>
<div>
<a href='".$home_content->getFullURL(Content::SITE_CCS)."' style='color:#043056;font-weight: 700; font-size:16px;text-decoration:none;'>
".$home_content->getTitle()."
</a>
</div>
<i style='color:#26679e;'>".str_replace(["AM", "PM"], ["a.m.", "p.m."], date("F j, Y \\a\\t g:i A", strtotime($home_content->getPublishedAt()->format("Y-m-d H:i:s"))))."</i>
<div class='my-3'>
<span class='sideintrocontent'>".$home_content->getIntroText()."</span>
<a href='".$home_content->getFullURL(Content::SITE_CCS)."' style='color:#26679e;'>
Read More
</a>
</div>
</div>
"],
["p" => "Please share!"],
],
[], //attachments
$account_managers, //cc
"", //bcc
"", //logo
[], //media_attachments
Content::SITE_CCS
);
}
}
}
//AAR
$aarHcObjs = [];
$aarHcidsString = "";
foreach($aar_home_page_contents as $hc) {
$aarHcidsString .= $hc->getId() . " ";
if(in_array($hc->getId(), $cids)) {
$aarHcObjs[] = $hc;
}
}
$aarHcObjsStr = "";
foreach($aarHcObjs as $home_content) {
$aarHcObjsStr .= $home_content->getId() . " ";
foreach($home_content->getCustomers() as $hc_cust) {
if($hc_cust->getDirectoryContent()) {
$recipients = [];
$valid_recipients = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryEmailRecipients()) as $recipient) {
if ($recipient && filter_var(trim($recipient), FILTER_VALIDATE_EMAIL)) {
$recipients[] = trim($recipient);
$valid_recipients = true;
}
}
$recipients_text = "none";
if($valid_recipients) {
$recipients_text = implode(", ", $recipients);
}
$account_managers = [];
$valid_account_managers = false;
foreach(explode(',', $hc_cust->getDirectoryContent()->getDirectoryAccountManagers()) as $account_manager) {
if ($account_manager && filter_var(trim($account_manager), FILTER_VALIDATE_EMAIL)) {
$account_managers[] = trim($account_manager);
$valid_account_managers = true;
}
}
$ac_text = "none";
if($valid_account_managers) {
$ac_text = implode(", ", $account_managers);
}
if(!$valid_recipients) {
continue;
}
//send email
$this->emailHelper->sendEmail(
$recipients,
//["egreco@intradatech.com", "apost@intradatech.com"],
"Your article is now live on the AskARoofer home page!",
[
["p" => "Hello " . $hc_cust->getTitle() . "!" ],
["p" => "Your article is now live on AAR!"],
["p" => "
<div>
<a href='".$home_content->getFullURL(Content::SITE_AAR)."'>
<img width=300 style='border:0;' src='https://www.askaroofer.com/".$home_content->getFeaturedImageURL()."'>
</a>
</div>
<div>
<div>
<a href='".$home_content->getFullURL(Content::SITE_AAR)."' style='color:#043056;font-weight: 700; font-size:16px;text-decoration:none;'>
".$home_content->getTitle()."
</a>
</div>
<i style='color:#26679e;'>".str_replace(["AM", "PM"], ["a.m.", "p.m."], date("F j, Y \\a\\t g:i A", strtotime($home_content->getPublishedAt()->format("Y-m-d H:i:s"))))."</i>
<div class='my-3'>
<span class='sideintrocontent'>".$home_content->getIntroText()."</span>
<a href='".$home_content->getFullURL(Content::SITE_AAR)."' style='color:#26679e;'>
Read More
</a>
</div>
</div>
"],
["p" => "Please share!"],
],
[], //attachments
$account_managers, //cc
"", //bcc
"", //logo
[], //media_attachments
Content::SITE_AAR
);
}
}
}
/*
$this->emailHelper->sendEmail(
["egreco@intradatech.com"],
"RoofersCoffeeShop.com: home page test",
[
["p" => "The following content ids went live:"],
["p" => $cidsString],
//["p" => "The following content ids went live and are on the home page:"],
["p" => "The following content ids are on the home page:"],
["p" => $hcObjsStr],
],
[],
"",
"",
"",
[],
Content::SITE_RCS
);
*/
}
catch(\Exception $e) {
$this->emailHelper->sendEmail(
["egreco@intradatech.com"],
"RoofersCoffeeShop.com: home page email error",
[
["p" => "Dev message: New home page live content email failed. Check logs."],
],
[],
"",
"",
"",
[],
Content::SITE_RCS
);
}
}
//Not site specific
public function updateExpiredAnnouncements() {
$contents = $this->entityManager
->createQuery(implode(" ", array (
"SELECT content",
"FROM",
"App\Entity\Content content",
"JOIN",
"content.contentmeta contentmeta",
"WHERE",
"content.announcement_sites != '' AND content.announcement_sites != '[]'",
"AND contentmeta.metakey = '_PostAnnouncementEndDate'",
"AND contentmeta.metavalue != ''",
"AND contentmeta.metavalue < :now",
)))
->setParameter("now", new \DateTime())
->getResult();
if($contents) {
foreach($contents as $content) {
$content->setAnnouncement(false);
$content->setAnnouncementSites([]);
$content->setContentmetum("_PostAnnouncementEndDate", "");
$this->entityManager->persist($content);
$this->entityManager->flush();
}
}
}
//Filtered by site
public function getDirectoryListings (
$page = 0,
$category = "_",
$type = "_",
$search = "_",
$location = "_",
$state = "_",
$limit = ""
) {
$site_code = $this->getSiteCode();
$page = (int) $page;
// Temp for ticket #151771. Should update to handle spaces / dashes.
$search = strtolower($search) == "georgia pacific" ? "georgia-pacific" : $search;
if ($category != "_") {
// just showing 1 for now...
// $category = explode("-", $category);
// foreach ($category as $c) {
// }
} else {
$category = "";
}
if ($type != "_") {
$type = explode("-", $type);
$num = 0;
foreach ($type as $t) {
switch ($t) {
case "products_services":
$num += Customer::TYPE_PRODUCTS_SERVICES;
break;
case "distributors":
$num += Customer::TYPE_DISTRIBUTORS;
break;
case "consultants":
$num += Customer::TYPE_CONSULTANTS;
break;
case "contractors":
$num += Customer::TYPE_CONTRACTORS;
break;
case "associations":
$num += Customer::TYPE_ASSOCIATIONS;
break;
case "manufacturers_reps":
$num += Customer::TYPE_MANUFACTURERS_REP;
break;
case "rcs_partners":
$num += Customer::TYPE_RCS_PARTNERS;
break;
}
}
$type = $num;
} else {
$type = "";
}
if ($location && $location != "_") {
$location_string = str_replace("-", "%' OR customers.location LIKE '%", $location);
$location_string = "customers.location LIKE '%" . $location_string . "%'";
} else {
$location_string = "";
}
if ($state && $state != "_") {
$state_string = str_replace("-", "%' OR customers.state LIKE '%", $state);
$state_string = "customers.state LIKE '%" . $state_string . "%'";
} else {
$state_string = "";
}
if(!$limit) {
$limit = self::DEFAULT_LIMIT;
}
$offset = ($page * $limit);
$total = 0;
$query = $this->entityManager
->createQuery(($sql = implode(" ", array (
"SELECT DISTINCT",
"content",
"FROM",
"App\Entity\Content content",
"JOIN",
"content.directory customers",
(($site_code) ? implode(" ", array (
"JOIN",
"customers.site site",
"LEFT JOIN",
"customers.levels levels",
)) : ""),
"LEFT JOIN",
"customers.categories categories",
"WHERE",
"content.type = 4 AND",
"content.status = 1",
// if search is provided
(($search) ? implode(" ", array (
"AND (",
//"customers.description LIKE :search1 OR",
"content.content_full LIKE :search1 OR",
"customers.title LIKE :search2 OR",
"customers.keywords LIKE :search3",
")",
)) : ""),
(($site_code) ? implode(" ", array (
"AND",
"site.id = {$site_code}",
"AND",
"(levels.site_id = {$site_code} OR levels.site_id IS NULL)",
)) : ""),
// if type is provided
(($type) ? implode(" ", array (
"AND (",
"BIT_AND( customers.customer_type, :type1 ) != 0",
")",
)) : ""),
// if category is provided
(($category) ? implode(" ", array (
"AND (",
"categories.id = :cat1",
")",
)) : ""),
// if location is provided
(($location_string) ? implode(" ", array (
"AND (",
$location_string,
")",
)) : ""),
// if state is provided
(($state_string) ? implode(" ", array (
"AND (",
$state_string,
")",
)) : ""),
"ORDER BY",
(($site_code == 3) ? implode(" ", array (
"CASE WHEN customers.id = 863 THEN 3
WHEN customers.id = 160 THEN 2
ELSE 0 END DESC,", //Hack to get Sherwin Williams to display first
// WHEN customers.id = 782 THEN 2
//WHEN customers.id = 760 THEN 1
)) : ""),
(($site_code == 4) ? implode(" ", array (
"CASE WHEN customers.id = 680 THEN 4
WHEN customers.id = 491 THEN 3
WHEN customers.id = 481 THEN 2
ELSE 0 END DESC,",
)) : ""),
"content.featured DESC,",
//"customers.customer_level,",
"CASE WHEN levels.level IS NULL THEN customers.customer_level
ELSE levels.level END ASC,",
"customers.title",
))));
if ($search) {
$query->setParameter("search1", "%{$search}%");
$query->setParameter("search2", "%{$search}%");
$query->setParameter("search3", "%{$search}%");
}
if ($type) {
$query->setParameter("type1", $type);
}
if ($category) {
$query->setParameter("cat1", $category);
}
// pagination
$query
->setFirstResult($offset)
->setMaxResults($limit);
$listings = $query
->getResult();
// now count the content
$sql = str_replace("SELECT DISTINCT content", "SELECT COUNT(DISTINCT content.id)", $sql);
$query = $this->entityManager
->createQuery($sql);
if ($search) {
$query->setParameter("search1", "%{$search}%");
$query->setParameter("search2", "%{$search}%");
$query->setParameter("search3", "%{$search}%");
}
if ($type) {
$query->setParameter("type1", $type);
}
if ($category) {
$query->setParameter("cat1", $category);
}
$count = $query
->getResult();
return array (
"listings" => $listings,
"total" => (int) $count[0][1],
);
}
public function getDirectoryCategories ()
{
return $this->entityManager
->getRepository(\App\Entity\Category::class)
->findBy([
"taxonomy" => "directory-category"
], array ("title" => "ASC"));
}
//Filtered by site
public function getClassifiedListings (
$page = 0,
$keywords = "",
$address = "",
$category = "",
$area = "",
$group = "",
$pagingSize = self::DEFAULT_LIMIT
) {
$site_code = $this->getSiteCode();
$page = (int) $page;
$limit = $pagingSize;
$offset = ($page * $limit);
$listings = array ();
if($area != "_") {
$area = explode("-", $area);
}
$sql = implode(" ", array (
"SELECT DISTINCT",
"content",
"FROM",
"App\Entity\Content content",
"JOIN",
"content.contentmeta contentmeta",
(($site_code) ? implode(" ", array (
"JOIN",
"content.site site",
)) : ""),
// join if looking for category
(($category != "_") ?
implode(" ", array (
"JOIN",
"content.secondary_categories categories1",
)) : ""
),
// join if looking for area
(($area != "_") ?
implode(" ", array (
"JOIN",
"content.secondary_categories categories2",
)) : ""
),
// join if looking for special grouping
((!empty($group)) ?
implode(" ", array (
"JOIN",
"content.secondary_categories categories3",
)) : ""
),
"WHERE",
"content.type = 3 AND",
"content.status = 1",
(($address != "_") ?
implode(" ", array (
"AND (",
"contentmeta.metakey = '_job_location' AND",
"contentmeta.metavalue LIKE :address1",
")",
)) : ""
),
(($site_code) ? implode(" ", array (
"AND",
"site.id = {$site_code}",
)) : ""),
(($keywords != "_") ?
implode(" ", array (
"AND (",
"content.title LIKE :keyword1 OR",
"content.content_full LIKE :keyword2",
")",
)) : ""
),
(($category != "_") ?
implode(" ", array (
"AND (",
"categories1.id = :cat1 AND",
"categories1.taxonomy = 'job_listing_category'",
")",
)) : ""
)));
if($area != "_") {
$sql .= "AND (categories2.taxonomy = 'job_listing_type' AND (";
$c = 0;
foreach($area as $a) {
if($c != 0) { $sql .= " OR "; }
$sql .= "categories2.id = {$a}";
$c++;
}
$sql .= "))";
}
$sql .= implode(" ", array (
((!empty($group)) ?
implode(" ", array (
"AND (",
// "categories3.slug = :cat3",
// "categories3.type = ". Category::TYPE_CLASSIFIED_GROUP,
"categories3 = :cat3",
")",
)) : ""
),
"ORDER BY",
"content.featured DESC,",
"content.published_at DESC,",
"content.featured DESC",
));
$query = $this->entityManager
->createQuery($sql);
// var_dump($sql);
// exit;
// var_dump($group);
// exit;
if ($keywords != "_") {
$query->setParameter("keyword1", "%{$keywords}%");
$query->setParameter("keyword2", "%{$keywords}%");
}
if ($address != "_") {
$query->setParameter("address1", "%{$address}%");
}
if ($category != "_") {
$query->setParameter("cat1", $category);
}
/*
if ($area != "_") {
$query->setParameter("cat2", $area);
}
*/
if (!empty($group)) {
$query->setParameter("cat3", $group);
}
// add pagination
$query
->setFirstResult($offset)
->setMaxResults($limit);
$listings = $query
->getResult();
// now count the content
$sql = str_replace("SELECT DISTINCT content", "SELECT COUNT(DISTINCT content.id)", $sql);
$query = $this->entityManager
->createQuery($sql);
if ($keywords != "_") {
$query->setParameter("keyword1", "%{$keywords}%");
$query->setParameter("keyword2", "%{$keywords}%");
}
if ($address != "_") {
$query->setParameter("address1", "%{$address}%");
}
if ($category != "_") {
$query->setParameter("cat1", $category);
}
/*
if ($area != "_") {
$query->setParameter("cat2", $area);
}
*/
if (!empty($group)) {
$query->setParameter("cat3", $group);
}
$count = $query
->getResult();
return array (
"listings" => $listings,
"total" => (int) $count[0][1],
);
}
//Filtered by site
//remove these ASAP
public function getPaginatedContentByType($type = "", int $limit = 10, int $page = 1, $search = "", $customer = "", $category_slug = "", $filter_by_site = true)
{
$site_code = $this->getSiteCode();
//added $search for events, not yet added to other types
//If type is event, uses event start date.
//Use a positive $page number for upcoming events, negative for past.
if($type == 7) {
$qb = $this->entityManager->createQueryBuilder();
$date = date("Y-m-d H:i:s");
if($page > 0) { // upcoming events
$qb->select('c')
->from("App\Entity\Content", "c")
->join("c.contentmeta", "m1", "WITH", "m1.metakey = '_EventStartDate'")
->join("c.contentmeta", "m2", "WITH", "m2.metakey = '_EventEndDate' AND m2.metavalue >= '$date'")
->leftjoin("c.contentmeta", "m3", "WITH", "m3.metakey = '_IsEvent' ");
if ($customer) {
$cuid = $customer->getId();
$qb->join("c.customers", "cu", "WITH", "cu.id = '$cuid'");
}
if($filter_by_site && $site_code) {
$qb->join("c.site", "s", "WITH", "s.id = {$site_code}");
}
$qb->where("c.status = 1 AND (c.type = 7 OR m3.metavalue = 'yes') AND m1.metavalue != '' AND m2.metavalue != ''");
if($search) {
$qb->andWhere("c.content_full LIKE '%{$search}%' OR c.title LIKE '%{$search}%'");
}
$results = $qb->setMaxResults($limit)
->setFirstResult($limit * ($page - 1))
->addOrderBy("m1.metavalue", "ASC")
->addOrderBy("c.title", "ASC")
->getQuery()->getResult();
}
else { // past events
$qb->select('c')
->from("App\Entity\Content", "c")
->join("c.contentmeta", "m1", "WITH", "m1.metakey = '_EventStartDate'")
->join("c.contentmeta", "m2", "WITH", "m2.metakey = '_EventEndDate' AND m2.metavalue < '$date'")
->leftjoin("c.contentmeta", "m3", "WITH", "m3.metakey = '_IsEvent' ");
if ($customer) {
$cuid = $customer->getId();
$qb->join("c.customers", "cu", "WITH", "cu.id = '$cuid'");
}
if($filter_by_site && $site_code) {
$qb->join("c.site", "s", "WITH", "s.id = {$site_code}");
}
$qb->where("c.status = 1 AND (c.type = 7 OR m3.metavalue = 'yes') AND m1.metavalue != '' AND m2.metavalue != ''");
if($search) {
$qb->andWhere("c.content_full LIKE '%{$search}%' OR c.title LIKE '%{$search}%'");
}
$results = $qb->setMaxResults($limit)
->setFirstResult($limit * (-$page - 1))
->addOrderBy("m1.metavalue", "DESC")
->addOrderBy("c.title", "DESC")
->getQuery()->getResult();
$results = array_reverse($results);
}
return $results;
}
else {
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from("App\Entity\Content", "c");
if ($customer) {
$cuid = $customer->getId();
$qb->join("c.customers", "cu", "WITH", "cu.id = '$cuid'");
}
if($category_slug) {
if($category_slug == "newsroom") {
$qb->join("c.secondary_categories", "cat1")
->leftJoin("cat1.prnt", "cat2")
->leftJoin("cat2.prnt", "cat3")
->leftJoin("cat3.prnt", "cat4")
;
$qb->leftjoin("c.category", "cat", "WITH", "(cat.slug != 'buy-online' AND cat.slug != 'partner-videos') OR cat IS NULL");
}
else {
$qb->join("c.category", "cat", "WITH", "cat.slug = '{$category_slug}'");
}
}
else {
//To remove 'buy online' items. We should make a 'buy online' content type, then remove this.
if($type == 2) {
$qb->leftjoin("c.category", "cat", "WITH", "(cat.slug != 'buy-online' AND cat.slug != 'partner-videos') OR cat IS NULL");
}
}
if($filter_by_site && $site_code) {
$qb->join("c.site", "s", "WITH", "s.id = {$site_code}");
}
$qb->where("c.type = {$type} AND c.status = 1");
if($category_slug == "newsroom") {
$qb->andWhere("(cat1.slug = '{$category_slug}' OR cat2.slug = '{$category_slug}' OR cat3.slug = '{$category_slug}' OR cat4.slug = '{$category_slug}'
OR cat1.slug IN ('rcs-influencers', 'mcs-influencers', 'ccs-influencers') OR cat2.slug IN ('rcs-influencers', 'mcs-influencers', 'ccs-influencers'))"); //temporary
}
$content = $qb->setMaxResults($limit)
->setFirstResult($limit * ($page - 1))
->addOrderBy("c.published_at", "DESC")
->getQuery()->getResult();
if ($content) {
return $content;
}
else {
return "";
}
throw new ResourceNotFoundException ("Content Not Found of Type: {$type}");
}
}
//RCS site only - unfiltered
public function getPreviousEvent($event)
{
$qb = $this->entityManager->createQueryBuilder();
$meta_collection = $event->getContentmeta();
$content_meta = array();
foreach($meta_collection as $meta) {
$content_meta[$meta->getMetakey()] = $meta->getMetavalue();
}
$date = $content_meta["_EventStartDate"];
$title = $event->getTitle();
$result = $qb->select('c')
->from("App\Entity\Content", "c")
->join("c.contentmeta", "m1", "WITH", "m1.metakey = '_EventStartDate' AND m1.metavalue <= :date")
->join("c.contentmeta", "m2", "WITH", "m2.metakey = '_EventEndDate'")
->leftjoin("c.contentmeta", "m3", "WITH", "m3.metakey = '_IsEvent' ")
->where("c.status = 1 AND (c.type = 7 OR m3.metavalue = 'yes') AND m1.metavalue != '' AND m2.metavalue != ''")
->andWhere("m1.metavalue < :date OR c.title < :title")
->setMaxResults(1)
->addOrderBy("m1.metavalue", "DESC")
->addOrderBy("c.title", "DESC")
->getQuery()
->setParameter("date", $date)
->setParameter("title", $title)
->getResult();
if($result) {
return $result[0];
}
else {
return "";
}
}
//RCS site only - unfiltered
public function getNextEvent($event)
{
$qb = $this->entityManager->createQueryBuilder();
$meta_collection = $event->getContentmeta();
$content_meta = array();
foreach($meta_collection as $meta) {
$content_meta[$meta->getMetakey()] = $meta->getMetavalue();
}
$date = $content_meta["_EventStartDate"];
$title = $event->getTitle();
$result = $qb->select('c')
->from("App\Entity\Content", "c")
->join("c.contentmeta", "m1", "WITH", "m1.metakey = '_EventStartDate' AND m1.metavalue >= :date")
->join("c.contentmeta", "m2", "WITH", "m2.metakey = '_EventEndDate'")
->leftjoin("c.contentmeta", "m3", "WITH", "m3.metakey = '_IsEvent' ")
->where("c.status = 1 AND (c.type = 7 OR m3.metavalue = 'yes') AND m1.metavalue != '' AND m2.metavalue != ''")
->andWhere("m1.metavalue > :date OR c.title > :title")
->setMaxResults(1)
->addOrderBy("m1.metavalue", "ASC")
->addOrderBy("c.title", "ASC")
->getQuery()
->setParameter("date", $date)
->setParameter("title", $title)
->getResult();
if($result) {
return $result[0];
}
else {
return "";
}
}
//RCS site only - unfiltered
public function countPreviousEvents($search = "")
{
$qb = $this->entityManager->createQueryBuilder();
$date = date("Y-m-d H:i:s");
$results = $qb->select('c')
->from("App\Entity\Content", "c")
->join("c.contentmeta", "m1", "WITH", "m1.metakey = '_EventStartDate'")
->join("c.contentmeta", "m2", "WITH", "m2.metakey = '_EventEndDate' AND m2.metavalue < '$date'")
->leftjoin("c.contentmeta", "m3", "WITH", "m3.metakey = '_IsEvent' ")
->where("c.status = 1 AND (c.type = 7 OR m3.metavalue = 'yes') AND m1.metavalue != '' AND m2.metavalue != ''")
->andWhere("c.content_full LIKE '%{$search}%' OR c.title LIKE '%{$search}%'")
->getQuery()->getResult();
return count($results);
}
//RCS site only - unfiltered
public function countUpcomingEvents($search = "")
{
$qb = $this->entityManager->createQueryBuilder();
$date = date("Y-m-d H:i:s");
$results = $qb->select('c')
->from("App\Entity\Content", "c")
->join("c.contentmeta", "m1", "WITH", "m1.metakey = '_EventStartDate'")
->join("c.contentmeta", "m2", "WITH", "m2.metakey = '_EventEndDate' AND m2.metavalue >= '$date'")
->leftjoin("c.contentmeta", "m3", "WITH", "m3.metakey = '_IsEvent' ")
->where("c.status = 1 AND (c.type = 7 OR m3.metavalue = 'yes') AND m1.metavalue != '' AND m2.metavalue != ''")
->andWhere("c.content_full LIKE '%{$search}%' OR c.title LIKE '%{$search}%'")
->getQuery()->getResult();
return count($results);
}
//RCS site only - unfiltered
public function getEventsByMonth($month = 0, $year = 0)
{
if(!$month) { $month = date('n'); }
if(!$year) { $year = date('Y'); }
$date = date("Y-m-d H:i:s", strtotime("$year-$month-01"));
$first_of_month = $date;
$first_weekday = date('w', strtotime($date));
$date = date("Y-m-d H:i:s", strtotime("-$first_weekday day", strtotime($date)));
$num_days_month = date('t', strtotime($first_of_month));
//$last_weekday = date("w", strtotime("-1 day", strtotime("$year-$month-$num_days_month")));
$last_weekday = date("w", strtotime("$year-$month-$num_days_month"));
$date_eod = date("Y-m-d H:i:s", strtotime(date("Y-m-d", strtotime($date)) . " 23:59:59"));
$results = array();
$x = 7 - $last_weekday;
while($date < date("Y-m-d H:i:s", strtotime("+$x day", strtotime("$year-$month-$num_days_month")))) {
$qb = $this->entityManager->createQueryBuilder();
$results[$date] = $qb->select('c')
->from("App\Entity\Content", "c")
->join("c.contentmeta", "m1", "WITH", "m1.metakey = '_EventStartDate' AND m1.metavalue <= '$date_eod'")
->join("c.contentmeta", "m2", "WITH", "m2.metakey = '_EventEndDate' AND m2.metavalue >= '$date'")
->leftjoin("c.contentmeta", "m3", "WITH", "m3.metakey = '_IsEvent' ")
->where("c.status = 1 AND (c.type = 7 OR m3.metavalue = 'yes') AND m1.metavalue != '' AND m2.metavalue != ''")
->orderBy("m1.metavalue", "ASC")
->getQuery()->getResult();
$date = date("Y-m-d H:i:s", strtotime("+1 day", strtotime($date)));
$date_eod = date("Y-m-d H:i:s", strtotime("+1 day", strtotime($date_eod)));
}
return $results;
}
//RCS site only - unfiltered
public function getEventsByDay($date, $search)
{
$qb = $this->entityManager->createQueryBuilder();
$date_eod = date("Y-m-d H:i:s", strtotime(date("Y-m-d", strtotime($date)) . " 23:59:59"));
$qb->select('c')
->from("App\Entity\Content", "c")
->join("c.contentmeta", "m1", "WITH", "m1.metakey = '_EventStartDate' AND m1.metavalue <= '$date_eod'")
->join("c.contentmeta", "m2", "WITH", "m2.metakey = '_EventEndDate' AND m2.metavalue >= '$date'")
->leftjoin("c.contentmeta", "m3", "WITH", "m3.metakey = '_IsEvent' ")
->where("c.status = 1 AND (c.type = 7 OR m3.metavalue = 'yes') AND m1.metavalue != '' AND m2.metavalue != ''");
if($search) {
$qb->andWhere("c.content_full LIKE '%{$search}%' OR c.title LIKE '%{$search}%'");
}
$results = $qb->orderBy("m1.metavalue", "ASC")
->getQuery()->getResult();
return $results;
}
/*
* Takes the content type and the number of results,
* and returns the latest X pages, posts, etc.
*/
public function getLatestContent ($type = "", $num_results = "3")
{
$content = $this->entityManager
->getRepository(Content::class)
->findBy([
"type" => $type,
"status" => 1,
],
[
'published_at' => 'DESC', //orderby
],
$num_results, //limit
0 //offset
);
if ($content) {
return $content;
}
throw new ResourceNotFoundException ("Content Not Found: type = {$type}");
}
//Not site specific
public function getContentById ($id = 0)
{
$content = $this->entityManager
->getRepository(Content::class)
->findOneBy([
"id" => $id
]);
if ($content) {
return $content;
}
throw new ResourceNotFoundException ("Content with Id {$id} - Not Found");
}
//Not site specific
public function getContentForAnalyticsAggregated($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT DISTINCT content",
"FROM",
"App\Entity\Content content",
"JOIN",
"content.customers customer",
"LEFT JOIN",
"customer.prnt prnt",
"JOIN",
"content.content_analytics_daily content_analytics_daily",
"WHERE",
"(customer.id = :id OR prnt.id = :id)",
"AND content_analytics_daily.day BETWEEN :start AND :end",
"AND content.status NOT IN ('0', '-1', '-3', '-4', '-5')",
));
$results = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
return $results;
}
public function countContentImpressions($content, $startDate, $endDate, $site_id="")
{
$query = implode(" ", array (
"SELECT COUNT(impression.traffic_data)",
"FROM",
"App\Entity\ContentImpression impression",
"JOIN",
"impression.content content",
"WHERE",
"content.id = :id",
"AND (impression.requested_at BETWEEN :start AND :end)",
($site_id ? implode(" ", array (
"AND impression.site_id = {$site_id}",
)) : ""),
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $content->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
public function countContentImpressionsGroupedBySite($content, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT impression.site_id, COUNT(impression.traffic_data) as impressions",
"FROM",
"App\Entity\ContentImpression impression",
"JOIN",
"impression.content content",
"WHERE",
"content.id = :id",
"AND (impression.requested_at BETWEEN :start AND :end)",
"GROUP BY impression.site_id",
));
$results = $this->entityManager
->createQuery($query)
->setParameter("id", $content->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
return $results;
}
//Not site specific
public function countContentImpressionsAggregated($content, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT SUM(content_analytics_daily.impressions)",
"FROM",
"App\Entity\ContentAnalyticsDaily content_analytics_daily",
"JOIN",
"content_analytics_daily.content content",
"WHERE",
"content.id = :id",
"AND content_analytics_daily.day BETWEEN :start AND :end",
));
$sum = $this->entityManager
->createQuery($query)
->setParameter("id", $content->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($sum)) {
return (int) $sum[0][1];
}
return 0;
}
//Not site specific
public function countContentImpressionsAggregatedCorrected($content, $startDate, $endDate, $site_id="")
{
$query = implode(" ", array (
"SELECT",
"(",
"SELECT IFNULL(SUM(cad.impressions), 0)",
"FROM content_analytics_daily cad",
"WHERE cad.content_id = :id",
"AND cad.day BETWEEN :start AND :end",
($site_id ? implode(" ", [
"AND cad.site_id = {$site_id}",
]) : ""),
") +",
"(",
"SELECT IFNULL(SUM(cacd.impressions), 0)",
"FROM content_analytics_correction_daily cacd",
"WHERE cacd.content_id = :id",
"AND cacd.day BETWEEN :start AND :end",
($site_id ? implode(" ", [
"AND cacd.site_id = {$site_id}",
]) : ""),
") as total",
));
$stmt = $this->entityManager->getConnection()->prepare($query);
$stmt->execute(['id' => $content->getId(), 'start' => $startDate->format("Y-m-d H:i:s"), 'end' => $endDate->format("Y-m-d H:i:s")]);
$count = $stmt->fetch();
if (!empty($count)) {
return (int) $count["total"];
}
return 0;
}
public function countContentClicks($content, $startDate, $endDate, $site_id="")
{
$query = implode(" ", array (
"SELECT COUNT(view.traffic_data)",
"FROM",
"App\Entity\ContentView view",
"JOIN",
"view.content content",
"WHERE",
"content.id = :id",
"AND (view.requested_at BETWEEN :start AND :end)",
($site_id ? implode(" ", array (
"AND view.site_id = {$site_id}",
)) : ""),
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $content->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
public function countContentClicksGroupedBySite($content, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT view.site_id, COUNT(view.traffic_data) as views",
"FROM",
"App\Entity\ContentView view",
"JOIN",
"view.content content",
"WHERE",
"content.id = :id",
"AND (view.requested_at BETWEEN :start AND :end)",
"GROUP BY view.site_id",
));
$results = $this->entityManager
->createQuery($query)
->setParameter("id", $content->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
return $results;
}
//Not site specific
public function countContentClicksAggregated($content, $startDate, $endDate, $site_id="")
{
$query = implode(" ", array (
"SELECT SUM(content_analytics_daily.views)",
"FROM",
"App\Entity\ContentAnalyticsDaily content_analytics_daily",
"JOIN",
"content_analytics_daily.content content",
"WHERE",
"content.id = :id",
"AND content_analytics_daily.day BETWEEN :start AND :end",
($site_id ? implode(" ", [
"AND content_analytics_daily.site_id = {$site_id}",
]) : ""),
));
$sum = $this->entityManager
->createQuery($query)
->setParameter("id", $content->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($sum)) {
return (int) $sum[0][1];
}
return 0;
}
//Not site specific
// Count all the content impressions for a specific customer and time range
public function countCustomerContentImpressions($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT COUNT(impression.traffic_data)",
"FROM",
"App\Entity\ContentImpressions impression",
"JOIN",
"impression.content content",
"JOIN",
"content.customers customer",
"WHERE",
"customer.id = :id",
"AND (impression.requested_at BETWEEN :start AND :end)",
"AND content.status NOT IN ('0', '-1', '-3', '-4', '-5')",
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
//Not site specific
// Count all the content impressions for a specific customer and time range
public function countCustomerContentImpressionsAggregated($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT SUM(content_analytics_daily.impressions)",
"FROM",
"App\Entity\ContentAnalyticsDaily content_analytics_daily",
"JOIN",
"content_analytics_daily.content content",
"JOIN",
"content.customers customer",
"WHERE",
"customer.id = :id",
"AND (content_analytics_daily.day BETWEEN :start AND :end)",
"AND content.status NOT IN ('0', '-1', '-3', '-4', '-5')",
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
//Not site specific
// Count all the content impressions for a specific customer and time range
public function countCustomerContentImpressionsAggregatedCorrected($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT",
"(",
"SELECT IFNULL(SUM(cad.impressions), 0)",
"FROM content_analytics_daily cad",
"JOIN customer_content cc",
"ON cc.content_id = cad.content_id",
"JOIN content content",
"ON cc.content_id = content.id",
"JOIN content_site cs",
"ON cs.content_id = cad.content_id AND cs.site_id = cad.site_id",
"WHERE cc.customer_id = :id",
"AND cad.day BETWEEN :start AND :end",
"AND content.status NOT IN ('0', '-1', '-3', '-4', '-5')",
") +",
"(",
"SELECT IFNULL(SUM(cacd.impressions), 0)",
"FROM content_analytics_correction_daily cacd",
"JOIN customer_content cc",
"ON cc.content_id = cacd.content_id",
"JOIN content content",
"ON cc.content_id = content.id",
"JOIN content_site cs",
"ON cs.content_id = cacd.content_id AND cs.site_id = cacd.site_id",
"WHERE cc.customer_id = :id",
"AND cacd.day BETWEEN :start AND :end",
"AND content.status NOT IN ('0', '-1', '-3', '-4', '-5')",
") as total",
));
$stmt = $this->entityManager->getConnection()->prepare($query);
$stmt->execute(['id' => $customer->getId(), 'start' => $startDate->format("Y-m-d H:i:s"), 'end' => $endDate->format("Y-m-d H:i:s")]);
$count = $stmt->fetch();
if (!empty($count)) {
return (int) $count["total"];
}
return 0;
}
//Not site specific
// Count all the content clicks (now called content views in the dashboard) for a specific customer and time range
public function countCustomerContentClicks($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT COUNT(view.traffic_data)",
"FROM",
"App\Entity\ContentView view",
"JOIN",
"view.content content",
"JOIN",
"content.customers customer",
"WHERE",
"customer.id = :id",
"AND (view.requested_at BETWEEN :start AND :end)",
"AND content.status NOT IN ('0', '-1', '-3', '-4', '-5')",
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
//Not site specific
// Count all the content clicks (now called content views in the dashboard) for a specific customer and time range
public function countCustomerContentClicksAggregated($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT SUM(content_analytics_daily.views)",
"FROM",
"App\Entity\ContentAnalyticsDaily content_analytics_daily",
"JOIN",
"content_analytics_daily.content content",
"JOIN",
"content.customers customer",
"JOIN",
"content.site site",
"WHERE",
"customer.id = :id",
"AND (content_analytics_daily.day BETWEEN :start AND :end)",
"AND content.status NOT IN ('0', '-1', '-3', '-4', '-5')",
"AND site.id = content_analytics_daily.site_id",
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
//Not site specific
public function countContentLinkClicks($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT content.id, click.link_url, COUNT(click.link_url) as count",
"FROM",
"App\Entity\ContentLinkClick click",
"JOIN",
"click.content content",
"JOIN",
"content.customers customer",
"LEFT JOIN",
"customer.prnt prnt",
"WHERE",
"(customer.id = :id OR prnt.id = :id)",
"AND (click.requested_at BETWEEN :start AND :end)",
"AND content.status NOT IN ('0', '-1', '-3', '-4', '-5')",
"GROUP BY click.link_url",
));
$results = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
return $results;
}
//Not site specific
public function countCustomerLeads($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT content.id, COUNT(lead.id) as count",
"FROM",
"App\Entity\Lead lead",
"JOIN",
"lead.content content",
"JOIN",
"content.customers customer",
"LEFT JOIN",
"customer.prnt prnt",
"WHERE",
"(customer.id = :id OR prnt.id = :id)",
"AND (lead.created_at BETWEEN :start AND :end)",
));
$query .= "GROUP BY content.id";
$results = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
return $results;
}
//Not site specific
//should replace with a general count function for getPaginatedContentByType results (or return with those results)
public function countPostsByCustomer($customer = "") {
if (!$customer) {
return null;
}
$cuid = $customer->getId();
$qb = $this->entityManager->createQueryBuilder();
$results = $qb->select('count(c.id)')
->from("App\Entity\Content", "c")
->join("c.customers", "cu", "WITH", "cu.id = '$cuid'")
->leftJoin("c.category", "cat", "WITH", "(cat.slug != 'buy-online' AND cat.slug != 'partner-videos') OR cat IS NULL")
->join("c.secondary_categories", "cat1")
->leftJoin("cat1.prnt", "cat2")
->leftJoin("cat2.prnt", "cat3")
->leftJoin("cat3.prnt", "cat4")
->where("c.type = 2 AND c.status = 1")
->andWhere("(cat1.slug = 'newsroom' OR cat2.slug = 'newsroom' OR cat3.slug = 'newsroom' OR cat4.slug = 'newsroom')")
->getQuery()->getSingleScalarResult();
return $results;
}
public function countContentByCustomer($type = "", $customer = "", $filter_by_site = true) {
$site_code = $this->getSiteCode();
if (!$customer) {
return null;
}
if (!$type) {
return null;
}
$cuid = $customer->getId();
$qb = $this->entityManager->createQueryBuilder();
$qb->select('count(c.id)')
->from("App\Entity\Content", "c")
->join("c.customers", "cu", "WITH", "cu.id = '$cuid'");
if($filter_by_site && $site_code) {
$qb->join("c.site", "s", "WITH", "s.id = {$site_code}");
}
$results = $qb->where("c.type = $type AND c.status = 1")
->getQuery()->getSingleScalarResult();
return $results;
}
//Filtered by site
public function getPostsByCategory (
$category = "",
$length = 10,
$page = 1,
$sort = "",
$includeChildren = true
) {
return $this->entityManager
->getRepository(Content::class)
->getPostsByCategory($category, $length, $page, $sort, $includeChildren, $this->getSiteCode());
}
public function getRecommendedPosts (
$category = null,
$length = 3,
$ignoreID = 0
) {
$query = implode(" ", [
"SELECT cad.content_id",
"FROM `content_analytics_daily` cad",
"LEFT JOIN `content` c ",
"ON cad.content_id = c.id ",
(!is_null($category) ? implode(" ", [
"LEFT JOIN `content_category` cc",
"ON cc.content_id = c.id",
"WHERE cc.category_id = :category",
"AND ",
]) : "WHERE "),
" cad.day > :date",
"AND c.status = 1",
"GROUP BY cad.content_id",
"ORDER BY MAX(cad.`views`) DESC",
"limit 20;"
]);
$stmt = $this->entityManager->getConnection()->prepare($query);
$exec_params = ['date' => date_create(date("Y-m-d"))->modify('-10 days')->format('Y-m-d')];
if(!is_null($category)) {
$exec_params['category'] = $category->getId();
}
$stmt->execute($exec_params);
$hot = $stmt->fetchAll();
$howManyHot = count($hot);
if ($howManyHot) {
$hot = $hot[array_rand($hot, 1)]['content_id'];
$hotPost = $this->getContentById($hot);
$hotPost->hot = true;
}
$site = $this->getSiteCode();
$result = $this->entityManager->createQueryBuilder();
$result = $result->select('a')->from("App\Entity\Content", "a")->join("a.secondary_categories", "c")->join("a.site", "s");
$result = $result->where("c.id = :id")->setParameter("id", $category)->distinct();
$result = $result->andWhere("a.status = 1");
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
if ($ignoreID) {
$result = $result->andWhere("c.id != {$ignoreID}")->andWhere("a.id != {$ignoreID}");
if ($howManyHot) {
$result = $result->andWhere("a.id != {$hot}")->andWhere("c.id != {$hot}");
}
}
if ($howManyHot) {
$result = $result->andWhere("a.id != {$hot}");
if ($howManyHot) {
$result = $result->andWhere("c.id != {$hot}");
}
}
$result = $result->orderBy("rand()");
if ($howManyHot) {
$result = $result->setMaxResults($length-1);
}else{
$result = $result->setMaxResults($length);
}
$result = $result->getQuery()->getResult();
for ($i = 0; $i < count($result); $i++) {
$result[$i]->hot = false;
}
if ($howManyHot) {
array_unshift($result , $hotPost);
}
shuffle($result);
return $result;
}
//Filtered by site
public function getPostsByCategories (
$categories = "",
$length = 10,
$page = 1,
$sort = "",
$includeChildren = true
) {
return $this->entityManager
->getRepository(Content::class)
->getPostsByCategories($categories, $length, $page, $sort, $includeChildren, $this->getSiteCode());
}
//Filtered by Site
public function getContentByCategories (
$type = 0,
$categories = "",
$length = 10,
$page = 1,
$sort = "",
$includeChildren = true
) {
return $this->entityManager
->getRepository(Content::class)
->getContentByCategories($type, $categories, $length, $page, $sort, $includeChildren, $this->getSiteCode());
}
//Filtered by site
public function countContentByCategories (
$type = 0,
$categories = "",
$includeChildren = true
) {
$count = $this->entityManager
->getRepository(Content::class)
->countContentByCategories($type, $categories, true, $this->getSiteCode());
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
//Filtered by site
public function getDirectoriesByCategory (
$category = "",
$length = 10,
$page = 1,
$sort = "",
$includeChildren = true
) {
return $this->entityManager
->getRepository(Content::class)
->getDirectoriesByCategory($category, $length, $page, $sort, $includeChildren, $this->getSiteCode());
}
//Filtered by site
public function countPostsByCategory (
$category = "",
$includeChildren = true
) {
$count = $this->entityManager
->getRepository(Content::class)
->countPostsByCategory($category, true, $this->getSiteCode());
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
//Filtered by site
public function countPostsByCategories (
$categories = "",
$includeChildren = true
) {
$count = $this->entityManager
->getRepository(Content::class)
->countPostsByCategories($categories, true, $this->getSiteCode());
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
//Not site specific
public function countTopicsPostsByForum (
$slug = ""
) {
$count = $this->entityManager
->getRepository(Content::class)
->countTopicsPostsByForum($slug);
if (!empty($count)) {
return [
"num_topics" => (int) $count[0][1],
"num_posts" => (int) $count[0][2] + (int) $count[0][1],
];
}
return [
"num_topics" => 0,
"num_posts" => 0,
];
}
public function getGalleries (
$category = "",
$sort = ""
) {
return $this->entityManager
->getRepository(Content::class)
->getGalleries($category, $sort, $this->getSiteCode());
}
public function getEbooks(
$customer = ""
){
$site_code = $this->getSiteCode();
return $this->entityManager
->getRepository(Content::class)
->getEbooks($customer, $site_code);
}
public function getEbooksByCategory(
$category = ""
){
$site_code = $this->getSiteCode();
return $this->entityManager
->getRepository(Content::class)
->getEbooksByCategory($category, $site_code);
}
//aarhere
public function getHrefById ($id = 0)
{
$content = $this->getContentById($id);
if ($content) {
return $this->getHref($content);
}
throw new \Exception ("Content with Id {$id} - Not Found");
}
public function getHref (Content $content)
{
switch ($content->getType())
{
case Content::PAGE:
return "/{$content->getSlug()}";
case Content::POST:
// need to grab the primary category
$category = "";
return "/post/todo{$category}/{$content->getSlug()}";
case Content::CLASSIFIED:
return "/classified/{$content->getSlug()}";
case Content::DIRECTORY:
return "/directory/{$content->getSlug()}";
case Content::EVENT:
return "/event/{$content->getSlug()}";
case Content::FORUM:
return "/forum/{$content->getSlug()}";
case Content::FORUM_TOPIC:
return "/forum/topic/{$content->getSlug()}";
case Content::FORUM_REPLY:
// grab the topic
$topic = "";
return "/forum/topic/{$topic}";
}
return "";
}
public function getComments(Content $content)
{
$criteria = Criteria::create()
->where(Criteria::expr()->eq("approved", "1"))
->orderBy(array("created_at" => Criteria::ASC))
;
return $content->getComments()->matching($criteria);
}
public function getPaginatedComments(Content $content, int $limit = 10, int $page = 1)
{
$criteria = Criteria::create()
->where(Criteria::expr()->eq("approved", "1"))
->orderBy(array("created_at" => Criteria::ASC))
->setFirstResult($limit * ($page - 1))
->setMaxResults($limit)
;
return $content->getComments()->matching($criteria);
}
public function getPaginatedChildren(Content $content, int $limit = 10, int $page = 1, bool $asc = false)
{
$criteria = Criteria::create()
->where(Criteria::expr()->eq("status", "1"))
->orderBy(array("published_at" => $asc ? Criteria::ASC : Criteria::DESC))
->setFirstResult($limit * ($page - 1))
->setMaxResults($limit)
;
return $content->getChildren()->matching($criteria);
}
//horrible, don't use this
public function countChildren(Content $content)
{
return $content->getActiveChildren()->count();
}
public function countActiveChildren(Content $content)
{
$sql = implode(" ", array (
"SELECT",
"COUNT(c)",
"FROM",
"App\Entity\Content c",
"WHERE",
"c.status = 1",
"AND c.prnt = :id",
));
$query = $this->entityManager
->createQuery($sql)
->setParameter("id", $content->getId());
$count = $query->getResult();
$count = $count[0][1];
return $count;
}
public function getTopicsByForum(Content $forum, int $limit = 10, int $page = 1)
{
$forumId = $forum->getId();
$offset = $limit * ($page - 1);
$query = implode(" ", array (
"SELECT i.id FROM (",
"SELECT t.id, r.published_at",
"FROM content r",
"JOIN content t ",
"ON r.prnt_id = t.id",
"WHERE r.type = 11",
"AND r.status = 1",
"AND t.prnt_id = :forumId",
"UNION",
"SELECT t.id, t.published_at",
"FROM content t",
"WHERE t.type = 10",
"AND t.status = 1",
"AND t.prnt_id = :forumId",
") i",
"GROUP BY i.id",
"ORDER BY MAX(i.published_at) DESC",
"LIMIT {$limit} OFFSET {$offset}",
));
$stmt = $this->entityManager->getConnection()->prepare($query);
$stmt->execute(['forumId' => $forum->getId()]);
$contents = $stmt->fetchAll();
$topics = [];
foreach($contents as $c) {
$content = $this->getContentById($c["id"]);
$topics[] = $content;
}
return $topics;
}
public function getTopicsByForumOld(Content $forum, int $limit = 10, int $page = 1)
{
$query = $this->entityManager
->createQuery(($sql = implode(" ", array (
"SELECT",
"topic, (CASE WHEN reply.published_at IS NULL THEN topic.published_at ELSE MAX(reply.published_at) END) AS rdate",
"FROM",
"App\Entity\Content topic",
"LEFT JOIN",
"topic.children reply",
"WHERE",
"topic.type = 10 AND",
"topic.status = 1 AND",
"topic.prnt = {$forum->getId()} AND",
"(reply.status = 1 OR reply.status IS NULL)",
"GROUP BY",
"topic",
"ORDER BY",
"rdate DESC",
))));
$query->setFirstResult($limit * ($page - 1))
->setMaxResults($limit);
$results = $query->getResult();
$results2 = array();
foreach($results as $result) {
$results2[] = $result[0];
}
return $results2;
}
public function getTopicsByForum2(Content $forum, int $limit = 10, int $page = 1)
{
$query = $this->entityManager
->createQuery(($sql = implode(" ", array (
"SELECT",
"reply",
"FROM",
"App\Entity\Content reply",
"LEFT JOIN",
"reply.prnt topic",
"WHERE",
"reply.type = 11 AND",
"reply.status = 1 AND",
"topic.status = 1 AND",
"topic.prnt = {$forum->getId()}",
"GROUP BY",
"reply.prnt",
"ORDER BY",
"MAX(reply.published_at) DESC,",
"topic.published_at DESC",
))));
$query->setFirstResult($limit * ($page - 1))
->setMaxResults($limit);
$replies = $query->getResult();
$topics = [];
foreach($replies as $reply) {
$topics[] = $reply->getPrnt();
}
return $topics;
}
/**
* Returns forum topics and replies based on their titles and content
*/
public function getForumSearchResults($search = "", int $limit = 10, int $page = 1)
{
$site_code = $this->getSiteCode();
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from("App\Entity\Content", "c");
if($site_code) {
$qb->join("c.site", "s", "WITH", "s.id = {$site_code}");
}
$qb->where("c.type = 10 OR c.type = 11")
->andWhere("c.status = 1");
$results = $qb->andWhere("c.content_full LIKE '%{$search}%' OR c.title LIKE '%{$search}%'")
->setMaxResults($limit)
->setFirstResult($limit * ($page - 1))
->addOrderBy("c.published_at", "DESC")
->getQuery()->getResult();
return $results;
}
//TODO: test
public function countForumSearchResults($search = "")
{
$site_code = $this->getSiteCode();
$query = implode(" ", array (
"SELECT COUNT(DISTINCT content)",
"FROM",
"App\Entity\Content content",
($site_code ? implode(" ", array (
"JOIN",
"content.site site",
)) : ""),
"WHERE",
"(content.type = 10 OR content.type = 11)",
"AND content.status = 1",
($site_code ? implode(" ", array (
"AND site.id = {$site_code}",
)) : ""),
"AND (content.content_full LIKE '%{$search}%' OR content.title LIKE '%{$search}%')",
));
$count = $this->entityManager
->createQuery($query)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
public function getSidebars(string $slug)
{
$sidebars = array ();
// ...
if (strpos($slug, "category/") !== false) {
// ... include category sidebar modules
}
if (strpos($slug, ".com/") == false) {
$sidebars[] = array(
"type" => "signup",
"title" => "Sign up for our e-news!"
);
$sidebars[] = array(
"type" => "latest_classifieds",
"title" => "Latest Classifieds",
"classifieds" => $this->getLatestContent(3)
);
}
return $sidebars;
}
/* Original code from https://gist.github.com/chrisknepper/cba89754a222695c37e9
Call this with the shown parameters (make sure $time and $end are integers and in Unix timestamp format!)
Get a link that will open a new event in Google Calendar with those details pre-filled */
public function make_google_calendar_link($name, $begin, $end, $location, $details) {
$params = array('&dates=', '/', '&location=', '&details=', '&sf=true&output=xml');
$url = 'https://www.google.com/calendar/render?action=TEMPLATE&text=';
$arg_list = func_get_args();
for ($i = 0; $i < count($arg_list); $i++) {
$current = $arg_list[$i];
if(is_int($current)) {
$t = new DateTime('@' . $current, new DateTimeZone('UTC'));
$current = $t->format('Ymd\THis\Z');
unset($t);
}
else {
$current = urlencode($current);
}
$url .= (string) $current . $params[$i];
}
return $url;
}
public function make_ics_file($location, $description, $dtstart, $dtend, $summary, $url) {
$ics = new ICS(array(
'location' => $location,
'description' => $description,
'dtstart' => $dtstart,
'dtend' => $dtend,
'summary' => $summary,
'url' => $url
));
return $ics->to_string();
}
/**
*
*/
public function getContent (
$type = "*",
$category = "*",
$order = "published",
$dir = "desc",
$page = 0,
$limit = 0,
$allowDupes = false,
$taxonomy = "*",
$use_secondary_categories = false, //need to explicitly require searching by secondary categories, otherwise just searches by the primary (for optimization)
$members_only = false, // not currently used
$require_featured_image = false,
$only_featured = false,
$customer_id = "",
$customer_level_or_higher = "",
$only_announcements = false,
$excludeInDupes = false, //Don't include content in the dupes / 'used_ids' variable. Used for the mobile sidebar (which is at top of page) so that other modules with no duplicates allowed still show this content.
$site_override = "",
$clear_dupes_after_get = false
//$test = false
) {
//$sarray = __METHOD__ . serialize(func_get_args());
/*if($test){
$cache = new FilesystemAdapter();
$test_cache = $cache->getItem("test_cache");
if($test_cache->isHit()) {
$results = $test_cache->get();
return $results;
}
}*/
$site_code = $this->getSiteCode();
if($site_override) {
if($site_override == "all") {
$site_code = null;
}
else {
$site_code = $site_override;
}
}
$order = ($order == "") ? "published" : $order;
$user = $this->security->getUser();
$isMember = false;
if($user && ($user->isMember() || $user->isAdmin()) ) {
//also show members only posts
$isMember = true;
}
// prevent showing the same content from different modules
static $used_ids;
if (empty($used_ids)) {
$used_content = array ();
}
$useCatIds = true;
$useTaxIds = true;
// for events
$today = date("Y-m-d H:i:s");
if($type == Content::EVENT) {
$order = "events";
$dir = "asc";
}
// Move to repository class
$typenum = $type;
if ($type == "*") {
$type = "> 0";
}
elseif($type == Content::EVENT) {
$type = "IN (7, 8)"; //Event or Webinar
}
else {
$type = (int) $type;
$type = "= {$type}";
}
if ($category != "*") {
if (strpos($category, ",") !== false) {
$category = explode(",", $category);
}
else if (!is_array($category)) {
$category = array($category);
}
foreach($category as $key => $c) {
if(!$this->categoryHelper->getCategoryExistsBySlug($c)) {
unset($category[$key]);
}
}
if(count($category) == 0) {
return $results = [
"total" => 0,
"content" => [],
];
}
$categories = $this->categoryHelper->getChildCategoriesBySlugs($category);
$category = [];
foreach($categories as $c) {
$category[] = $c;
//$category[] = $c->getSlug(); //4/17
}
$useCatIds = true;
$categorySQL = array ();
foreach ($category as $cat) {
if ($useCatIds && !is_numeric($cat)) {
$useCatIds = false;
}
$categorySQL[] = ":cat" . (count($categorySQL) + 1);
}
}
if ($taxonomy != "*") {
if (strpos($taxonomy, ",") !== false) {
$taxonomy = explode(",", $taxonomy);
}
else if (!is_array($taxonomy)) {
$taxonomy = array($taxonomy);
}
$useTaxIds = true;
$taxonomySQL = array ();
foreach ($taxonomy as $tax) {
if (!$useTaxIds && !is_numeric($tax)) {
$useTaxIds = false;
}
$taxonomySQL[] = ":tax" . (count($taxonomySQL) + 1);
}
}
$dir = (strtolower($dir) == "asc") ? "ASC" : "DESC";
$order_by = "";
// types of sorting
switch (strtolower($order)) {
case "created":
$order_by = "content.created_at {$dir}";
break;
case "published":
$order_by = "content.published_at {$dir}";
break;
case "expires":
$order_by = "content.expires_at {$dir}";
break;
case "title":
$order_by = "content.title {$dir}";
break;
case "random":
$order_by = "random";
break;
case "events":
$order_by = "contentmeta1.metavalue {$dir}";
break;
}
$sql = implode(" ", array (
"SELECT DISTINCT",
"content",
"FROM",
"App\Entity\Content content",
// if we are filtering by categories
($use_secondary_categories || is_array($taxonomy) ? implode(" ", array (
"JOIN",
"content.secondary_categories category1",
/* 4/17
"LEFT JOIN",
"category1.prnt category2",
"LEFT JOIN",
"category2.prnt category3",
*/
)) : ""),
// for directory pages
($typenum == Content::DIRECTORY ? implode(" ", array (
"JOIN",
"content.customers customers",
)) : ""),
($typenum != Content::DIRECTORY ? implode(" ", array (
"LEFT JOIN",
"content.customers customers",
)) : ""),
// for events pages
//($typenum == Content::EVENT ? implode(" ", array (
($order == "events" ? implode(" ", array (
"JOIN",
"content.contentmeta contentmeta1",
)) : ""),
//($typenum == Content::EVENT ? implode(" ", array (
($order == "events" ? implode(" ", array (
"JOIN",
"content.contentmeta contentmeta2",
)) : ""),
//($typenum == Content::EVENT ? implode(" ", array (
($order == "events"? implode(" ", array (
"LEFT JOIN",
"content.contentmeta contentmetaisevent",
)) : ""),
($site_code ? implode(" ", array (
"JOIN",
"content.site site",
)) : ""),
"WHERE",
"content.type {$type} AND",
"content.status = 1",
(!empty($used_ids) && !$allowDupes ? implode(" ", array (
"AND content.id NOT IN (",
implode(", ", $used_ids),
")",
)) : ""),
($site_code ? implode(" ", array (
"AND site.id = {$site_code}",
)) : ""),
/*
($members_only ? implode(" ", array (
"AND content.members_only = 1",
)) : ""),
*/
($require_featured_image ? implode(" ", array (
"AND content.media != ''",
)) : ""),
(!$isMember ? implode(" ", array (
"AND content.members_only = 0",
)) : ""),
($customer_id ? implode(" ", array (
"AND customers.id IN ({$customer_id})",
)) : ""),
/*
($customer_id ? implode(" ", array (
"AND customers.id = {$customer_id}",
)) : ""),
*/
($customer_level_or_higher ? implode(" ", array (
"AND customers.customer_level <= {$customer_level_or_higher}",
)) : ""),
($only_featured ? implode(" ", array (
"AND content.featured = 1",
)) : ""),
(($only_announcements && $site_code) ? implode(" ", array (
"AND content.announcement_sites LIKE '%{$site_code}%'",
)) : ""),
//Don't include if excluded from list views
"AND content.exclude_from_list = 0",
// sort by start date, and filter out past events
//($typenum == Content::EVENT ? implode(" ", array (
($order == "events" ? implode(" ", array (
//"AND content.expires_at > '{$today}'"
"AND contentmeta1.metakey = '_EventStartDate'",
"AND contentmeta1.metavalue != ''",
"AND (",
"contentmeta2.metakey = '_EventEndDate' AND",
"contentmeta2.metavalue > '{$today}'",
") ",
"AND (",
"content.type = 7 OR ",
"(contentmetaisevent.metakey = '_IsEvent' AND contentmetaisevent.metavalue = 'yes')",
") ",
)) : ""),
// filter out directory influencers
($typenum == Content::DIRECTORY ? implode(" ", array (
"AND content.title NOT LIKE '%RCS Influencer%'",
"AND content.title NOT LIKE '%PHD, Author%'",
)) : ""),
// if filtering by categories
(is_array($category) ? implode(" ",
($useCatIds ?
array (
"AND (",
"category1.id IN (",
implode(", ", $categorySQL),
")",
/*4/17, same was below
"OR",
"category2.id IN (",
implode(", ", $categorySQL),
") OR",
"category3.id IN (",
implode(", ", $categorySQL),
")",
*/
")",
) :
($use_secondary_categories || is_array($taxonomy) ?
array (
"AND (",
"category1 IN (",
implode(", ", $categorySQL),
")",
")",
) :
array (
"AND (",
"content.category IN (",
implode(", ", $categorySQL),
")",
")",
)
)
)
) : "" ),
// if filtering by taxonomies
(is_array($taxonomy) ? implode(" ",
// ($useTaxIds ? <- taxonomy does use ids
array (
"AND (",
"category1.taxonomy IN (",
implode(", ", $taxonomySQL),
")",
/*4/17
"OR",
"category2.taxonomy IN (",
implode(", ", $taxonomySQL),
")",
"OR",
"category3.taxonomy IN (",
implode(", ", $taxonomySQL),
")",
*/
")",
)
) : "" ),
"ORDER BY",
(($order_by != "" && $order_by != "random") ?
$order_by :
"content.published_at DESC"
),
));
if ($order_by == "random") {
// first grab all the id's from content that match the query
$sql = str_replace("SELECT DISTINCT content", "SELECT DISTINCT (content.id)", $sql);
$query = $this->entityManager->createQuery($sql);
if (is_array($category)) {
$pos = 1;
foreach ($category as $cat) {
$query->setParameter("cat{$pos}", $cat);
$pos++;
}
}
if (is_array($taxonomy)) {
$pos = 1;
foreach ($taxonomy as $tax) {
$query->setParameter("tax{$pos}", $tax);
$pos++;
}
}
$content = $query->getResult();
shuffle($content);
$temp = array ();
for ($i = 0; $i < count($content); $i++) {
$temp[] = $content[$i][1];
}
$content = $temp;
if ($limit) {
$content = array_slice($content, 0, $limit);
}
$sql = str_replace("SELECT DISTINCT (content.id)", "SELECT DISTINCT content", $sql);
if (count($content)) {
$sql = str_replace("WHERE", "WHERE content.id IN (". implode(",", $content) . ") AND", $sql);
}
}
$query = $this->entityManager->createQuery($sql);
// add the parameters
if (is_array($category)) {
$pos = 1;
foreach ($category as $cat) {
$query->setParameter("cat{$pos}", $cat);
$pos++;
}
}
if (is_array($taxonomy)) {
$pos = 1;
foreach ($taxonomy as $tax) {
$query->setParameter("tax{$pos}", $tax);
$pos++;
}
}
// pagination
if ($limit) {
$offset = ($page * $limit);
$query
->setFirstResult($offset)
->setMaxResults($limit);
}
$content = $query->getResult();
/* works but won't use the full result set ...
if (strtolower($order) == "random") {
shuffle($content);
}
*/
// store the ids
if(!$excludeInDupes) {
for ($i = 0; $i < count($content); $i++) {
$used_ids[] = $content[$i]->getId();
}
}
// count the totals
$sql = str_replace("SELECT DISTINCT content", "SELECT COUNT(DISTINCT content.id)", $sql);
$query = $this->entityManager
->createQuery($sql);
// add the parameters
if (is_array($category)) {
$pos = 1;
foreach ($category as $cat) {
$query->setParameter("cat{$pos}", $cat);
$pos++;
}
}
if (is_array($taxonomy)) {
$pos = 1;
foreach ($taxonomy as $tax) {
$query->setParameter("tax{$pos}", $tax);
$pos++;
}
}
$total = $query->getResult();
if ($order_by == "random") {
shuffle($content);
}
$results = [
"total" => $total[0][1],
"content" => $content,
];
/*if($test){
$test_cache->set($results);
$test_cache->expiresAfter(120);
$cache->save($test_cache);
}*/
// clear dupes (not generally used)
if($clear_dupes_after_get) {
$used_ids = [];
$used_content = [];
}
return $results;
}
//Used to check if something posted to the forum should be considered spam
//Currently just checks for Chinese and Korean characters
public function isSpam($string) {
//Checks for Chinese characters
if( preg_match("/\p{Han}+/u", $string) ) {
return true;
}
//Checks for Korean characters
if( preg_match('/[\x{3130}-\x{318F}\x{AC00}-\x{D7AF}]/u', $string) ) {
return true;
}
//Checks for Cyrillic letters
if( preg_match("/\p{Cyrillic}+/u", $string) ) {
return true;
}
//Checks for Arabic letters
if( preg_match("/\p{Arabic}+/u", $string) ) {
return true;
}
//Block .onion links
if (strpos($string, ".onion") !== false) {
return true;
}
//Block certain URLs / strings (Should we make something like the "domain blacklist" where these can be entered by admins?)
if (strpos($string, "digitalvertex.com") !== false ||
strpos($string, "mailbanger.com") !== false ||
strpos($string, "webolowitz.com") !== false ||
strpos($string, "thissucks.com") !== false ||
strpos($string, "jumboleadmagnet.com") !== false
) {
return true;
}
// Block Specific email address (Should we make something like the "domain blacklist" where these can be entered by admins?)
if (strpos($string, "millionsgroup4@gmail.com") !== false) {
return true;
}
return false;
}
public function getTopicsByReplyDate ()
{
$sql = implode(" ", array (
"SELECT DISTINCT",
"topic",
"FROM",
"App\Entity\Content topic",
"LEFT JOIN",
"topic.children reply",
"WHERE",
"topic.type = 10 AND",
"topic.status = 1",
"ORDER BY",
"reply.published_at DESC,",
"topic.published_at DESC",
));
$query = $this->entityManager
->createQuery($sql);
return $query->getResult();
}
public function search (
$search = "",
$page = 1
) {
$site_code = $this->getSiteCode() ?? 1;
$limit = self::DEFAULT_LIMIT;
$offset = ($limit * ($page - 1));
$sql = implode(" ", [
"SELECT",
"COUNT(DISTINCT content.id)",
"FROM",
"App\Entity\Content content",
"JOIN",
"content.site site",
"LEFT JOIN",
"content.directory customer",
"LEFT JOIN",
"customer.levels levels",
"WHERE",
"content.status = 1 AND",
"site.id = {$site_code} AND",
"content.searchable = 1 AND",
"content.type != 6 AND",
"content.type != 9 AND",
"content.type != 10 AND",
"content.type != 11 AND",
"content.type != 12 AND",
"content.type != 13 AND",
"content.type != 16 AND",
"content.type != 17 AND",
"content.type != 21 AND",
"content.type != 22 AND",
"content.type != 23 AND",
"content.type != 24 AND",
"content.type != 27 AND",
"(",
"content.title LIKE :search OR",
"content.content_full LIKE :search OR",
"content.content_builder LIKE :search",
")",
" AND (levels.site_id = {$site_code} OR levels.site_id IS NULL)",
"ORDER BY",
"CASE WHEN :searchExact LIKE :gpString AND content.id = 109945 THEN 1 ELSE 2 END,", //exception for Georgia-Pacific directory (ticket #159817)
"CASE WHEN content.title LIKE :search THEN 1 ELSE 2 END,",
"CASE WHEN content.type = 4 THEN 1 ELSE 2 END,",
"CASE WHEN content.type = 4 AND customer.id IS NOT NULL AND levels.level IS NULL THEN customer.customer_level
WHEN content.type = 4 AND customer.id IS NOT NULL AND levels.level IS NOT NULL THEN levels.level
ELSE 10000 END,",
"content.published_at DESC",
]);
$query = $this->entityManager
->createQuery($sql)
->setParameter("search", "%{$search}%")
->setParameter("searchExact", "$search")
->setParameter("gpString", "gp");
$count = $query->getResult();
$count = $count[0][1];
$sql = str_replace("COUNT(DISTINCT content.id)", "DISTINCT content", $sql);
$query = $this->entityManager
->createQuery($sql)
->setParameter("search", "%{$search}%")
->setParameter("searchExact", "$search")
->setParameter("gpString", "gp")
->setFirstResult($offset)
->setMaxResults($limit);
$content = $query->getResult();
return [
"count" => $count,
"content" => $content,
];
}
public function countImpressions ($content)
{
$sql = implode(" ", array (
"SELECT",
"COUNT(t)",
"FROM",
"App\Entity\Traffic t",
"WHERE",
"t.user_this_path = :url",
));
$query = $this->entityManager
->createQuery($sql)
->setParameter("url", $content->getURL());
$count = $query->getResult();
$count = $count[0][1];
return $count;
}
/*public function enqueueImpression($request, $content="")
{
$this->impressionQueue(false, $request, $content);
}
public function storeQueuedImpressions()
{
$this->impressionQueue(true);
}
public function impressionQueue($store=false, $request="", $content="")
{
static $stored_request;
static $content_ids;
if(empty($stored_request)) {
$stored_request = $request;
}
if(empty($content_ids)) {
$content_ids = [];
}
if($content){
$content_ids[] = $content->getId();
}
if($store && $stored_request && !empty($content_ids)) {
$all_content = [];
foreach($content_ids as $content_id) {
$all_content[] = $this->getContentById($content_id);
}
$this->addImpressions($stored_request, $all_content);
$content_ids = [];
}
}*/
public function enqueueImpression($request, $content="")
{
$this->impressionQueue(false, $request, $content);
}
public function enqueueImpressions($request, $contents="")
{
foreach($contents as $content) {
$this->impressionQueue(false, $request, $content);
}
}
public function storeQueuedImpressions($request)
{
$this->impressionQueue(true, $request);
}
public function impressionQueue($store=false, $request="", $content="")
{
if(!$request) {
return false;
}
$session = $request->getSession();
//$stored_request = $session->get("stored_request", "");
$content_ids = $session->get("queued_content_ids", []);
if($content_ids && count($content_ids) > 1000) {
$session->remove("queued_content_ids");
$content_ids = [];
}
if($content){
$content_ids[] = $content->getId();
}
if($store && $request && !empty($content_ids)) {
$session->remove("queued_content_ids");
$all_content = [];
foreach($content_ids as $content_id) {
$current_content = $this->entityManager
->getRepository(Content::class)
->findOneBy([
"id" => $content_id
]);
if($current_content) {
$all_content[] = $current_content;
}
}
$this->addImpressions($request, $all_content);
}
else {
$session->set("queued_content_ids", $content_ids);
}
}
public function addImpressions($request, $contents)
{
foreach($contents as $content) {
$this->addImpression($request, $content);
}
}
public function addImpression($request, $content="", $link="", $item="", $type="0", $href="")
{
try {
//$request = $event->getRequest();
$session = $request->getSession();
$site_code = $this->getSiteCode();
$routeName = $request->get("_route");
$routeParams = $request->get("_route_params");
$path = $request->getPathInfo();
if ($this->tokenStorage->getToken()) {
$user = $this->tokenStorage->getToken()->getUser();
} else {
$user = null;
}
if (is_string($user)) {
$user = null;
}
// ==================================================
// Log the traffic request
// $temp = $request;
$temp = $_REQUEST;
// remove any private payment data ...
if (isset($temp["payment"])) {
unset($temp["payment"]);
}
$last_route = $session->get("last_route", [
"name" => "",
"params" => "",
"path" => "",
]);
$user_token = $session->get("user_token", bin2hex(openssl_random_pseudo_bytes(32)));
$user_ip = $request->getClientIp();
$user_ua = $request->headers->get("User-Agent");
$user_refer = isset($_SERVER["HTTP_REFERER"]) ? $_SERVER["HTTP_REFERER"] : "";
$user_this_path = $path;
$user_this_route = isset($routeName) ? $routeName : "";
$user_this_route_params = isset($routeParams) ? json_encode($routeParams) : "";
$user_last_path = isset($last_route["path"]) ? $last_route["path"] : "";
$user_last_route = isset($last_route["name"]) ? $last_route["name"] : "";
$user_last_route_params = isset($last_route["params"]) ? json_encode($last_route["params"]) : "";
$user_request_data = json_encode($temp);
$requested_at = new DateTime("now");
$hash = md5(implode("", [
$user_token,
$user_ip,
$user_ua,
($user) ? $user->getId() : "",
$user_refer,
$user_this_path,
$user_this_route,
$user_this_route_params,
$user_last_path,
$user_last_route,
$user_last_route_params,
$user_request_data,
date("Y-m-d H:i:s"),
($content) ? $content->getId() : "",
($link) ? $link->getId() : "",
($item) ? $item->getId() : "",
rand(1,1000),
]));
$traffic_data = new TrafficData();
$traffic_data->setHash($hash);
$traffic_data->setUserToken($user_token);
$traffic_data->setUser($user);
$traffic_data->setUserIp($user_ip);
$traffic_data->setUserUa($user_ua);
$traffic_data->setUserRefer($user_refer);
$traffic_data->setUserThisPath($user_this_path);
$traffic_data->setUserThisRoute($user_this_route);
$traffic_data->setUserThisRouteParams($user_this_route_params);
$traffic_data->setUserLastPath($user_last_path);
$traffic_data->setUserLastRoute($user_last_route);
$traffic_data->setUserLastRouteParams($user_last_route_params);
$traffic_data->setUserRequestData($user_request_data);
$traffic_data->setRequestedAt($requested_at);
if($type == 3) {
if($content) {
$content_link_click = new ContentLinkClick();
$content_link_click->setTrafficData($traffic_data);
$content_link_click->setContent($content);
$content_link_click->setRequestedAt($requested_at);
$content_link_click->setSiteId($site_code);
$content_link_click->setLinkUrl($href);
}
}
else {
if($content) {
$content_impression = new ContentImpression();
$content_impression->setTrafficData($traffic_data);
$content_impression->setContent($content);
$content_impression->setRequestedAt($requested_at);
$content_impression->setSiteId($site_code);
//$content->incrementTotalImpressions();
}
elseif($link) {
/*
$traffic->setType(Traffic::TYPE_LINK_CLICK);
$traffic->setLink($link);
*/
}
elseif($item) {
if($type == 1) {
$ad_impression = new AdImpression();
$ad_impression->setTrafficData($traffic_data);
$ad_impression->setMediaGroupItem($item);
$ad_impression->setRequestedAt($requested_at);
$ad_impression->setSiteId($site_code);
//$item->incrementTotalImpressions();
}
elseif($type == 2) {
$ad_click = new AdClick();
$ad_click->setTrafficData($traffic_data);
$ad_click->setMediaGroupItem($item);
$ad_click->setRequestedAt($requested_at);
$ad_click->setSiteId($site_code);
//$item->incrementTotalClicks();
}
}
}
try{
/*
$this->entityManager->persist($traffic);
$this->entityManager->flush();
*/
if(($type == 3) && $content ) {
$this->entityManager->persist($content_link_click);
$this->entityManager->persist($traffic_data);
$this->entityManager->flush();
}
elseif($content) {
$this->entityManager->persist($content);
$this->entityManager->persist($content_impression);
$this->entityManager->persist($traffic_data);
$this->entityManager->flush();
}
elseif($item) {
$this->entityManager->persist($item);
$this->entityManager->persist($traffic_data);
if($type == 1) {
$this->entityManager->persist($ad_impression);
}
elseif($type == 2) {
$this->entityManager->persist($ad_click);
}
$this->entityManager->flush();
}
} catch(\Exception $e) {
try{
//Exception may occur if inserting a duplicate primary key. Should log this somewhere.
$errorlog = fopen(__DIR__ . "/../../var/log/traffic_error.txt", "a") or die("Unable to open file!");
$txt = 'Caught exception (ContentHelper.php addImpression()): '. $e->getMessage() . "\n";
fwrite($errorlog, $txt);
fclose($errorlog);
} catch(\Exception $e) {
// Error writing to file
}
}
$session->set("user_token", $user_token);
} catch(\Exception $e) {
try{
$errorlog = fopen(__DIR__ . "/../../var/log/traffic_error.txt", "a") or die("Unable to open file!");
$txt = 'Caught exception (ContentHelper.php addImpression(), 1st loops before persisting): '. $e->getMessage() . "\n";
fwrite($errorlog, $txt);
fclose($errorlog);
} catch(\Exception $e) {
// Error writing to file
}
}
}
public function likeContent($content, $cuser)
{
$content->addContentMetumKV("_like_user_id", $cuser->getId());
}
public function unlikeContent($content, $cuser)
{
$content->removeContentMetumKV("_like_user_id", $cuser->getId());
}
public function countLikes($content)
{
//return $content->countContentMetum("_like_user_id");
return $content->countLikes();
}
//move this to its own helper
public function getProductById ($id = "")
{
$product = $this->entityManager
->getRepository(Product::class)
->findOneBy([
"id" => $id
]);
if ($product) {
return $product;
}
throw new ResourceNotFoundException ("Product with id {$id} - Not Found");
}
public function getDirectoryConentSiteMap($slug = "") {
$directory = $this->getDirectoryBySlug($slug);
$customer = $directory->getDirectory();
$customerID = $customer->getId();
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from('App\Entity\Content', 'c')
->join("c.customers", "cu", "WITH", "cu.id = '$customerID'")
->andWhere('c.status = 1')
->orderBy('c.published_at', 'DESC');
$content = $qb->getQuery()->getResult();
// split up by content types
$typemap = [
1 => "PAGE",
2 => "POST",
3 => "CLASSIFIED",
5 => "GALLERY",
6 => "LANDING_PAGE",
7 => "EVENT",
8 => "WEBINAR",
9 => "FORUM",
10 => "FORUM_TOPIC",
11 => "FORUM_REPLY",
12 => "MODULE",
13 => "HTML_AD",
14 => "PROMOS_REBATES",
15 => "THE_HUB",
16 => "EVENT_ORGANIZER",
17 => "EVENT_VENUE",
18 => "PODCAST",
19 => "EBOOK",
20 => "CONTEST_GAMES",
21 => "ACF_FIELD_GROUP",
22 => "ACF_FIELD",
23 => "TRADE_ASSOCIATIONS",
24 => "THANK_YOU_LANDING_PAGE",
25 => "AWARD",
26 => "VIDEO",
27 => "R_CLUB_PERK",
28 => "SCHOLARSHIP",
999 => "BUY_ONLINE",
998 => "SPANISH",
997 => "INTERNATIONAL",
996 => "CANADA"
];
$types = [
$PAGE = [], // type = 1;
$POST = [], // type = 2;
$CLASSIFIED = [], // type = 3;
$GALLERY = [], // type = 5;
$LANDING_PAGE = [], // type = 6;
$EVENT = [], // type = 7;
$WEBINAR = [], // type = 8;
$FORUM = [], // type = 9;
$FORUM_TOPIC = [], // type = 10;
$FORUM_REPLY = [], // type = 11;
$MODULE = [], // type = 12;
$HTML_AD = [], // type = 13;
$PROMOS_REBATES = [], // type = 14;
$THE_HUB = [], // type = 15;
$RLW = [], // type = 15;
$EVENT_ORGANIZER = [], // type = 16;
$EVENT_VENUE = [], // type = 17;
$PODCAST = [], // type = 18;
$EBOOK = [], // type = 19;
$CONTEST_GAMES = [], // type = 20;
$ACF_FIELD_GROUP = [], // type = 21;
$ACF_FIELD = [], // type = 22;
$TRADE_ASSOCIATIONS = [], // type = 23;
$THANK_YOU_LANDING_PAGE = [], // type = 24;
$AWARD = [], // type = 25;
$VIDEO = [], // type = 26;
$R_CLUB_PERK = [], // type = 27;
$SCHOLARSHIP = [], // type = 28;
$buyonline = [], // type = 999;
$spanish = [], // type = 998;
$international = [], // type = 997;
$canada = [], // type = 996;
];
// split content up by type
foreach ($content as $con) {
$content_type = $con->getType();
if (!array_key_exists($content_type, $typemap)) {
continue;
}
$maptype = $typemap[$content_type];
$types{$maptype}[] = $con;
}
// $types buy online
$types["buyonline"] = $this->getContent(
"*",
"buy-online",
"published",
"desc",
0,
0,
true,
"*",
true,
false,
false,
false,
$customerID
);
if ($types["buyonline"]["content"]){
$types["buyonline"] = $types["buyonline"]['content'];
}else{
$types["buyonline"] = [];
}
$types["spanish"] = $this->getContent(
"*",
"en-espanol",
"published",
"desc",
0,
0,
true,
"*",
true, //secondary categories
false,
false,
false,
$customerID
);
if ($types["spanish"]["content"]){
$types["spanish"] = $types["spanish"]['content'];
}else{
$types["spanish"] = [];
}
$types["international"] = $this->getContent(
"*",
"international",
"published",
"desc",
0,
0,
true,
"*",
true, //secondary categories
false,
false,
false,
$customerID
);
if ($types["international"]["content"]){
$types["international"] = $types["international"]['content'];
}else{
$types["international"] = [];
}
$types["canada"] = $this->getContent(
"*",
"canada",
"published",
"desc",
0,
0,
true,
"*",
true, //secondary categories
false,
false,
false,
$customerID
);
if ($types["canada"]["content"]) {
$types["canada"] = $types["canada"]['content'];
}else{
$types["canada"] = [];
}
$urls = [];
// loop over types and count each sub arrawy
foreach ($types as $key=>$tp){
$chuncked = array_chunk($tp, 10);
$chunchedCount = count($chuncked);
// get type number from maptype
$typenum = array_search($key, $typemap);
// if typenum is less than 900
if ($key != "buyonline" && $key != "spanish" && $key != "international" && $key != "canada") {
if ($chunchedCount == 1) {
if ($typenum == 2){
$urls[] = "/directory/$slug/posts/";
$urls[] = "/directory/$slug/posts/1";
}
$urls[] = "/directory/$slug/content/$typenum";
} else {
for ($i = 0; $i < $chunchedCount; $i++) {
if ($i == 0) {
if ($typenum == 2){
$urls[] = "/directory/$slug/posts/";
$urls[] = "/directory/$slug/posts/1";
}
$urls[] = "/directory/$slug/content/$typenum";
}else{
if ($typenum == 2){
$urls[] = "/directory/$slug/posts/" . ($i + 1);
}
$urls[] = "/directory/$slug/content/$typenum/" . ($i + 1);
}
}
}
}else{
if ($key == "buyonline"){
$key = "buy-online";
}
// $urls[] = "/directory/$slug/content/$key";
if ($chunchedCount == 1) {
$urls[] = "/directory/$slug/content/$key";
} else {
for ($i = 0; $i < $chunchedCount; $i++) {
if ($i == 0) {
$urls[] = "/directory/$slug/content/$key";
}else{
$urls[] = "/directory/$slug/content/$key/" . ($i + 1);
}
}
}
}
}
return $urls;
}
}

src/Utils/ContentHelper.php line 137