From LeonWiki!

Stored Procedures

Synchronizing Contacts with company account manager value

Synchronizes the Account Manager value for each company with all the contacts for that company.

 
alter PROCEDURE [dbo].[synchronize_contact_field_with_company_account_manager]
	-- Add the parameters for the stored procedure here
	@clientid varchar(60), @acctMgrName varchar(60), @target_type_id int
AS
BEGIN
declare @mytran varchar(20)
select @mytran = 'mytran'

begin transaction @mytran

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @creatorid varchar(60), 
			@contact_index	int, 
			@contact_count	int, 
			@contact_number int, 
			@codeid int;
	select @creatorid	= user

	/* We know the client_id and the account manager name, we don't know what the code_id is for the account manager value (employee name). We get
		this value by doing the following select: 
	*/
	select @codeid = (select code_id from symmetrysql.dbo.amgr_user_field_defs_tbl where @acctMgrName=description and type_id=@target_type_id)
	if @codeid = null
		begin
			rollback transaction @mytran
			return
		end
	select distinct contact_number into #contactRecords from dbo.amgr_client_tbl
		where
			client_id=@clientid
		and
			contact_number>0
		and
			contact_number<60000
		order by contact_number asc

	select @contact_count = count(*) from #contactRecords

	insert into dbo.debug_log(contact_number,client_id,codeId,msg,last_edit,type_id)
		values(@contact_count,@clientid,@codeid,@acctMgrName+'=AcctMgr: @contact_count,@client_id,codeid',getdate(),@target_type_id)

	-- delete the existing category records, since we are recreating below, based on the company settings

	delete from amgr_user_fields_tbl
		where
			client_id=@clientid
		and
			contact_number!=0
		and
			(type_id=@target_type_id or type_id=null)

	set @contact_index = 0

	while @contact_index < @contact_count
		begin
			set rowcount @contact_index
			select @contact_number = contact_number from #contactRecords
			/* insert the new record */
			--insert into dbo.debug_log(contact_number,client_id,codeId,msg,last_edit,type_id)
			--	values(@contact_number,@clientid,@codeid,@acctMgrName+'=accountMgr,stored_proc: @contact_number,@client_id,@codeid',getdate(),@target_type_id)

			insert into AMGR_User_Fields_Tbl 
			(
				Client_Id, 
				Contact_Number, 
				[Type_Id], 
				Code_Id, 
				Last_Code_Id, 
				AlphaNumericCol, 
				Creator_Id, 
				NumericCol,
				Create_Date, 
				mmddDate, 
				Modified_By_Id,
				last_modify_date
			)
			values(@clientid, @contact_number, @target_type_id, @codeid, 0, '', @creatorid, 0, GETDATE(), '', @creatorid, getdate())
			set @contact_index = @contact_index + 1

		end

		drop table #contactRecords
		--drop table #companyRecords

	commit transaction @mytran
END
GO

Calling client procedure

use symmetrySQL

declare @c_count int, @index int, @cid varchar(50), @target_type_id int, @acctMgrName varchar(60);
set @target_type_id=234 -- : 234 (contact account manager) : 57998 (lead status) : 60030 (category)
set @index=0

/* get the companies that have account manager field set */
select cl.client_id, res.name into #company 
	from 
		symmetrysql.dbo.amgr_client_tbl cl, symmetrysql.dbo.amgr_resources res 
	where cl.assigned_to=res.resource_id

select @c_count = count(*) from #company

alter table AMGR_User_Fields_Tbl disable trigger all

while @index < @c_count
	begin
		set rowcount @index
		select @cid = client_id from #company
		select @acctMgrName = name from #company
	
		insert into dbo.debug_log(contact_number,client_id,codeId,msg,last_edit,type_id)
			values(@index,@cid,0,@acctMgrName+'=AccountMgr,@index,@cid,0,@acctMgrName',getdate(),@target_type_id)

		exec dbo.synchronize_contact_field_with_company_account_manager @cid, @acctMgrName, @target_type_id

		set @index = @index + 1
	end

