Třídová akce

Kód:

8

Tělo:

Select 3, Email from crmcampaignaudiences CA
  left join CRMCampaignes C on (C.ID=CA.parent_id)
  left join CRMCampaignTypes CT on (CT.ID=C.CampaignType_ID)
  where 
    (CA.Parent_ID = :CampaignID) and 
    (CT.Channel = 0) and
    (CA.AudienceState <> 3) and
    (CA.email <> '')
  group by CA.email
  having count(CA.ID) > 1
union all
Select 2, P.lastname  || ' ' || P.firstname from crmcampaignaudiences CA
  left join Persons P on (P.ID = CA.Person_ID)
  where 
    (CA.Parent_ID = :CampaignID) and (
    CA.TargetAddressType = 2) and 
    (CA.Firm_ID is null) and
    (CA.AudienceState <> 3)
  group by P.lastname  || ' ' || P.firstname
  having count(CA.ID) > 1
union all
Select 2, F.Name || ' - ' || P.lastname || ' ' || P.FirstName  from crmcampaignaudiences CA
  left join Firms F on (F.ID = CA.Firm_ID)
  left join Persons P on (P.ID = CA.Person_ID)
  where 
    (CA.parent_id = :CampaignID) and 
    (CA.targetaddresstype = 2) and 
    (not CA.Firm_ID is null) and
    (CA.AudienceState <> 3)
  group by F.Name || ' - ' || P.lastname || ' ' || P.FirstName
  having count(CA.ID) > 1
union all
Select 1, F.name || ' - ' || FO.Name from crmcampaignaudiences CA
  left join Firms F on (F.ID = CA.Firm_ID)
  left join FirmOffices FO on (FO.ID = CA.FirmOffice_ID)
  where 
    (CA.Parent_ID = :CampaignID) and 
    (CA.TargetAddressType = 1) and
    (CA.AudienceState <> 3)
  group by F.name || ' - ' || FO.Name
  having count(CA.ID) > 1
union all
Select 0, F.Name from crmcampaignaudiences CA
  left join Firms F on (F.ID = CA.Firm_ID)
  where 
    (CA.Parent_ID = :CampaignID) and 
    (CA.TargetAddressType = 0) and
    (CA.AudienceState <> 3)
  group by F.Name
  having count(CA.ID) > 1

Generated by ABRA Software a.s. 27.10.2021 16:34:54