--specifieke updates voor LAKESIDE
set nocount on
DECLARE @Num INT, @Comp VARCHAR(100), @Qry VARCHAR(8000), @TableObject VARCHAR(200), @CodePosition VARCHAR(100)
	,@Xerr INT, @nOTES varchar(8000), @SqlTableName VARCHAR(200)

DECLARE	CompanyCur CURSOR LOCAL FAST_FORWARD FOR 
SELECT	Name
FROM	Company
OPEN CompanyCur
WHILE 1 = 1
BEGIN
	FETCH NEXT FROM CompanyCur INTO @Comp
	IF @@FETCH_STATUS <> 0 BREAK
	SET @Comp = REPLACE(REPLACE(@Comp,'.','_'),'/','_')
	SET @Num=3
	WHILE @Num < 8
	BEGIN
		IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = @Comp + '$Components ' + LTRIM(@Num) + ' SAVED4CONV')
		BEGIN
			SET @Qry = 'SELECT * INTO [' + @Comp + '$Components ' + LTRIM(@Num) + ' SAVED4CONV]'
			+ ' FROM  [' + @Comp + '$PfsComponents ' + LTRIM(@Num) + '] zonder'
			+ ' DELETE zonder'
			+ ' FROM  [' + @Comp + '$PfsComponents ' + LTRIM(@Num) + '] zonder'
			+ ' WHERE zonder.[Component Group Code] = '''''
			EXECUTE(@Qry)
		END
		SET @Num=@Num+1
	END
	SET @Qry =' DELETE X FROM  [' + @Comp + '$PfsComponents 3] X WHERE [Component Group Code] <> ''DOELGROEP'' '
	EXECUTE(@Qry)
	SET @Qry =' DELETE X FROM  [' + @Comp + '$PfsComponents 4] X WHERE [Component Group Code] <> ''LENGTE'' '
	EXECUTE(@Qry)
	SET @Qry =' DELETE X FROM  [' + @Comp + '$PfsComponents 5] X WHERE [Component Group Code] <> ''MATERIAAL'' '
	EXECUTE(@Qry)
	SET @Qry =' DELETE X FROM  [' + @Comp + '$PfsComponents 6] X WHERE [Component Group Code] <> ''PRIJSTYPE'' '
--	EXECUTE(@Qry)
	
END
CLOSE CompanyCur
DEALLOCATE CompanyCur
go
--AddFieldToAllCompanies.sql
BEGIN TRY DROP TABLE #Fields END TRY BEGIN CATCH END CATCH
go
DECLARE @Comp VARCHAR(100), @Qry VARCHAR(8000), @Tbl VARCHAR(100), @Fld VARCHAR(100), @DT VARCHAR(100), @Def VARCHAR(100), @ToDo VARCHAR(1000)
CREATE TABLE #Fields (TableName VARCHAR(100), FieldName VARCHAR(100), DataType VARCHAR(100), Def VARCHAR(100) , ToDo VARCHAR(1000) DEFAULT 'ADD')
INSERT #Fields
(TableName, FieldName, DataType, Def)
	SELECT 'RetDistr_ Tracking Spec_', 	'Line No_', 		'INT','0'	--Als tabel RetDistr_ Route Buffer al renamed is naar RetDistr_ Tracking Spec_...
UNION   SELECT 'RetDistr_ Route Buffer', 	'Line No_', 		'INT','0' --Als tabel nog niet renamed is...
UNION	SELECT 'Registered Whse. Activity Hdr.','P2L Warehouse Activity', 	'INT','0'
UNION	SELECT 'Registered Whse. Activity Hdr.','P2L Document No_', 		'INT','0'
UNION	SELECT 'Warehouse Activity Header','P2L Document No_', 		'INT','0'
UNION	SELECT 'Inventory Setup','Max. Qty. to Adjust','decimal(38,20)','0.0'
INSERT #Fields (TableName, FieldName, DataType, Def, ToDo)
SELECT 'RetRetail Customer','RetIsEmployee','tinyint','','ALTER COLUMN RetIsEmployee tinyint NULL'


UPDATE #Fields
SET	TableName = REPLACE(REPLACE(TableName,'.','_'),'/','_')
,	FieldName = REPLACE(REPLACE(FieldName,'.','_'),'/','_')

DECLARE	CompanyCur CURSOR LOCAL FAST_FORWARD FOR 
SELECT	Name
FROM	Company
OPEN CompanyCur
WHILE 1 = 1
BEGIN
	FETCH NEXT FROM CompanyCur INTO @Comp
	IF @@FETCH_STATUS <> 0 BREAK
	SET @Comp = REPLACE(REPLACE(@Comp,'.','_'),'/','_')
	RAISERROR('@Comp %s:',0,1, @Comp) WITH NOWAIT 

	DECLARE	TblCur CURSOR LOCAL FAST_FORWARD FOR 
	SELECT	TableName, FieldName, DataType, Def, ToDo
	FROM	#Fields 
	OPEN TblCur
	WHILE 1 = 1
	BEGIN
		FETCH NEXT FROM TblCur INTO @Tbl, @Fld, @DT, @Def, @ToDo
		IF @@FETCH_STATUS <> 0 BREAK
		RAISERROR('@Tbl=%s,@Fld=%s,@DT=%s,@Def=%s:',0,1, @Tbl,@Fld,@Dt,@Def) WITH NOWAIT 
		IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = @Comp + '$' + @Tbl)
		BEGIN
			IF @ToDo='ADD'
			BEGIN
				IF EXISTS (SELECT 1 FROM SYSOBJECTS O JOIN SYSCOLUMNS C ON C.ID = O.ID 
						WHERE O.NAME = @Comp + '$' + @Tbl
						AND C.NAME = @Fld) CONTINUE
				SET @Qry = 'ALTER TABLE [' + @Comp + '$' + @Tbl + '] ADD [' + @Fld + '] ' + @DT +' NOT NULL CONSTRAINT ConstraintToDrop DEFAULT ' + @Def
				PRINT @Qry
				EXECUTE(@Qry)
				
				SET @Qry = 'ALTER TABLE [' + @Comp + '$' + @Tbl + '] DROP CONSTRAINT ConstraintToDrop '
				EXECUTE(@Qry)
		
			END
			ELSE
			BEGIN
				SET @Qry = 'ALTER TABLE [' + @Comp + '$' + @Tbl + '] ' + @ToDo
				EXECUTE(@Qry)		
			END
		END
	END
	CLOSE TblCur
	DEALLOCATE TblCur

END
CLOSE CompanyCur
DEALLOCATE CompanyCur


