-- // .query('select distinct e from rtneo$ContragentRealEstate r join rtneo$Contragent e where r.contragent = e and (r.category.isLiving is null or r.category.isLiving=false)

-- and not r.realEstate.name = \'Земельный участок\' and size(e.contracts)=0 and not e.id in (select t.entityId from rtneo$EntityesTemporarySet t) order by e.id')

-- // .query('select distinct e from rtneo$ContragentRealEstate r join rtneo$Contragent e on r.contragent = e where ((r.category.isLiving is null or r.category.isLiving=false)

-- and not r.realEstate.name = \'Земельный участок\' and size(e.contracts)=0 and (r.validityFrom is null or r.validityFrom >= \'2019-01-01\') and not r.excludeFromAccounting = true) or (e.id in (select t.entityId from rtneo$EntityesTemporarySet t where not t.setName = \'CreatedContractAllRecalc30-09\')) order by e.id EXCEPT select distinct e from rtneo$Contragent e join rtneo$EntityesTemporarySet t where e.id = t.entityId and t.setName = \'CreatedContractAllRecalc30-09\'')

-- .query('select c from rtneo$Contragent c where c.id = \'0033395b-c0da-b70f-8e93-fc437b5bd862\'')

-- // .query('select distinct e from rtneo$ContragentRealEstate r join rtneo$Contragent e on r.contragent = e where (((r.category.isLiving is null or r.category.isLiving=false)

-- and not r.realEstate.name = \'Земельный участок\' and size(e.contracts)=0 and (r.validityFrom is null or r.validityFrom >= \'2019-01-01\')

