Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [Sites_tbl]'
Print ' Creation Date : 22.03.2006'
Print ' Autor         : J.Feuerstein'
Print '-------------------------------------------------------------------------'

use [#DB_N#]


Declare @MyActionMsg1 nvarchar (max)
Declare @MyTableName nvarchar (max)
    set @MyTableName='Sites_tbl'

--------------------------------------------------------------------------------
-- verzweigen auf TableCreate: oder TableUpdate:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sites_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto TableUpdate
  END
ELSE
  BEGIN
   goto TableCreate
  END

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: ' + @MyTableName + ' dose not exist and will be created new'
  Print (@MyActionMsg1)
  CREATE TABLE [dbo].[Sites_tbl] (
        [Id]                            [uniqueidentifier] NOT NULL ,
        [pSite_ID]                      [nvarchar] (50)  NOT NULL ,   -- ReferenzNummer in Oracle DB 99899900
        [SiteAktive]                    [bit]            NULL     ,   -- ist diese Site aktive Ja/Nein
        [SiteID]                        [nvarchar] (50)  NOT NULL ,   -- JobMgr interne kuerzel für die TCE DB z.B. TCNG
        [pName]                         [nvarchar] (255) ,            -- bezeichung der DB "TEST System 123"
        [DBNetworkAlias]                [nvarchar] (50) ,             -- Oracle Network Alias
        [UserName]                      [nvarchar] (32) ,             -- DB UserName
        [Password]                      [nvarchar] (32)  NULL ,       -- DB Pw
        [OracleHome]                    [nvarchar] (255) NULL ,
        [OracleBin]                     [nvarchar] (255) NULL ,
        [OracleTblNameInfodbaReplace]   [nvarchar] (32) NULL ,        -- 07.03.2011 13:03 new definiert den replace fuer INFODBA.

        [DBProvider]                    [nvarchar] (255) NULL ,       -- SqlClient= System.Data.SqlClient OracleClient=System.Data.OracleClient
        [DBConnectionString]            [nvarchar] (max) NULL ,       -- Data Source=DbServerName\InstanceName;Initial Catalog=CatalogName;User ID=#UserName#;Password=#Password#;Connection Timeout=30;Persist Security Info=True;
        [DbConnectionTimeout]           [int] NULL ,                  -- neu (02.11.2009)
        [DbCommandTimeout]              [int] NULL ,                  -- neu (02.11.2009)
        [Tns_Admin]                     [nvarchar] (255) NULL ,

        [TCArchitecture]                [nvarchar] (255) NULL ,       -- def. TCArchitecture Values NotSet / TCUA / TCEng (#New: 29.03.2013 / J.Fes)


        [ImportItemSearchNames]         [nvarchar] (max) NULL ,       -- aenderugn von 255 auf max Date 08.11.2011
        [ImportGroupSearchNames]        [nvarchar] (max) NULL ,       -- neu (08.11.2011)

        [ImportItemLastModifyDateBegin] [Datetime] NULL ,             -- merkt sich das Datum ab dem beim naechsten Site.Imp fortgefahren wird

        [ImportItemLastModifyDateEnd]   [Datetime] NULL ,
        [ImportAssembMaxComp]           [int] NULL ,
        [ImportAssembMaxLev]            [int] NULL ,
        [ImportAssembMaxTimeSec]        [int] NULL ,                  -- maximale Dauer eines Assembly Importes (neu 19.3.08)

        [ImportMsgAfterNImports]        [int] NULL ,                  -- nach vievielen Imports soll eine Ausgabe im InfoFenster ausgegeben werden (neu 19.3.08)
        [ImportLevelType]               [nvarchar] (10) ,             -- ImportlevelType (10=full) (11=fullUpdate) (20=medium) (31=quick1) (32=quick2)
        [ImportReadnew]                 [bit] NULL ,                  -- sollen die Daten neu gelesen werden neu 21.11.2007

        [ImportPrjData]                 [bit] NULL ,                  -- sollen die Prj Daten gelesen werden neu 02.04.2013/J.Fes
        [ImportItemDataAll]             [bit] NULL ,                  -- sollen die Item Daten gelesen werden #New 16.08.2013/J.Fes
        [ImportMultiSiteData]           [bit] NULL ,                  -- wird in dieser DB TC Multisite verwendet (Default = YES) #New: 21.08.2013/J.Fes
        [ImportFs_Properties]           [bit] NULL ,                  -- soll die File Properties (Default = YES) #New: 26.08.2013/J.Fes

        [ImportDataCleanup]             [bit] NULL ,                  -- sollen die Daten bereinigt werden ? #New 27.03.2013/J.Fes

        [ImportDBReconnetWaitTime_mSec] [int] NULL ,                  -- DB Reconnect wait time (new 27.12.2008)
        [ImportDBReconnetRetrysMax]     [int] NULL ,                  -- DB Reconnect N Retrys  (new 27.12.2008)

        [DataSyncStartDateTime]         [Datetime] NULL ,             -- new (13.06.2011) controls DataSearch.DateTime for Register:"Sites/Data Import" Buttom:"Syn.Site.Data

        [NativeRoot_DP]                 [nvarchar] (255) ,            -- Rootpath für den Import
        [NativeFileMaskInclude]         [nvarchar] (255) ,            -- Einschluss Liste *.Prt
        [NativeFileMaskExclude]         [nvarchar] (255) ,            -- Ausschluss Liste *XYZ*.prt
        [NativeMaskAp]                  [nvarchar] (255) ,            -- Kennzeichner für Assemply Parts

        [Group]                         [nvarchar] (32) ,             -- Org.Group
        [Note]                          [nvarchar] (255) ,            -- Org.Note

        [ImportTcIrSearchMethode]       [nvarchar] (10) ,             -- ImportTcIrSearchMethode 1 = IrsAll 2 = IrsOnlyContainigUGMASTER  (J.Fes new 08.07.2010)
        [ImportTcObjectFs_DPNE]         [bit] NULL ,                  -- sollen die ObjectFs_DPNE Daten importiert werden ? (J.Fes new 08.07.2010)
        [ImportTcObjectFs_NE]           [bit] NULL ,                  -- sollen die ObjectFs_NE Daten importiert werden ? (J.Fes new 08.07.2010)
        [ImportApWithCompCound0AsSp]    [bit] NULL ,                  -- sollen Baugruppen die eine Bom besitzen aber 0 Komponenten haben als Sp gewertet werden (J.Fes new 10.08.2010 09:40)
        [ImportTcApBomLineUgGometryNo_DoCount] [bit] NULL             -- sollen die BOM Lines die Ug Geometrie=No haben mit gezaehlt werden? (J.Fes neu 08.11.2011)

       ) ON [PRIMARY]

  goto CheckCreateIndex
--------------------------------------------------------------------------------
TableUpdate:

  set @MyActionMsg1=
       '-- Table: [' + @MyTableName + '] exist and will be updated'
  Print (@MyActionMsg1)

  Declare @ColumeName nvarchar (256)
  Declare @ColumeNameOld nvarchar (256)
  Declare @ColumeNameNew nvarchar (256)
  Declare @ExecuteStr nvarchar (max)

  -- Alle Updates hierher
  set @ColumeName='ImportAssembMaxTimeSec'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [max duration for importing assembly (new 19.3.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ImportMsgAfterNImports'
  if (Select Coalesce(Col_length('Sites_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [after how may Imports done a new Message comes up in the message area (new 19.3.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end

  /*
  set @ColumeName='ImportObjectNewProcStateMask'
  if (Select Coalesce(Col_length('Sites_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the default status for New Objects (new 15.05.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Sites_tbl] ADD [' + @ColumeName +'] [nvarchar] (11) NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='ImportObjectUpdProcStateMask'
  if (Select Coalesce(Col_length('Sites_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the default status for update Objects (new 15.05.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Sites_tbl] ADD [' + @ColumeName +'] [nvarchar] (11) NULL'
      execute (@ExecuteStr)
    end
  */
  -- löschen 27.12.2008
  set @ColumeName='ImportObjectNewProcStateMask'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName + ' 27.12.2008 using now ProcessGroupDef')
      set @ExecuteStr = 'ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName + ']'
      execute (@ExecuteStr)
    end

  -- löschen 27.12.2008
  set @ColumeName='ImportObjectUpdProcStateMask'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName + ' 27.12.2008 using now ProcessGroupDef')
      set @ExecuteStr = 'ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName + ']'
      execute (@ExecuteStr)
    end

  -- [DBProvider]     [nvarchar] (255) NULL ,         --  MSSql= System.Data.SqlClient Oracle=System.Data.OracleClient
  set @ColumeName='DBProvider'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the DBProvider (new 19.08.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end

  -- [DBConnectionString]     [nvarchar] (max) NULL , --  Data Source=DbServerName\InstanceName;Initial Catalog=CatalogName;User ID=#UserName#;Password=#Password#;Connection Timeout=30;Persist Security Info=True;
  set @ColumeName='DBConnectionString'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the DBConnectionString (new 19.08.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max) NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ImportDBReconnetWaitTime_mSec'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [DB Reconnect wait time (new 27.12.2008)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='ImportDBReconnetRetrysMax'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [DB Reconnect N Retrys  (new 27.12.2008)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end
    
  set @ColumeName='DbConnectionTimeout'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 02.11.2009)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='DbCommandTimeout'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 02.11.2009)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [int] NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ImportTcIrSearchMethode'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 08.07.2010)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (10) NULL'
      execute (@ExecuteStr)
    end


    
  set @ColumeName='ImportTcObjectFs_DPNE'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 08.07.2010)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='ImportTcObjectFs_NE'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 08.07.2010)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end

  -- sollen Baugruppen 0 Komponenten haben als Sp gewertet werden (J.Fes new 10.08.2010 09:40)
  set @ColumeName='ImportApWithCompCound0AsSp'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 10.08.2010)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='OracleTblNameInfodbaReplace'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 07.03.2011 13:04)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (32) NULL'
      execute (@ExecuteStr)
    end

   set @ColumeName='DataSyncStartDateTime'
   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
     begin
       print ('Create Column:' + @ColumeName +  ' [new (13.06.2011) controls DataSearch.DateTime for Register:Sites/Data Import Buttom:Syn.Site.Data]')
       set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] datetime'
       execute (@ExecuteStr)
     end

  set @ColumeName='ImportItemSearchNames'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) <> -1
  begin
     print ('Column: Sites_tbl.ImportItemSearchNames upsize to max char [Change:08.11.2011 17:25 J.Fes]')
     ALTER TABLE [dbo].[Sites_tbl]
       ALTER COLUMN [ImportItemSearchNames] nvarchar(max) COLLATE Latin1_General_CI_AS
  end



  set @ColumeName='ImportGroupSearchNames'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 08.11.2011 17:21)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max) NULL'
      execute (@ExecuteStr)
    end

  -- -- sollen die BOM Lines die Ug Geometrie=No haben mit gezaehlt werden? (J.Fes neu 08.11.2011)
  set @ColumeName='ImportTcApBomLineUgGometryNo_DoCount'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 10.08.2010)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end


   -- ImportDataCleanup
  set @ColumeName='ImportDataCleanup'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 27.03.2013/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end

  -- TCArchitecture
  set @ColumeName='TCArchitecture'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [defines the TCArchitecture (new 29.03.2013/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (255) NULL'
      execute (@ExecuteStr)
    end


  -- ImportPrjData
  set @ColumeName='ImportPrjData'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 02.04.2013/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end

  -- ImportItemDataAll
  -- 02.12.2013/J.Fes if exist ImportItemData rename to ImportItemDataAll
  set @ColumeNameOld='ImportItemData'
  set @ColumeNameNew='ImportItemDataAll'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:[' + @ColumeNameOld + '] to:[' + @ColumeNameNew + ']')
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end

  /*
  set @ColumeName='ImportItemDataAll'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 16.08.2013/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end
  */

  set @ColumeName='ImportMultiSiteData'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 21.08.2013/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end

  set @ColumeName='ImportFs_Properties'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 26.08.2013/J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [Bit] NULL'
      execute (@ExecuteStr)
    end


 goto CheckCreateIndex

CheckCreateIndex:

  If NOT Exists (select Name from sysindexes where Name = 'Sites_tbl_pk')
  begin
     print (' --> create Sites_tbl_pk')
     ALTER TABLE [dbo].[Sites_tbl]
       ADD CONSTRAINT [Sites_tbl_pk]
       PRIMARY KEY NONCLUSTERED ([Id])
       WITH (
         FILLFACTOR = 80,
         PAD_INDEX = OFF,
         STATISTICS_NORECOMPUTE = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON)
  end

  If NOT Exists (select Name from sysindexes where Name = 'Sites_tbl_SiteID_uq')
  begin
     print (' --> create Sites_tbl_SiteID_uq')
     ALTER TABLE [dbo].[Sites_tbl]
        ADD CONSTRAINT [Sites_tbl_SiteID_uq]
        UNIQUE NONCLUSTERED ([SiteID])
        WITH (
          FILLFACTOR = 80,
          PAD_INDEX = OFF,
          IGNORE_DUP_KEY = OFF,
          STATISTICS_NORECOMPUTE = OFF,
          ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON)
  end

  If NOT Exists (select Name from sysindexes where Name = 'Sites_tbl_pSite_ID_uq')
  begin
     print (' --> create Sites_tbl_pSite_ID_uq')
     ALTER TABLE [dbo].[Sites_tbl]
        ADD CONSTRAINT [Sites_tbl_pSite_ID_uq]
        UNIQUE NONCLUSTERED ([pSite_ID])
        WITH (
          FILLFACTOR = 80,
          PAD_INDEX = OFF,
          IGNORE_DUP_KEY = OFF,
          STATISTICS_NORECOMPUTE = OFF,
          ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON)
  end

--------------------------------------------------------------------------------
EndeExit:
 Print '------------------------------------------------------------------------'
 Print (@MyActionMsg1)
 Print ('   |-> Done on Database: #DB_N#')
 Print '------------------------------------------------------------------------'

GO
