1fsOperators log
{ts '2024-11-20 13:56:20'} SQL
declare @ownerId int = ?;
    declare @ownerType varchar(3) = ?;

    with clientSegmentsRaw as (

		select clientSegId, segmentId, row_number() over ( partition by segmentId order by segmentPath ) ranking
		  from tbl_OPR_ClientSegments cs with (nolock)
		  where cs.ownerId = @ownerId
		  and cs.fsl_tablecode = @ownerType
		  and segmentId > 0

	),

    clientSegments as (
        select clientSegId, segmentId
          from clientSegmentsRaw with (nolock)
          where ranking = 1
    ),


    data as (

        select c.cho_id, c.cho_type, c.cho_name, c.cho_address, c.cho_city, c.cho_state, c.cho_zipCode, c.cho_countryId, 
            c.cho_segmentId, nullif( c.cho_url, '' ) cho_url, nullif( c.cho_phone, '' ) cho_phone,
        o.operatorId, t.territoryId, t.name as territoryName, t.territoryPath, o.crmActive,

        case when o.operatorId is not null then o.oprSegment else cs.clientSegId end clientSegId

        from tbl_cho_operators c with (nolock)
        
        left outer join tbl_OPR_ClientOperators o with (nolock)
            on o.ownerId = @ownerId
            and o.fsltablecode = @ownerType
            and o.fsl_choId = c.cho_id
            and o.territoryId > 0
        left outer join tbl_TER_Territories t with (nolock)
            on t.ownerId = o.ownerId
            and t.fsl_tablecode = o.fsltablecode
            and t.territoryId = o.territoryId
        left outer join clientSegments cs with (nolock)
           on cs.segmentId = c.cho_segmentId
        where coalesce(c.cho_acctclosed,'0') <> '1'
          and c.cho_name not like 'Insufficient%'

        
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where 

	(
	

	(
	[cho_name]
							
					like '%Hal Smith Restaurant Group%' 	
	)
and 

	(
	[cho_address]
							
					like '%3101 W Tecumseh Rd%' 	
	)
and 

	(
	[cho_city]
							
					like '%Norman%' 	
	)
and 

	(
	[cho_state]
							= 'OK' 	
	)
and 

	(
	[cho_zipCode]
							
					like '73072%' 	
	)
and 

	(
	[cho_countryId]
							= '27' 	
	)
and 

	(
	

	(
	[cho_type]
							= 'O' 	
	)
or 

	(
	[cho_type]
							= 'I' 	
	)
	
	)
	
	)



	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by cho_name
		
		
		 offset 0 rows fetch next 25 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 54
-- MFR

{ts '2024-11-20 13:59:04'} SQL
declare @ownerId int = ?;
    declare @ownerType varchar(3) = ?;

    with clientSegmentsRaw as (

		select clientSegId, segmentId, row_number() over ( partition by segmentId order by segmentPath ) ranking
		  from tbl_OPR_ClientSegments cs with (nolock)
		  where cs.ownerId = @ownerId
		  and cs.fsl_tablecode = @ownerType
		  and segmentId > 0

	),

    clientSegments as (
        select clientSegId, segmentId
          from clientSegmentsRaw with (nolock)
          where ranking = 1
    ),


    data as (

        select c.cho_id, c.cho_type, c.cho_name, c.cho_address, c.cho_city, c.cho_state, c.cho_zipCode, c.cho_countryId, 
            c.cho_segmentId, nullif( c.cho_url, '' ) cho_url, nullif( c.cho_phone, '' ) cho_phone,
        o.operatorId, t.territoryId, t.name as territoryName, t.territoryPath, o.crmActive,

        case when o.operatorId is not null then o.oprSegment else cs.clientSegId end clientSegId

        from tbl_cho_operators c with (nolock)
        
        left outer join tbl_OPR_ClientOperators o with (nolock)
            on o.ownerId = @ownerId
            and o.fsltablecode = @ownerType
            and o.fsl_choId = c.cho_id
            and o.territoryId > 0
        left outer join tbl_TER_Territories t with (nolock)
            on t.ownerId = o.ownerId
            and t.fsl_tablecode = o.fsltablecode
            and t.territoryId = o.territoryId
        left outer join clientSegments cs with (nolock)
           on cs.segmentId = c.cho_segmentId
        where coalesce(c.cho_acctclosed,'0') <> '1'
          and c.cho_name not like 'Insufficient%'

        
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where 

	(
	

	(
	[cho_name]
							
					like '%Hal Smith Restaurant Group%' 	
	)
and 

	(
	[cho_address]
							
					like '%3101 W Tecumseh Rd%' 	
	)
and 

	(
	[cho_city]
							
					like '%Norman%' 	
	)
and 

	(
	[cho_state]
							= 'OK' 	
	)
and 

	(
	[cho_zipCode]
							
					like '73072%' 	
	)
and 

	(
	[cho_countryId]
							= '27' 	
	)
and 

	(
	[cho_type]
							= 'X' 	
	)
	
	)



	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by cho_name
		
		
		 offset 0 rows fetch next 25 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 54
-- MFR

{ts '2024-11-20 14:00:35'} SQL
declare @ownerId int = ?;
    declare @ownerType varchar(3) = ?;

    with clientSegmentsRaw as (

		select clientSegId, segmentId, row_number() over ( partition by segmentId order by segmentPath ) ranking
		  from tbl_OPR_ClientSegments cs with (nolock)
		  where cs.ownerId = @ownerId
		  and cs.fsl_tablecode = @ownerType
		  and segmentId > 0

	),

    clientSegments as (
        select clientSegId, segmentId
          from clientSegmentsRaw with (nolock)
          where ranking = 1
    ),


    data as (

        select c.cho_id, c.cho_type, c.cho_name, c.cho_address, c.cho_city, c.cho_state, c.cho_zipCode, c.cho_countryId, 
            c.cho_segmentId, nullif( c.cho_url, '' ) cho_url, nullif( c.cho_phone, '' ) cho_phone,
        o.operatorId, t.territoryId, t.name as territoryName, t.territoryPath, o.crmActive,

        case when o.operatorId is not null then o.oprSegment else cs.clientSegId end clientSegId

        from tbl_cho_operators c with (nolock)
        
        left outer join tbl_OPR_ClientOperators o with (nolock)
            on o.ownerId = @ownerId
            and o.fsltablecode = @ownerType
            and o.fsl_choId = c.cho_id
            and o.territoryId > 0
        left outer join tbl_TER_Territories t with (nolock)
            on t.ownerId = o.ownerId
            and t.fsl_tablecode = o.fsltablecode
            and t.territoryId = o.territoryId
        left outer join clientSegments cs with (nolock)
           on cs.segmentId = c.cho_segmentId
        where coalesce(c.cho_acctclosed,'0') <> '1'
          and c.cho_name not like 'Insufficient%'

        
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where 

	(
	

	(
	[cho_name]
							
					like '%Hal Smith Restaurant Group%' 	
	)
and 

	(
	[cho_address]
							
					like '%3101 W Tecumseh Rd%' 	
	)
and 

	(
	[cho_city]
							
					like '%Norman%' 	
	)
and 

	(
	[cho_state]
							= 'OK' 	
	)
and 

	(
	[cho_zipCode]
							
					like '73072%' 	
	)
and 

	(
	[cho_countryId]
							= '27' 	
	)
and 

	(
	

	(
	[cho_type]
							= 'O' 	
	)
or 

	(
	[cho_type]
							= 'I' 	
	)
	
	)
	
	)



	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by cho_name
		
		
		 offset 0 rows fetch next 25 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 54
-- MFR

{ts '2024-11-20 14:04:38'} SQL
declare @ownerId int = ?;
    declare @ownerType varchar(3) = ?;

    with clientSegmentsRaw as (

		select clientSegId, segmentId, row_number() over ( partition by segmentId order by segmentPath ) ranking
		  from tbl_OPR_ClientSegments cs with (nolock)
		  where cs.ownerId = @ownerId
		  and cs.fsl_tablecode = @ownerType
		  and segmentId > 0

	),

    clientSegments as (
        select clientSegId, segmentId
          from clientSegmentsRaw with (nolock)
          where ranking = 1
    ),


    data as (

        select c.cho_id, c.cho_type, c.cho_name, c.cho_address, c.cho_city, c.cho_state, c.cho_zipCode, c.cho_countryId, 
            c.cho_segmentId, nullif( c.cho_url, '' ) cho_url, nullif( c.cho_phone, '' ) cho_phone,
        o.operatorId, t.territoryId, t.name as territoryName, t.territoryPath, o.crmActive,

        case when o.operatorId is not null then o.oprSegment else cs.clientSegId end clientSegId

        from tbl_cho_operators c with (nolock)
        
        left outer join tbl_OPR_ClientOperators o with (nolock)
            on o.ownerId = @ownerId
            and o.fsltablecode = @ownerType
            and o.fsl_choId = c.cho_id
            and o.territoryId > 0
        left outer join tbl_TER_Territories t with (nolock)
            on t.ownerId = o.ownerId
            and t.fsl_tablecode = o.fsltablecode
            and t.territoryId = o.territoryId
        left outer join clientSegments cs with (nolock)
           on cs.segmentId = c.cho_segmentId
        where coalesce(c.cho_acctclosed,'0') <> '1'
          and c.cho_name not like 'Insufficient%'

        
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where 

	(
	

	(
	[cho_name]
							
					like '%Hal Smith Restaurant Group%' 	
	)
and 

	(
	[cho_address]
							
					like '%3101 W Tecumseh Rd%' 	
	)
and 

	(
	[cho_city]
							
					like '%Norman%' 	
	)
and 

	(
	[cho_zipCode]
							
					like '73072%' 	
	)
and 

	(
	[cho_countryId]
							= '27' 	
	)
and 

	(
	

	(
	[cho_type]
							= 'O' 	
	)
or 

	(
	[cho_type]
							= 'I' 	
	)
	
	)
	
	)



	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by cho_name
		
		
		 offset 0 rows fetch next 25 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 54
-- MFR