forms log
{ts '2024-10-10 17:21:02'} SQL
declare @ownerId int = 4839 ;
    declare @ownerType varchar(3) = 'MFR' ;

    with forms as (
        select f.formId, f.formName, f.publishDate, f.unpublishDate, 
		
				case when datediff( day, getDate(), f.unpublishDate ) < 0 then 'N' 
				     when datediff( day, f.publishDate, getDate() ) < 0 then 'N'
					 else 'Y' end as active,

				f.synopsis,
				confirmationMessageTitle, confirmationMessageText,  
				f.fieldDomain, f.fieldCategory,
				json_query( f.contactFieldMapping, '$' ) as contactFieldMapping, 

				cast(
					case when 1 = 2
						
							or json_value( f.contactFieldMapping, '$.contactEmail' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactFirstName' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactLastName' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyName' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyAddress1' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyAddress2' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyCity' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyState' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyZip' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactJobFunction' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactTitle' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactPhone' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactCell' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactCommPref' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactLangPref' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactLinkedIn' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactFacebook' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactTwitter' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactInstagram' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactTikTok' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyEmail' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyPhone' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyLinkedIn' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyFacebook' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyTwitter' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyInstagram' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyTikTok' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyYouTube' ) is not null
						
							or json_value( f.contactFieldMapping, '$.operatorSegment' ) is not null
						
							or json_value( f.contactFieldMapping, '$.operatorCuisine' ) is not null
						
							or json_value( f.contactFieldMapping, '$.operatorDistributorPref' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyType' ) is not null
						
						then 1
					else 0
					end	
				as bit ) as parseEntity,

				f.applicability,

				cast( case when f.applicability like '%OPR%' then 1 else 0 end  as bit ) isOperatorApplicable,
				cast( case when f.applicability like '%CDR%' then 1 else 0 end  as bit ) isDistributorApplicable,
				
				cast( case when f.applicability like '%FSPM%' then 1 else 0 end  as bit ) isGeneralApplicable,
				cast( case when f.applicability like '%SPL%' then 1 else 0 end  as bit ) isOpportunityApplicable,
				cast( case when f.applicability like '%CLL%' then 1 else 0 end  as bit ) isInteractionApplicable,
				cast( case when f.applicability like '%EML%' then 1 else 0 end  as bit ) isEmailCampaignApplicable,
				cast( case when f.applicability like '%BRO%' then 1 else 0 end  as bit ) isBrokerApplicable,
				cast( case when f.applicability like '%WWW%' then 1 else 0 end  as bit ) isWebsiteApplicable,

				coalesce( f.archive, 'N' ) as archive,
				
				case when exists ( select *
	                  from tbl_WRK_FormRecipients fr with (nolock)
	                 where fr.formId = f.formId
	                   and fr.ownerId = f.ownerId
	                   and fr.ownerType = f.ownerType
	                   and fr.recipientType in ( 'TO', 'APPROVER' )) then 'Y' else 'N' end as ready,
				case when exists ( select *
	                  from tbl_WRK_Fields wff with (nolock)
	                 where wff.ownerId = f.ownerId
	                   and wff.ownerType = f.ownerType
					   and wff.active = 'Y'
	                   and wff.category = f.fieldCategory ) then 'Y' else 'N' end as hasFields,
				f.ccSubmission
          from tbl_WRK_Forms f with (nolock)
         where f.ownerId = @ownerId
           and f.ownerType = @ownerType
    )
	
	,

	submissions as (


		select formId, count(*) submissionCount, max( submissionTime ) lastSubmissionTime 
			from tbl_WRK_FormSubmissions with (nolock)
			where ownerId = @ownerId
			and ownerType = @ownerType
  		group by formId

	),

	data as (

		select f.*, s.submissionCount, s.lastSubmissionTime
		  from forms f
		  left outer join submissions s
		    on s.formId = f.formId

	)


    
, data_preData as (

	
	select *
	  from data 
	where 

	(
	

	(
	[ready]
							= 'Y' 	
	)
and 

	(
	[isDistributorApplicable]
							= '1' 	
	)
	
	)



	)

	, data_data as (

		select * 
		       , count(*) over() as fseRowCount 
		  from data_preData
		  

	)



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


	order by formName
		
		
		 offset 0 rows fetch next 10000000 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

{ts '2024-10-10 17:52:02'} SQL
declare @ownerId int = 4839 ;
    declare @ownerType varchar(3) = 'MFR' ;

    with forms as (
        select f.formId, f.formName, f.publishDate, f.unpublishDate, 
		
				case when datediff( day, getDate(), f.unpublishDate ) < 0 then 'N' 
				     when datediff( day, f.publishDate, getDate() ) < 0 then 'N'
					 else 'Y' end as active,

				f.synopsis,
				confirmationMessageTitle, confirmationMessageText,  
				f.fieldDomain, f.fieldCategory,
				json_query( f.contactFieldMapping, '$' ) as contactFieldMapping, 

				cast(
					case when 1 = 2
						
							or json_value( f.contactFieldMapping, '$.contactEmail' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactFirstName' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactLastName' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyName' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyAddress1' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyAddress2' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyCity' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyState' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyZip' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactJobFunction' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactTitle' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactPhone' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactCell' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactCommPref' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactLangPref' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactLinkedIn' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactFacebook' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactTwitter' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactInstagram' ) is not null
						
							or json_value( f.contactFieldMapping, '$.contactTikTok' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyEmail' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyPhone' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyLinkedIn' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyFacebook' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyTwitter' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyInstagram' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyTikTok' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyYouTube' ) is not null
						
							or json_value( f.contactFieldMapping, '$.operatorSegment' ) is not null
						
							or json_value( f.contactFieldMapping, '$.operatorCuisine' ) is not null
						
							or json_value( f.contactFieldMapping, '$.operatorDistributorPref' ) is not null
						
							or json_value( f.contactFieldMapping, '$.companyType' ) is not null
						
						then 1
					else 0
					end	
				as bit ) as parseEntity,

				f.applicability,

				cast( case when f.applicability like '%OPR%' then 1 else 0 end  as bit ) isOperatorApplicable,
				cast( case when f.applicability like '%CDR%' then 1 else 0 end  as bit ) isDistributorApplicable,
				
				cast( case when f.applicability like '%FSPM%' then 1 else 0 end  as bit ) isGeneralApplicable,
				cast( case when f.applicability like '%SPL%' then 1 else 0 end  as bit ) isOpportunityApplicable,
				cast( case when f.applicability like '%CLL%' then 1 else 0 end  as bit ) isInteractionApplicable,
				cast( case when f.applicability like '%EML%' then 1 else 0 end  as bit ) isEmailCampaignApplicable,
				cast( case when f.applicability like '%BRO%' then 1 else 0 end  as bit ) isBrokerApplicable,
				cast( case when f.applicability like '%WWW%' then 1 else 0 end  as bit ) isWebsiteApplicable,

				coalesce( f.archive, 'N' ) as archive,
				
				case when exists ( select *
	                  from tbl_WRK_FormRecipients fr with (nolock)
	                 where fr.formId = f.formId
	                   and fr.ownerId = f.ownerId
	                   and fr.ownerType = f.ownerType
	                   and fr.recipientType in ( 'TO', 'APPROVER' )) then 'Y' else 'N' end as ready,
				case when exists ( select *
	                  from tbl_WRK_Fields wff with (nolock)
	                 where wff.ownerId = f.ownerId
	                   and wff.ownerType = f.ownerType
					   and wff.active = 'Y'
	                   and wff.category = f.fieldCategory ) then 'Y' else 'N' end as hasFields,
				f.ccSubmission
          from tbl_WRK_Forms f with (nolock)
         where f.ownerId = @ownerId
           and f.ownerType = @ownerType
    )
	
	,

	submissions as (


		select formId, count(*) submissionCount, max( submissionTime ) lastSubmissionTime 
			from tbl_WRK_FormSubmissions with (nolock)
			where ownerId = @ownerId
			and ownerType = @ownerType
  		group by formId

	),

	data as (

		select f.*, s.submissionCount, s.lastSubmissionTime
		  from forms f
		  left outer join submissions s
		    on s.formId = f.formId

	)


    
, data_preData as (

	
	select *
	  from data 
	where 

	(
	

	(
	[ready]
							= 'Y' 	
	)
and 

	(
	[isDistributorApplicable]
							= '1' 	
	)
	
	)



	)

	, data_data as (

		select * 
		       , count(*) over() as fseRowCount 
		  from data_preData
		  

	)



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


	order by formName
		
		
		 offset 0 rows fetch next 10000000 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