BIRDEYE-42553

Whitelabel Reseller users

      select distinct
   u.first_name as firstName,
   u.last_name as lastName,
   u.email_id as email,
   u.phone as phone,
   b.business_id as businessId,
   b.name as businessName 
from
   user u 
   inner join
      business_user bu 
      on bu.user_id = u.id 
   inner join
      business b 
      on b.id = bu.business_id 
   inner join
      business_billing bb 
      on b.id = bb.business_id 
where
   u.status = 'active' 
   and bu.is_visible = 1 
   and bu.user_role <> 'employee'
   and b.activation_status = 'paid' 
   and b.reseller_id = 4 
   and b.account_type = 'Whitelabel'
  and bb.payment_status not REGEXP 'expired' 
   and NOT (u.email_id REGEXP 'birdeye.com|bazaarify.com|dtabuz.com|mailinator.com|test|automation|performance') 
   and b.status = 'active' 
   and b.closed = 0 
   and b.zoho_id is not null;
----------------------------------------------------------------------------------------------------------------------------------------
Cobranded Reseller users

select distinct
   u.first_name as firstName,
   u.last_name as lastName,
   u.email_id as email,
   u.phone as phone,
   b.business_id as businessId,
   b.name as businessName 
from
   user u 
   inner join
      business_user bu 
      on bu.user_id = u.id 
   inner join
      business b 
      on b.id = bu.business_id 
   inner join
      business_billing bb 
      on b.id = bb.business_id 
where
   u.status = 'active' 
   and bu.is_visible = 1 
   and bu.user_role <> 'employee'
   and b.activation_status = 'paid' 
   and b.reseller_id = 3 
   and b.account_type = 'Cobranded'
  and bb.payment_status not REGEXP 'expired' 
   and NOT (u.email_id REGEXP 'birdeye.com|bazaarify.com|dtabuz.com|mailinator.com|test|automation|performance') 
   and b.status = 'active' 
   and b.closed = 0 
   and b.zoho_id is not null;
----------------------------------------------------------------------------------------------------------------------------------------
Single Location Direct Business users

	   select distinct
   u.first_name as firstName,
   u.last_name as lastName,
   u.email_id as email,
   u.phone as phone,
   b.business_id as businessId,
   b.name as businessName 
from
   user u 
   inner join
      business_user bu 
      on bu.user_id = u.id 
   inner join
      business b 
      on b.id = bu.business_id 
   inner join
      business_billing bb 
      on b.enterprise_id = bb.business_id 
where
   u.status = 'active' 
   and bu.is_visible = 1 
   and bu.user_role <> 'employee'
   and b.activation_status = 'paid' 
   and b.reseller_id = 1 
   and b.account_type = ''
   and bb.payment_status not REGEXP 'expired' 
   and NOT (u.email_id REGEXP 'birdeye.com|bazaarify.com|dtabuz.com|mailinator.com|test|automation|performance') 
   and b.status = 'active' 
   and b.closed = 0 
   and b.zoho_id is not null;
----------------------------------------------------------------------------------------------------------------------------------------	
Multi Location Direct Business users

   select distinct
   u.first_name as firstName,
   u.last_name as lastName,
   u.email_id as email,
   u.phone as phone,
   e.business_id as businessId,
   e.name as businessName
from
   user u 
   inner join
      business_user bu 
      on bu.user_id = u.id 
   inner join
      business b 
      on b.id = bu.business_id 
   inner join
      business e 
      on e.id = b.enterprise_id 
   inner join
      business_billing bb 
      on b.enterprise_id = bb.business_id 
where
   u.status = 'active' 
   and bu.is_visible = 1 
   and bu.user_role <> 'employee'
   and b.activation_status = 'paid' 
   and bb.payment_status not REGEXP 'expired' 
   and NOT (u.email_id REGEXP 'birdeye.com|bazaarify.com|dtabuz.com|mailinator.com|test|automation|performance') 
   and b.status = 'active' 
   and b.closed = 0 and e.closed = 0
   and b.account_type = 'Direct' and e.reseller_id = 2
   and b.zoho_id is not null and e.zoho_id is not null
UNION
select distinct
   u.first_name as firstName,
   u.last_name as lastName,
   u.email_id as email,
   u.phone as phone,
   e.business_id as businessId,
   e.name as businessName
from
   user u 
   inner join
      business_user bu 
      on bu.user_id = u.id 
   inner join
      business b 
      on b.id = bu.business_id 
   inner join
      business e 
      on e.id = b.enterprise_id 
   inner join
      business_billing bb 
      on b.id = bb.business_id 
where
   u.status = 'active' 
   and bu.is_visible = 1 
   and bu.user_role <> 'employee'
   and b.activation_status = 'paid' 
   and bb.payment_status not REGEXP 'expired' 
   and NOT (u.email_id REGEXP 'birdeye.com|bazaarify.com|dtabuz.com|mailinator.com|test|automation|performance') 
   and b.status = 'active' 
   and b.closed = 0 and e.closed = 0
   and b.account_type = 'Direct' and e.reseller_id = 2
   and b.zoho_id is not null and e.zoho_id is not null