Neuer Mandant

1. Im Netframe einen neuen Mandanten anlegen oder direkt in der DB

2. CP_UPDATE checken ob schon Einträge vorhanden (falls IMMO z.B. Mandanten kopiert hat und nicht neu angelegt) und ggf. unsere Einträge löschen. Sollte die CPI den Mandanten kopiert haben (was sie eigentlich nicht tun sollten) dann einfach alle Einträge aus der CP_UPDATE Tabelle löschen. Nach der Installation dann dieses SQL anpassen und ausführen.

   insert into bps.dbo.cp_update
   select u1.* from cp_update u1
   left outer join bps.dbo.cp_update u2
   on(u1.upd_key = u2.upd_key)
   where u2.upd_id is null

3. CP_UPDATE_VERSION Einträge löschen (System, DMS und falls im neuen Mandanten schon Einträge von uns sind auch diese (falls Mandanten DB kopiert))

4. Tabellen und Sichten löschen (falls Mandanten DB kopiert, Skripte mehrmals ausführen)

5. Installation ausführen

6. Konvertierung kopieren

7. Nummernkreis anlegen wenn noch nicht bei der Anlage des Mandanten erstellt

8. Indiv Felder synchen

DECLARE @SQL nvarchar(MAX) = N''

DECLARE @IC_TABLE varchar(50)
DECLARE @IC_FIELDTYPE integer
DECLARE @fieldType varchar(50)
DECLARE @IC_FIELDNAME varchar(50)
DECLARE @IC_PRECISION varchar(10)
DECLARE @IC_SCALE varchar(10)
DECLARE @IC_CALC varchar(MAX)

DECLARE INDIV_CURSOR CURSOR FOR
SELECT
IC_TABLE,
IC_FIELDTYPE,
CASE
WHEN IC_FIELDTYPE = 1
THEN 'varchar'
WHEN IC_FIELDTYPE = 2
THEN 'integer'
WHEN IC_FIELDTYPE = 3
THEN 'decimal'
WHEN IC_FIELDTYPE = 4
THEN 'dateTime'
WHEN IC_FIELDTYPE = 5
THEN 'bit'
END AS fieldType,
IC_FIELDNAME,
ISNULL(IC_PRECISION,''),
ISNULL(IC_SCALE,''),
IC_CALC
FROM SystemDomizilPlusOM_WEI.dbo.CP_INDIV_COLUMNS ic
LEFT OUTER JOIN sys.tables tab
ON ic.IC_TABLE COLLATE DATABASE_DEFAULT = tab.name
LEFT OUTER JOIN sys.columns col
ON tab.object_id = col.object_id
AND ic.IC_FIELDNAME = col.name
WHERE col.object_id IS NULL

OPEN INDIV_CURSOR

FETCH NEXT FROM INDIV_CURSOR INTO
@IC_TABLE,
@IC_FIELDTYPE,
@fieldType,
@IC_FIELDNAME,
@IC_PRECISION,
@IC_SCALE,
@IC_CALC
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IC_FIELDTYPE = 6
BEGIN
SET @SQL = @SQL + char(13)+char(10)+N'exec dbo.CP_UPDATE_ADD_COMPUTED_FIELD ''dbo'', '''+@IC_TABLE+''', '''+@IC_FIELDNAME+''', '''+@IC_CALC+''
END
ELSE
BEGIN
IF @IC_FIELDTYPE = 1
BEGIN
SET @IC_SCALE = ''
END
SET @SQL = @SQL + char(13)+char(10)+N'exec dbo.CP_UPDATE_ADD_FIELD ''dbo'', '''+@IC_TABLE+''', '''+@fieldType+''', '''+@IC_FIELDNAME+''', '''+@IC_PRECISION+''', '''+@IC_SCALE+''', '''', 1'
END

FETCH NEXT FROM INDIV_CURSOR INTO
@IC_TABLE,
@IC_FIELDTYPE,
@fieldType,
@IC_FIELDNAME,
@IC_PRECISION,
@IC_SCALE,
@IC_CALC
END
exec sp_executeSql @sql

CLOSE INDIV_CURSOR
DEALLOCATE INDIV_CURSOR

9. Firmen in den neuen Mandanten kopieren

