protected function join(QueryBuilder $qb, $add = false)
{
$qb->leftjoin('agency.accounts', 'accounts');
$qb->leftjoin('agency.users', 'users');
$qb->leftjoin('users.end', 'users_end');
$qb->leftjoin('agency.contacts', 'contacts');
$qb->leftjoin('agency.city', 'city');
if ($add) {
$qb->addSelect('accounts');
$qb->addSelect('users');
$qb->addSelect('users_end');
$qb->addSelect('contacts');
$qb->addSelect('city');
}
return $qb;
}
protected function join(QueryBuilder $qb, $add = false)
{
$qb->leftjoin('agency_account.turnovers', 'turnovers');
if ($add) {
$qb->addSelect('turnovers');
}
return $qb;
}
protected function join(QueryBuilder $qb, $add = false)
{
$qb->leftjoin('account.contacts', 'contacts');
$qb->leftjoin('account.owners', 'owners');
$qb->leftjoin('owners.end', 'owners_end');
$qb->leftjoin('account.agencies', 'agencies');
$qb->leftjoin('agencies.turnovers', 'turnovers');
$qb->leftjoin('account.city', 'city');
if ($add) {
$qb->addSelect('contacts');
$qb->addSelect('owners');
$qb->addSelect('owners_end');
$qb->addSelect('agencies');
$qb->addSelect('turnovers');
$qb->addSelect('city');
}
return $qb;
}
protected function join(QueryBuilder $qb, $add = false)
{
$qb->innerjoin('user.user', 'securityUser');
$qb->leftjoin('user.activities', 'activities');
$qb->leftjoin('user.assignedAccount', 'assignedAccount');
$qb->leftjoin('assignedAccount.end', 'assignedAccount_end');
$qb->leftjoin('user.agencies', 'agencies');
$qb->leftjoin('agencies.agency', 'agency');
$qb->leftjoin('agencies.end', 'agencies_end');
if ($add) {
$qb->addSelect('securityUser');
$qb->addSelect('assignedAccount');
$qb->addSelect('agencies');
$qb->addSelect('agency');
$qb->addSelect('agencies_end');
$qb->addSelect('assignedAccount_end');
}
return $qb;
}
protected function search(QueryBuilder $qb, $searchs)
{
if (is_array($searchs)) {
foreach ($searchs as $field => $value) {
if ($value) {
switch ($field) {
case 'id':
$qb->andWhere('campaign.id = :search_id');
$qb->setParameter('search_id', $value);
break;
case 'name':
$qb->andWhere('campaign.name LIKE :search_name');
$qb->setParameter('search_name', '%' . $value . '%');
break;
case 'subject':
$qb->andWhere('campaign.emailSubject LIKE :search_emailSubject');
$qb->setParameter('search_emailSubject', '%' . $value . '%');
break;
case 'state':
$qb->andWhere('campaign.state = :search_state');
$qb->setParameter('search_state', $value);
break;
case 'dateSended':
$value = \DateTime::createFromFormat('d-m-Y H:i:s', $value . ':00');
$qb->andWhere('campaign.dateSended >= :search_date_sended');
$qb->setParameter('search_date_sended', $value);
break;
case 'dateSendedEnd':
$value = \DateTime::createFromFormat('d-m-Y H:i:s', $value . ':00');
$qb->andWhere('campaign.dateSended < :search_date_sended_end');
$qb->setParameter('search_date_sended_end', $value);
break;
case 'owner':
$qb->andWhere('owner.firstname LIKE :search_owner OR owner.name LIKE :search_owner');
$qb->setParameter('search_owner', '%' . $value . '%');
break;
case 'agency':
$qb->leftjoin('owner.agencies', 'owner_agencies');
$qb->addSelect('owner_agencies');
$qb->leftjoin('owner_agencies.end', 'owner_agencies_end');
$qb->addSelect('owner_agencies_end');
$qb->andWhere('owner_agencies.agency = :search_agency');
$qb->andWhere('owner_agencies.dateCreated <= campaign.dateCreated');
$qb->andWhere('owner_agencies_end IS NULL OR owner_agencies_end.dateEnd > campaign.dateCreated');
$qb->setParameter('search_agency', $value);
break;
case 'createdBy':
$qb->leftjoin('owner.agencies', 'owner_agencies');
$qb->addSelect('owner_agencies');
$qb->leftjoin('owner_agencies.end', 'owner_agencies_end');
$qb->addSelect('owner_agencies_end');
$qb->andWhere('owner_agencies.agency = :search_agency');
$qb->andWhere('owner_agencies.dateCreated <= campaign.dateCreated');
$qb->andWhere('owner_agencies_end IS NULL OR owner_agencies_end.dateEnd > campaign.dateCreated');
$qb->setParameter('search_agency', $value);
$qb->andWhere('owner_agencies_end IS NULL');
$qb->andWhere('owner = :search_owner');
$qb->setParameter('search_owner', $value);
break;
}
}
}
}
return $qb;
}
protected function search(QueryBuilder $qb, $searchs)
{
if (is_array($searchs)) {
foreach ($searchs as $field => $value) {
if ($value) {
switch ($field) {
case 'id':
$qb->andWhere('activity.id = :search_id');
$qb->setParameter('search_id', $value);
break;
case 'type':
$qb->andWhere('type.title LIKE :search_type');
$qb->setParameter('search_type', '%' . $value . '%');
break;
case 'type_type':
$qb->andWhere('type.type = :search_type_type');
$qb->setParameter('search_type_type', $value);
break;
case 'account_name':
$qb->andWhere('account.name LIKE :search_account_name');
$qb->setParameter('search_account_name', '%' . $value . '%');
break;
case 'user_name':
$qb->andWhere('user.name LIKE :search_user_name OR user.firstname LIKE :search_user_name');
$qb->setParameter('search_user_name', '%' . $value . '%');
break;
case 'user':
$qb->andWhere('user = :search_user');
$qb->setParameter('search_user', $value);
break;
case 'dateStart':
$value = \DateTime::createFromFormat('d-m-Y H:i:s', $value . ':00');
$qb->andWhere('activity.dateStart >= :search_date_start');
$qb->setParameter('search_date_start', $value);
break;
case 'dateStartEnd':
$value = \DateTime::createFromFormat('d-m-Y H:i:s', $value . ':00');
$qb->andWhere('activity.dateStart < :search_date_end');
$qb->setParameter('search_date_end', $value);
break;
case 'comment':
$qb->andWhere('activity.comment LIKE :search_comment');
$qb->setParameter('search_comment', '%' . $value . '%');
break;
case 'account':
$qb->andWhere('account = :search_account');
$qb->setParameter('search_account', $value);
break;
case 'agency':
$qb->leftjoin('user.agencies', 'user_agencies');
$qb->leftjoin('user_agencies.end', 'user_agencies_end');
$qb->andWhere('user_agencies.agency = :search_agency');
$qb->andWhere('user_agencies.dateCreated <= activity.dateCreated');
$qb->andWhere('user_agencies_end IS NULL OR user_agencies_end.dateEnd > activity.dateCreated');
$qb->setParameter('search_agency', $value);
break;
case 'activity_account_owner':
$qb->leftjoin('account.owners', 'account_owners');
$qb->andWhere('account_owners.user = :search_activity_account_owner');
$qb->setParameter('search_activity_account_owner', $value);
break;
}
}
}
}
return $qb;
}
protected function search(QueryBuilder $qb, $searchs)
{
if (is_array($searchs)) {
foreach ($searchs as $field => $value) {
if ($value) {
switch ($field) {
case 'id':
$qb->andWhere('contact.id = :search_id');
$qb->setParameter('search_id', $value);
break;
case 'name':
$qb->andWhere('contact.name LIKE :search_name OR contact.firstname LIKE :search_name');
$qb->setParameter('search_name', '%' . $value . '%');
break;
case 'email':
$qb->andWhere('contact.email LIKE :search_email');
$qb->setParameter('search_email', '%' . $value . '%');
break;
case 'function':
$qb->andWhere('contact.function LIKE :search_function');
$qb->setParameter('search_function', '%' . $value . '%');
break;
case 'city':
$qb->andWhere('city.name LIKE :search_city OR city.zipcode LIKE :search_city');
$qb->setParameter('search_city', '%' . $value . '%');
break;
case 'phones':
$qb->andWhere('phones.phone LIKE :search_phones');
$qb->setParameter('search_phones', '%' . $value . '%');
break;
case 'account_name':
$qb->andWhere('account.name LIKE :search_account_name');
$qb->setParameter('search_account_name', '%' . $value . '%');
break;
case 'agency':
$qb->andWhere('agencies = :search_agency');
$qb->setParameter('search_agency', $value);
break;
case 'account_owner':
$qb->leftjoin('account.owners', 'owners');
$qb->andWhere('owners.user = :search_owner');
$qb->setParameter('search_owner', $value);
break;
case 'hasFax':
$qb->andWhere('phones.type = :phone_type_fax OR account.fax IS NOT NULL');
$qb->setParameter('phone_type_fax', ContactPhone::FAX);
break;
case 'hasEmail':
$qb->andWhere('contact.email IS NOT NULL');
break;
}
}
}
}
return $qb;
}
