New feature to tag people - Write method Search::flood_with_connections(s, n, rest_days)

[ExpandedVenture]

Method Header

    # Return a dataset with records of the `profile` table who are already a 1st degree connection of any active `lnuser`.
    #
    # Parameters:
    # - s: Mandatory. The search to flood.
    # - n: Mandatory. How many results to add.
    # - lead_rest_days: Default value is `15`. Number of days the leads shouldn't be reached after eithet receive a connection_requests, a message, or a mention in a post.
    # - account_rest_days: Default value is `15`. Number of days an account can view the same post of the same client.
    # - work_with_public_crowd: Default value is `true`. If `true`, the reach people connected with any account with `isnull(disabled_for_heijunka,0)=0`. If `false`, then work with the network belonging the `client` owner of the search.
    #
    # Conditions:
    # - Search can't target by any other filter than job position, industry and location. Location filters must be countries or USA states only.
    # - Search can't have previous results (it must be virgin).
    # - Search must by `type==1` (flood-with-connections search)
    #  
    def self.dataset_flood_with_linkedin_connections(s, n, lead_rest_days=15, account_rest_days=15, work_with_public_crowd=true)

Query

use euler;
go

select 
    top 300 -- n: Mandatory. How many results to add.
    p.id, p.name, p.location, p.industry, p.headline, u.username
from [profile] p with (nolock index(IX_profile__industry__headline__location))
join drl_minutely_stats x with (nolock) on ( p.id=x.id_profile and x.ininvite_hit_time is not null )
join lnuser u with (nolock index(IX_lnuser__disabled_for_heijunka)) on u.id=x.id_lnuser
join search s with (nolock) on s.id=x.id_search

-- not deleted account
where u.delete_time is null 

-- this lead already doesn't exist
and p.name not in (
    select q.name
    from result r with (nolock)
    join [profile] q with (nolock) on q.id=r.id_profile
    where r.id_search='0DECD86C-21A1-41A8-905B-4CE5E7845393' 
)

-- filter by industry
and p.industry in (
    'Financial Services'
)

-- filter by job position
and (
    -- looking at the headline
    p.headline like '%CEO%' OR
    p.headline like '%Founder%' OR
    p.headline like '%Owner%' OR
    -- looking at the job positions
    s.id IN (
        select k.id_search
        from searchkeyword k with (nolock)
        where isnull(k.apply_to_current_job_title, 0) = 1
        and isnull(k.is_negative, 0) = 0
        and (
            k.keyword like '%CEO%' OR
            k.keyword like '%Founder%' OR
            k.keyword like '%Owner%'
        )

    )
)

-- filter by location (remember: country and/or US states only)
and (
    -- filter Canada
    p.location like '%, Canada%' OR
    -- filter United States
    (
        p.location like '%United States%' or
        not exists ( select y.id from lncountry y with (nolock) where p.location like '%'+y.name+'%' )
    ) OR
    -- filter New York state
    (
        p.location like '%New York%' and
        not exists ( select y.id from lncountry y with (nolock) where p.location like '%'+y.name+'%' )
    )
)

-- lead_rest_days: Number of days the leads shouldn't be reached after eithet receive a connection_requests, a message, or a mention in a post.
and not exists (
    select q.id
    from [profile] q with (nolock index(IX_profile__name))
    join result r with (nolock) on q.id=r.id_profile
    join lnchat h with (nolock) on r.id=h.id_result
    where q.name=p.name
    and h.create_time > dateadd(dd, -15, getdate())
)

-- lead_rest_days: Number of days the leads shouldn't be reached after eithet receive a connection_requests, a message, or a mention in a post.
and not exists (
    select q.id
    from [profile] q with (nolock index(IX_profile__name))
    join result r with (nolock) on q.id=r.id_profile
    join nbmention h with (nolock) on r.id=h.id_result
    join nbcommand c with (nolock) on c.id=h.id_command
    where q.name=p.name
    and c.create_time > dateadd(dd, -15, getdate())
)

-- account_rest_days: Number of days an account can view the same post of the same client.
and not exists (
    select q.id
    from lnuser q with (nolock)
    join lnchat h with (nolock) on q.id=h.id_lnuser
    where q.id=u.id
    and h.create_time > dateadd(dd, -15, getdate())
)

-- work_with_public_crowd: If `true`, the reach people connected with any account with `isnull(disabled_for_heijunka,0)=0`. 
and isnull(u.disabled_for_heijunka,0)=0

/*
 * not allowed feature yet
 *
-- work_with_public_crowd: If `false`, then work with the network belonging the `client` owner of the search.
and x.id_client = 'B1D94DDD-D9FA-4BB3-AD6A-9B65AB161295' -- Expanded Venture 2
*/

-- account must be allowed_for_crowdtrust
and isnull(u.allowed_for_crowdtrust,0) = 1
;

[ExpandedVenture]

Done.