DECLARE @SQL nvarchar(MAX)
DECLARE @newLine varchar(10)
DECLARE @COL varchar(100)
DECLARE KREDITOR_CURSOR CURSOR FOR
SELECT
col.name
FROM sys.tables tab
INNER JOIN sys.columns col
ON tab.object_id = col.object_id
WHERE tab.name = 'NET_KREDITOR'
AND col.is_computed = 0

OPEN KREDITOR_CURSOR

SET @SQL = N'INSERT INTO NET_KREDITOR('
SET @newLine = CHAR(13)+CHAR(10)+' '
FETCH NEXT FROM KREDITOR_CURSOR INTO @COL
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @SQL + @newLine + N''+@COL+''

SET @newLine = CHAR(13)+CHAR(10)+' ,'
FETCH NEXT FROM KREDITOR_CURSOR INTO @COL
END

CLOSE KREDITOR_CURSOR
OPEN KREDITOR_CURSOR

SET @SQL = @SQL + N'
)
SELECT'
SET @newLine = CHAR(13)+CHAR(10)+' '
FETCH NEXT FROM KREDITOR_CURSOR INTO @COL
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COL = 'alt.'+@COL
SET @SQL = @SQL + @newLine + N''+@COL+''

SET @newLine = CHAR(13)+CHAR(10)+' ,'
FETCH NEXT FROM KREDITOR_CURSOR INTO @COL
END

SET @SQL = @SQL + N'
FROM we.dbo.NET_KREDITOR alt
LEFT OUTER JOIN NET_KREDITOR neu
ON alt.KR_ID = neu.KR_ID
WHERE neu.KR_ID IS NULL'

exec sp_executeSql @SQL

CLOSE KREDITOR_CURSOR
DEALLOCATE KREDITOR_CURSOR

Anschließend auch die Bankkonten übertragen:

insert into net_kreditor_bankkonten ( KRBK_ID, KR_ID, KRBK_STANDARD, KRBK_BANK_NAME, KRBK_IBAN, KRBK_BIC, KRBK_ANMERKUNG, KRBK_ERSTELLER, KRBK_ERSTELLT_DATUM, KRBK_BENUTZER, KRBK_ZEITSTEMPEL, KRBK_SYNC_ID ) select KRBK_ID, KR_ID, KRBK_STANDARD, KRBK_BANK_NAME, KRBK_IBAN, KRBK_BIC, KRBK_ANMERKUNG, KRBK_ERSTELLER, KRBK_ERSTELLT_DATUM, KRBK_BENUTZER, KRBK_ZEITSTEMPEL, KRBK_SYNC_ID from we.dbo.net_kreditor_bankkonten where kr_id not in (select kr_id from net_kreditor_bankkonten)

10. Personen kopieren die synchronisiert werden

DECLARE @SQL nvarchar(MAX)
DECLARE @newLine varchar(10)
DECLARE @COL varchar(100)
DECLARE PERSON_CURSOR CURSOR FOR
SELECT
col.name
FROM sys.tables tab
INNER JOIN sys.columns col
ON tab.object_id = col.object_id
WHERE tab.name = 'NET_PERSON'
AND col.is_computed = 0

OPEN PERSON_CURSOR

SET @SQL = N'INSERT INTO NET_PERSON('
SET @newLine = CHAR(13)+CHAR(10)+' '
FETCH NEXT FROM PERSON_CURSOR INTO @COL
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @SQL + @newLine + N''+@COL+''

SET @newLine = CHAR(13)+CHAR(10)+' ,'
FETCH NEXT FROM PERSON_CURSOR INTO @COL
END

CLOSE PERSON_CURSOR
OPEN PERSON_CURSOR

SET @SQL = @SQL + N'
)
SELECT'
SET @newLine = CHAR(13)+CHAR(10)+' '
FETCH NEXT FROM PERSON_CURSOR INTO @COL
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@COL = 'PE_ID')
BEGIN
SET @COL = 'NEWID()'
END
ELSE
BEGIN
SET @COL = 'alt.'+@COL
END
SET @SQL = @SQL + @newLine + N''+@COL+''

SET @newLine = CHAR(13)+CHAR(10)+' ,'
FETCH NEXT FROM PERSON_CURSOR INTO @COL
END

SET @SQL = @SQL + N'
FROM we.dbo.NET_PERSON alt
LEFT OUTER JOIN NET_PERSON neu
ON alt.PE_SYNC_ID = neu.PE_SYNC_ID
WHERE alt.PE_MANDANTEN_SYNCHRONISIEREN = 1
AND neu.PE_ID IS NULL'