-- and not r.excludeFromAccounting = true) or (e.id in (select t.entityId from rtneo$EntityesTemporarySet t where not t.setName = \'CreatedContractAllRecalc30-09\')))

-- and not e.id in (select t.entityId from rtneo$EntityesTemporarySet t where t.setName = \'CreatedContractAllRecalc30-09\')')

select count(rc)

from rtneo_contragent_real_estate cre

left join rtneo_contragent rc ON rc.id = cre.contragent_id

left join rtneo_contract rct ON rct.contragent_id = rc.id

join rtneo_real_estate re ON re.id = cre.real_estate_id

join rtneo_real_estate_category rec ON rec.id = cre.category_id

where rc.delete_ts is null and rct.delete_ts is null and cre.delete_ts is null

and rct is null

and (rec.is_living is null or rec.is_living = false)

and not re.name = 'Земельный участок'

or rc.id in (select entity_id from rtneo_entityes_temporary_set)

-- order by rc.id

============================================================================================

select count(distinct contragent) from (

select rc

from rtneo_contragent_real_estate cre

left join rtneo_contragent rc ON rc.id = cre.contragent_id

left join rtneo_contract rct ON rct.contragent_id = rc.id

join rtneo_real_estate re ON re.id = cre.real_estate_id

join rtneo_real_estate_category rec ON rec.id = cre.category_id

where rc.delete_ts is null and rct.delete_ts is null and cre.delete_ts is null

and rct is null

and (rec.is_living is null or rec.is_living = false)

and not re.name = 'Земельный участок'

union

select rc from rtneo_contragent rc join rtneo_entityes_temporary_set ts on rc.id = ts.entity_id where not ts.entity_name = 'ContragentRealEstate') as contragent

-- count(distinct contragent) = 32265

insert into rtneo_entityes_temporary_set (entity_id, id, entity_name, set_name, version, create_ts)

(select distinct contragent.id, newid(), 'Contragent', 'emptyAndPrev04-10-20CreatedContract', 1, now() from (

select rc.id as id

from rtneo_contragent_real_estate cre

left join rtneo_contragent rc ON rc.id = cre.contragent_id

left join rtneo_contract rct ON rct.contragent_id = rc.id

join rtneo_real_estate re ON re.id = cre.real_estate_id

join rtneo_real_estate_category rec ON rec.id = cre.category_id

where rc.delete_ts is null and rct.delete_ts is null and cre.delete_ts is null

and rct is null

and (rec.is_living is null or rec.is_living = false)

and not re.name = 'Земельный участок'

union

select rc.id as id from rtneo_contragent rc join rtneo_entityes_temporary_set ts on rc.id = ts.entity_id where not ts.entity_name = 'ContragentRealEstate') contragent)

//С одним договором, не заходили

select count(distinct contragents.id) from (

select rc.id as id, rc.user_id as user, rc as contragent, count(distinct rct.id) as contracts

from rtneo_contragent_real_estate cre

left join rtneo_contragent rc ON rc.id = cre.contragent_id

left join rtneo_contract rct ON rct.contragent_id = rc.id

join rtneo_real_estate re ON re.id = cre.real_estate_id

join rtneo_real_estate_category rec ON rec.id = cre.category_id

where rc.delete_ts is null and rct.delete_ts is null and cre.delete_ts is null

and (rec.is_living is null or rec.is_living = false)

and not re.name = 'Земельный участок' group by rc.id having count(distinct rct.id) = 1) as contragents left join sec_session_log sess on contragents.user = sess.user_id where sess is null

--count 4213

//С одним договором, заходили

select count(distinct contragents.id) from (

select rc.id as id, rc.user_id as user, rc as contragent, count(distinct rct.id) as contracts

from rtneo_contragent_real_estate cre

left join rtneo_contragent rc ON rc.id = cre.contragent_id

left join rtneo_contract rct ON rct.contragent_id = rc.id

join rtneo_real_estate re ON re.id = cre.real_estate_id

join rtneo_real_estate_category rec ON rec.id = cre.category_id

where rc.delete_ts is null and rct.delete_ts is null and cre.delete_ts is null

and (rec.is_living is null or rec.is_living = false)

and not re.name = 'Земельный участок' group by rc.id having count(distinct rct.id) = 1) as contragents left join sec_session_log sess on contragents.user = sess.user_id where not sess is null

--count 1062

insert into rtneo_entityes_temporary_set (entity_id, id, entity_name, set_name, version, create_ts)

(select distinct contragent.id1, newid(), 'Contragent', 'oneIsLogin04-10-20CreatedContract', 1, now() from (

select distinct contragents.id as id1 from (

select rc.id as id, rc.user_id as user, rc as contragent, count(distinct rct.id) as contracts

from rtneo_contragent_real_estate cre

left join rtneo_contragent rc ON rc.id = cre.contragent_id

left join rtneo_contract rct ON rct.contragent_id = rc.id

join rtneo_real_estate re ON re.id = cre.real_estate_id

join rtneo_real_estate_category rec ON rec.id = cre.category_id

where rc.delete_ts is null and rct.delete_ts is null and cre.delete_ts is null

and (rec.is_living is null or rec.is_living = false)

and not re.name = 'Земельный участок' group by rc.id having count(distinct rct.id) = 1) as contragents left join sec_session_log sess on contragents.user = sess.user_id where not sess is null) as contragent)

insert into rtneo_entityes_temporary_set (entity_id, id, entity_name, set_name, version, create_ts)

(select distinct contragent.id1, newid(), 'Contragent', 'oneIsNoLogin04-10-20CreatedContract', 1, now() from (

select distinct contragents.id as id1 from (

select rc.id as id, rc.user_id as user, rc as contragent, count(distinct rct.id) as contracts

from rtneo_contragent_real_estate cre

left join rtneo_contragent rc ON rc.id = cre.contragent_id

left join rtneo_contract rct ON rct.contragent_id = rc.id

join rtneo_real_estate re ON re.id = cre.real_estate_id

join rtneo_real_estate_category rec ON rec.id = cre.category_id

where rc.delete_ts is null and rct.delete_ts is null and cre.delete_ts is null

and (rec.is_living is null or rec.is_living = false)

and not re.name = 'Земельный участок' group by rc.id having count(distinct rct.id) = 1) as contragents left join sec_session_log sess on contragents.user = sess.user_id where sess is null) as contragent)

36225