set rowcount 0

drop table #company

alter table AMGR_User_Fields_Tbl enable trigger all

Synchronizing company category to contacts

Takes a client_id and gathers all the company records (contact_number=0) to determine what, if any, category values have been set. For each category value, create a contact record with that value if it doesn't already exist.

 alter PROCEDURE [dbo].[synchronize_category]
	-- Add the parameters for the stored procedure here
	@clientid varchar(60) ,@target_type_id int
AS

declare @mytran varchar(20)
select @mytran = 'mytran'

begin transaction @mytran

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @creatorid varchar(60), 
			@company_index	int, 
			@contact_index	int, 
			@company_count	int, 
			@contact_count	int, 
			@contact_number int, 
			@codeid int;
	select @creatorid	= user

	/*
		Select company records for passed in client_id
	*/

	select * into #companyRecords from dbo.amgr_user_fields_tbl
		where
			client_id=@clientid
		and
			type_id=@target_type_id
		and
			contact_number=0

	set @company_index=0

	select @company_count = count(*) from #companyRecords

	insert into dbo.debug_log(contact_number,client_id,codeId,msg,last_edit,type_id)
		values(@company_count,'0',0,'sp: @company_count,0,0',getdate(),@target_type_id)


	select distinct contact_number into #contactRecords from dbo.amgr_client_tbl
		where
			client_id=@clientid
		and
			contact_number>0
		and
			contact_number<6000

		order by contact_number asc

	select @contact_count = count(*) from #contactRecords

	insert into dbo.debug_log(contact_number,client_id,codeId,msg,last_edit,type_id)
		values(@contact_count,@clientid,0,'sp: @contact_count,@client_id,@target_type_id,@codeid',getdate(),@target_type_id)

	-- delete the existing category records, since we are recreating below, based on the company settings

	delete from amgr_user_fields_tbl
		where
			client_id=@clientid
		and
			contact_number!=0
		and
			(type_id=@target_type_id or type_id=null)


	insert into dbo.debug_log(contact_number,client_id,codeId,msg,last_edit,type_id)
		values(@contact_count,0,0,'sp: @contact_count,0,0',getdate(),@target_type_id)

	while @company_index < @company_count
		begin
			/* get first company record*/
			set rowcount @company_index

			select @codeid = code_id from #companyRecords

			set @contact_index=0

			while @contact_index < @contact_count
				begin
					set rowcount @contact_index
					select @contact_number = contact_number from #contactRecords
					/* insert the new record */
					insert into dbo.debug_log(contact_number,client_id,codeId,msg,last_edit,type_id)
						values(@contact_number,@clientid,@codeid,'sp: @contact_number,@client_id,@target_type_id,@codeid',getdate(),@target_type_id)

					insert into AMGR_User_Fields_Tbl 
					(
						Client_Id, 
						Contact_Number, 
						[Type_Id], 
						Code_Id, 
						Last_Code_Id, 
						AlphaNumericCol, 
						Creator_Id, 
						NumericCol,
						Create_Date, 
						mmddDate, 
						Modified_By_Id,
						last_modify_date
					)
					values(@clientid, @contact_number, @target_type_id, @codeid, 0, '', @creatorid, 0, GETDATE(), '', @creatorid, getdate())

					set @contact_index = @contact_index + 1

				end
				set @company_index = @company_index + 1
		end

		drop table #contactRecords
		drop table #companyRecords

	commit transaction @mytran

Calling Client procedure

use [symmetrySQL]

select distinct client_id into #company from amgr_client_tbl
declare @c_count int, @index int, @cid varchar(50);

declare @target_type_id int;
set @target_type_id=60030

set @index=0
select @c_count = count(*) from #company

alter table AMGR_User_Fields_Tbl disable trigger all

while @index < @c_count
begin
	set rowcount @index
	select @cid = client_id from #company
	exec dbo.synchronize_category @cid, @target_type_id
	set @index = @index + 1
end

set rowcount 0

drop table #company

alter table AMGR_User_Fields_Tbl enable trigger all