exec sp_executeSql @SQL

CLOSE PERSON_CURSOR
DEALLOCATE PERSON_CURSOR

11. Immer prüfen das die globale variable 51178 für die mandanten im om an der 12. stelle ein j hat

    Die globale variable ist im RW System zu prüfen

12. In den Testfirma Skripten den Connection String auf null setzten sollte es für den Mandanten kein Testsystem geben.

Skript zum Tabellen löschen:

DECLARE @NAME AS VARCHAR(100)
DECLARE @SQL AS VARCHAR(max)

DECLARE drop_cursor CURSOR
FOR
SELECT name
FROM SYS.OBJECTS
WHERE TYPE = 'U'
AND SUBSTRING(NAME, 1, 4) = 'NET_'

OPEN drop_cursor

FETCH NEXT FROM drop_cursor
INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = ''
SET @SQL = 'DROP TABLE ' + @NAME

EXEC(@SQL)

SET @SQL = ''

FETCH NEXT FROM drop_cursor
INTO @NAME
END

CLOSE drop_cursor
DEALLOCATE drop_cursor

Skript zum Sichten löschen:

DECLARE @NAME AS VARCHAR(100)
DECLARE @SQL AS VARCHAR(max)

DECLARE drop_cursor CURSOR
FOR
SELECT name
FROM SYS.OBJECTS
WHERE TYPE = 'V'
AND(SUBSTRING(NAME, 1, 3) = 'CP_'
OR SUBSTRING(NAME, 1, 4) = 'NET_')

OPEN drop_cursor

FETCH NEXT FROM drop_cursor
INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = ''
SET @SQL = 'DROP VIEW ' + @NAME

EXEC(@SQL)

SET @SQL = ''

FETCH NEXT FROM drop_cursor
INTO @NAME
END

CLOSE drop_cursor
DEALLOCATE drop_cursor

SQL Benutzer in den neuen Mandanten hinzufügen:

DECLARE @CO_ID AS VARCHAR(36) = '9043B23C-1391-4956-A0A0-8FA332652111'

INSERT INTO CP_USER_COMPANY(

UC_ID,

US_ID,

CO_ID,

UC_DEFAULT,

UC_DISABLED

)

select

NEWID(),

u.US_ID,

@CO_ID AS CO_ID,

null AS UC_DEFAULT,

null AS UC_DISABLED

from cp_user u

left outer join CP_USER_COMPANY uc

on(u.US_ID = uc.US_ID

AND uc.CO_ID = @CO_ID)

WHERE uc.US_ID is null

SQL Synchronisierten Benutzern die PE_NUMMER vergeben:

SET NOCOUNT ON

SELECT

p.PE_ID,

p.PE_NUMMER,

0 AS COUNTER

INTO #tmpPerNr

FROM NET_PERSON p

WHERE p.PE_NUMMER is null

DECLARE @PE_ID AS VARCHAR(36)

DECLARE @PE_NUMMER AS VARCHAR(50)

DECLARE @COUNTER AS INT

DECLARE @tmpCOUNTER AS INT = 0

DECLARE nr_cur CURSOR FOR

SELECT

PE_ID, PE_NUMMER, COUNTER

FROM #tmpPerNr

OPEN nr_cur

FETCH NEXT FROM nr_cur INTO @PE_ID, @PE_NUMMER, @COUNTER

WHILE @@FETCH_STATUS = 0

BEGIN

SET @tmpCOUNTER = @tmpCOUNTER + 1

UPDATE #tmpPerNr

SET

COUNTER = @tmpCOUNTER,

PE_NUMMER = 'PER-' + RIGHT('000000' + cast(@tmpCounter as varchar(20)), 6)

WHERE PE_ID = @PE_ID

FETCH NEXT FROM nr_cur INTO @PE_ID, @PE_NUMMER, @COUNTER

END

CLOSE nr_cur

DEALLOCATE nr_cur

SET NOCOUNT OFF

UPDATE p

SET

PE_NUMMER = t.PE_NUMMER

FROM #tmpPerNr t

INNER JOIN NET_PERSON p

ON(p.PE_ID = t.PE_ID)

DROP TABLE #tmpPerNr