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

use [#DB_N#]
GO


--------------------------------------------------------------------------------


Print '-- [DS_with_Irs_View] ---------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "DS_with_Irs_View" and type = "V")
Drop View DS_with_Irs_View
GO

-- !! Namen nicht aendern !! siehe auch QuellCode ..\JobServer.DataSource\JobServer_DataSource.vb  Objects_tbl_UpdateProcessState
-- !! alter name war: ObjectsDataSets_View
CREATE VIEW [DS_with_Irs_View]
-- BasisView
-- Angezeigt werden die Datasets mit Irs
-- 31.12.2008 DS.ImportObjectErr eingefügt
AS
SELECT
  -- IRs.Id As IrId,
  IRs.ObjectName         AS IrName,
  IRs.ObjectName2        AS IrName2,
  IRs.ObjectDescription  AS IrDescription,
  IRs.ObjectReleaseState AS IrReleaseState,
  IRs.ObjectReleaseDate  AS IrReleaseDate,
  IRs.ObjectCreateDate   AS IrCreateDate,
  IRs.ObjectLastModDate  AS IrLastModDate,

--  DS.ObjectIsIrLatest        AS ObjectIsIrLatest,  -- #New 03.06.2015 J.Fes/A.Becker
--   DS.ObjectIsIrLatestWorking AS ObjectIsIrLatestWorking, -- #New 03.06.2015 J.Fes/A.Becker  #Remove 09.06.2015/J.Fes
  DS.ObjectIrLatestRank      AS ObjectIrLatestRank , -- #New 03.06.2015 J.Fes/A.Becker


  IRs.ObjectPuId         AS IrPuId,
  -- 10.01.2012 11:58 eingefuegt
  Irs.ObjectType         AS IrType,

  DS.Id AS Id,
  DS.ObjectName,
  DS.ObjectDescription,
  DS.ObjectType,
-- 10.01.2012 11:58 entnommen das DS.ObjecIrType wieder entnommen wurde
--  DS.ObjectIrType        AS IrType,
  DS.ObjectCliName,
  DS.ObjectFromSiteId,
  DS.ObjectClass,
  DS.ObjectOwningSiteId,
  DS.ObjectExportRecord,
  DS.ObjectExportRecordDate,
  DS.ObjectOwner,
  DS.ObjectGroup,
  DS.ObjectCreateDate,
  DS.ObjectLastModUser,
  DS.ObjectLastModDate,
  DS.ObjectUgMaster_Type,
  DS.ObjectUgMaster_CompCount,
  DS.ObjectUgMaster_CompCountUnique, -- #New 22.08.2012/a.becker (KION)
  DS.ObjectUgMaster_Lev1CompN,       -- #New 07.08.2014/J.Fes
  DS.ObjectUgMaster_Lev1CompUniqueN, -- #New 07.08.2014/J.Fes
  DS.ObjectUgMaster_ApBOM_isPrecise, -- #New 07.08.2014/J.Fes

  -- #New 26.02.2014 J.Fes
  CASE DS.ObjectFromSiteId WHEN DS.ObjectOwningSiteId THEN '1-SO (SiteObj)'
                           ELSE   '2-RO (ReplObj)'  END As [Object_SiteOwningTyp],

--  case DS.ObjectFromSiteId '#Debug' As [Object_SiteOwningTyp],
  DS.ObjectUgMaster_APLevels,
  DS.ObjectUgMaster_FsHitCount,
--  DS.ObjectUgMaster_FsHitCount_YN,
  DS.ObjectUgMaster_PartFamMemName,
  DS.ObjectReleaseState,
  DS.ObjectReleaseDate,
  DS.ObjectFs_DPNE,
  DS.ObjectFs_Vol,
  DS.ObjectFs_NE,
  DS.ObjectFs_SizeBytes, -- new 22.08.2012 a.becker
--   CAST(ROUND(DS.ObjectFs_SizeBytes/1048576, 2) AS decimal(10,2)) As [ObjectFs_SizeMB],
  CAST(DS.ObjectFs_SizeBytes/1048576.00 AS decimal(15,2)) As [ObjectFs_SizeMB],  -- new 08.11.2012 17:44 J.Fes (KION) 1048576=1024*1024
  DS.ObjectFs_HashCode,  -- new 22.08.2012 a.becker
  DS.ObjectFs_LastModDate, -- new 25.04.2014 J.Fes
  DS.ObjectDs_Ver,         -- #New: 29.05.2016 J.Fes
  DS.ObjectDs_VerKeepLimt, -- #New: 29.05.2016 J.Fes
  DS.ObjectDs_VersN,       -- #New: 29.05.2016 J.Fes

  DS.ObjectSaveVersion,  -- new 22.08.2012 a.becker
--  DS.ObjectNote,       -- #Removed 17.06.2016 J.Fes
  DS.ObjectPrjIds,       -- new 01.04.2013 J.Fes
  DS.ObjectSyncTrigger,  -- #New 17.06.2016 J.Fes
  DS.ProcStateCol01,
  DS.ProcStateCol02,
  DS.ProcStateCol03,
  DS.ProcStateCol04,
  DS.ProcStateCol05,
  DS.ProcStateCol06,
  DS.ProcStateCol07,
  DS.ProcStateCol08,
  DS.ProcStateCol09,
  DS.ProcStateCol10,

  -- #New 07.08.2014/J.Fes
  DS.ProcStateCol11,
  DS.ProcStateCol12,
  DS.ProcStateCol13,
  DS.ProcStateCol14,
  DS.ProcStateCol15,
  DS.ProcStateCol16,
  DS.ProcStateCol17,
  DS.ProcStateCol18,
  DS.ProcStateCol19,
  DS.ProcStateCol20,
  DS.ProcStateColMc,  -- #New:02.03.2016/J.Fes
  DS.ProcStateColRf,

  DS.ImportObjectErr,
  
  DS.MultiCadAP,       -- #New: 21.08.2019/S.Gueth
  DS.MultiCadItemRevs, -- #New: 21.08.2019/S.Gueth

  -- #New 07.08.2014/J.Fes
  DS.ObjectAttr01,
  DS.ObjectAttr02,
  DS.ObjectAttr03,
  DS.ObjectAttr04,
  DS.ObjectAttr05,
  DS.ObjectAttr06,
  DS.ObjectAttr07,
  DS.ObjectAttr08,
  DS.ObjectAttr09,
  DS.ObjectAttr10,
  -- nur temporär
  DS.ObjectPuId
FROM
  -- 27.09.2011 WITH (nolock) engefuegt J.Fes
  Objects_tbl DS WITH (nolock)
  INNER JOIN Objects_Ir_Rel_tbl IrRel ON (DS.Id = IrRel.ObjectId)
  INNER JOIN Objects_tbl        Irs   ON (IrRel.IrId = Irs.Id)
where
  -- #New: 07.03.2017 /J.Fes to enshure only Data of DS will be returnd
  DS.ObjectClass = 'DS'
GO



Print '-- [IRs_View] -----------------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "IRs_View" and type = "V")
Drop View IRs_View
GO

CREATE VIEW [IRs_View]
-- BasisView: Ausgegeben werden die IRs
AS
SELECT
  dbo.Objects_tbl.Id,
  dbo.Objects_tbl.ObjectPuId,
  dbo.Objects_tbl.ObjectName , -- wird zusäzliche benötigt
  dbo.Objects_tbl.ObjectPuId As IrPuid,  -- 05.12.2016 Renamed IrPuId -to- IrPuid
  dbo.Objects_tbl.ObjectName As IrName,
  dbo.Objects_tbl.ObjectName2 As IrName2,
  dbo.Objects_tbl.ObjectDescription As IrDescription,
  dbo.Objects_tbl.ObjectFromSiteId,
  dbo.Objects_tbl.ObjectClass,
  dbo.Objects_tbl.ObjectType As IrType, -- 20.12.2011 04:48 eingefuegt
  dbo.Objects_tbl.ObjectCliName,
  dbo.Objects_tbl.ObjectOwningSiteId,
  dbo.Objects_tbl.ObjectExportRecord,
  dbo.Objects_tbl.ObjectExportRecordDate, -- 20.08.2013 New
  dbo.Objects_tbl.ObjectOwner,
  dbo.Objects_tbl.ObjectGroup,
  dbo.Objects_tbl.ObjectCreateDate,
  dbo.Objects_tbl.ObjectLastModUser,
  dbo.Objects_tbl.ObjectLastModDate,
  dbo.Objects_tbl.ObjectUgMaster_Type,
  dbo.Objects_tbl.ObjectUgMaster_CompCount,
  dbo.Objects_tbl.ObjectUgMaster_CompCountUnique,   -- new 22.08.2012 a.becker (KION)
  dbo.Objects_tbl.ObjectUgMaster_APLevels,
  dbo.Objects_tbl.ObjectUgMaster_FsHitCount,
  dbo.Objects_tbl.ObjectUgMaster_Lev1CompN,       -- #New 29.10.2015 12:13 J.Fes
  dbo.Objects_tbl.ObjectUgMaster_Lev1CompUniqueN, -- #New 29.10.2015 12:13 J.Fes
  dbo.Objects_tbl.ObjectUgMaster_ApBOM_isPrecise, -- #New 29.10.2015 12:13 J.Fes

--  dbo.Objects_tbl.ObjectFs_DPNE,  -- 25.04.2014 J.Fes entnommen
  dbo.Objects_tbl.ObjectFs_Vol,
--  dbo.Objects_tbl.ObjectFs_NE, ,  -- 25.04.2014 J.Fes entnommen
--   dbo.Objects_tbl.ObjectFs_SizeBytes, -- new 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--   dbo.Objects_tbl.ObjectFs_HashCode,  -- new 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--   dbo.Objects_tbl.ObjectFs_LastModDate, -- new 25.04.2014 J.Fes but no usage

--  dbo.Objects_tbl.ObjectIsIrLatest, -- #New 03.06.2015 J.Fes/A.Becker
--   dbo.Objects_tbl.ObjectIsIrLatestWorking, -- #New 03.06.2015 J.Fes/A.Becker  #Remove 09.06.2015/J.Fes
  dbo.Objects_tbl.ObjectIrLatestRank, -- #New 03.06.2015 J.Fes/A.Becker


  dbo.Objects_tbl.ObjectSaveVersion,   -- new 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
  dbo.Objects_tbl.ObjectUgMaster_PartFamMemName,
  dbo.Objects_tbl.ObjectReleaseState,
  dbo.Objects_tbl.ObjectReleaseDate,
  
  dbo.Objects_tbl.MultiCadAP,          -- #New:21.08.2019/S.Gueth
  dbo.Objects_tbl.MultiCadItemRevs,          -- #New:21.08.2019/S.Gueth
  
--  dbo.Objects_tbl.ObjectNote,        -- #Removed 17.06.2016 J.Fes
  dbo.Objects_tbl.ObjectPrjIds,
  dbo.Objects_tbl.ObjectSyncTrigger,   -- #New: 17.06.2016/J.Fes
  dbo.Objects_tbl.ImportObjectErr,
  dbo.Objects_tbl.ProcStateCol01,
  dbo.Objects_tbl.ProcStateCol02,
  dbo.Objects_tbl.ProcStateCol03,
  dbo.Objects_tbl.ProcStateCol04,
  dbo.Objects_tbl.ProcStateCol05,
  dbo.Objects_tbl.ProcStateCol06,
  dbo.Objects_tbl.ProcStateCol07,
  dbo.Objects_tbl.ProcStateCol08,
  dbo.Objects_tbl.ProcStateCol09,
  dbo.Objects_tbl.ProcStateCol10,

  -- #New 07.08.2014/J.Fes
  dbo.Objects_tbl.ProcStateCol11,
  dbo.Objects_tbl.ProcStateCol12,
  dbo.Objects_tbl.ProcStateCol13,
  dbo.Objects_tbl.ProcStateCol14,
  dbo.Objects_tbl.ProcStateCol15,
  dbo.Objects_tbl.ProcStateCol16,
  dbo.Objects_tbl.ProcStateCol17,
  dbo.Objects_tbl.ProcStateCol18,
  dbo.Objects_tbl.ProcStateCol19,
  dbo.Objects_tbl.ProcStateCol20,
  dbo.Objects_tbl.ProcStateColMc, -- #New:02.03.2016/J.Fes
  dbo.Objects_tbl.ProcStateColRf,

  -- #New 07.08.2014/J.Fes
  dbo.Objects_tbl.ObjectAttr01,
  dbo.Objects_tbl.ObjectAttr02,
  dbo.Objects_tbl.ObjectAttr03,
  dbo.Objects_tbl.ObjectAttr04,
  dbo.Objects_tbl.ObjectAttr05,
  dbo.Objects_tbl.ObjectAttr06,
  dbo.Objects_tbl.ObjectAttr07,
  dbo.Objects_tbl.ObjectAttr08,
  dbo.Objects_tbl.ObjectAttr09,
  dbo.Objects_tbl.ObjectAttr10


FROM
  --  WITH (nolock) engefuegt 09.07.2010 J.Fes
  dbo.Objects_tbl WITH (nolock)
WHERE
  dbo.Objects_tbl.ObjectClass = 'IR'
GO




Print '-- [DS_AndProcessData_UDF] ----------------------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "DS_AndProcessData_UDF")
DROP FUNCTION DS_AndProcessData_UDF
GO

CREATE FUNCTION DS_AndProcessData_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  DS_with_Irs_View.*,
  PD_tbl.Id As zzzPDat_DRId ,
  PD_tbl.Objects_tbl_Id ,
-- 01.08.2015/J.Fes removed
--  PD_tbl.JobControlRuleName,
--  PD_tbl.ObjectCliName AS AAPDObjectCliName,
  PD_tbl.ProcessPacketName,
-- 01.08.2015/J.Fes removed
--  PD_tbl.ProcessOnSiteId,
  PD_tbl.ProcessState,
  PD_tbl.ProcessTimeStart,
  PD_tbl.ProcessTimeEnd,
  PD_tbl.ProcessCMDLine,
  PD_tbl.ProcessOnComputer ,
  PD_tbl.ProcessResultCode,
  PD_tbl.ProcessResultMsg,
  -- eingetragen 08.07.2013 16:48 J.Fes
  PD_tbl.ProcessResultExtendedData,
  -- eingetragen 05.04.2013 19:30 J.Fes
  PD_tbl.ProcessPrgUsedVer,
  PD_tbl.ProcessObjectSaveVersion,
  -- eingetragen 12.07.2009 J.Fes
  PD_tbl.ProcessResultHasWarning,
  PD_tbl.ProcessResultLink,
  --  eingetragen 22.10.2008 J.Fes
   --  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_Id,
  PD_tbl.ProcessProcStateCol,
  --  eingetragen 22.2.2009 J.Fes
  PD_tbl.Row_isLastRes ,

  --  eingetragen 26.08.2015/A.Becker um den SQL Like bei ItemName filtern zu verkürzen
  DS_with_Irs_View.IrName AS N,
  -- #New 04.02.18 J.Fes to get easy process status of @ProcessProcStateCol
  ObjProcStateValue = CASE @ProcessProcStateCol
    WHEN 'ProcStateCol01' THEN [ProcStateCol01]
    WHEN 'ProcStateCol02' THEN [ProcStateCol02]
    WHEN 'ProcStateCol03' THEN [ProcStateCol03]
    WHEN 'ProcStateCol04' THEN [ProcStateCol04]
    WHEN 'ProcStateCol05' THEN [ProcStateCol05]
    WHEN 'ProcStateCol06' THEN [ProcStateCol06]
    WHEN 'ProcStateCol07' THEN [ProcStateCol07]
    WHEN 'ProcStateCol08' THEN [ProcStateCol08]
    WHEN 'ProcStateCol09' THEN [ProcStateCol09]
    WHEN 'ProcStateCol10' THEN [ProcStateCol10]
    WHEN 'ProcStateCol11' THEN [ProcStateCol11]
    WHEN 'ProcStateCol12' THEN [ProcStateCol12]
    WHEN 'ProcStateCol13' THEN [ProcStateCol13]
    WHEN 'ProcStateCol14' THEN [ProcStateCol14]
    WHEN 'ProcStateCol15' THEN [ProcStateCol15]
    WHEN 'ProcStateCol16' THEN [ProcStateCol16]
    WHEN 'ProcStateCol17' THEN [ProcStateCol17]
    WHEN 'ProcStateCol18' THEN [ProcStateCol18]
    WHEN 'ProcStateCol19' THEN [ProcStateCol19]
    WHEN 'ProcStateCol20' THEN [ProcStateCol20]
    WHEN 'ProcStateColMc' THEN [ProcStateColMc]
    WHEN 'ProcStateColRf' THEN [ProcStateColRf]
   END
FROM
  --  27.09.2011  WITH (nolock) eingefuegt J.Fes
  DS_with_Irs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock)
      WHERE ProcessProcStateCol=@ProcessProcStateCol
       -- 01.08.2015/J.Fes removed
       -- AND ObjectClass = 'DS'
     ) as PD_tbl
  ON (DS_with_Irs_View.Id = PD_tbl.Objects_tbl_Id)
)
GO


Print '-- [IRs_AndProcessData_UDF] ---------------------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "IRs_AndProcessData_UDF")
DROP FUNCTION IRs_AndProcessData_UDF
GO

CREATE FUNCTION IRs_AndProcessData_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  IRs_View.*,
  PD_tbl.Id As zzzPDat_DRId ,
  PD_tbl.Objects_tbl_Id ,
-- 01.08.2015/J.Fes removed
--  PD_tbl.JobControlRuleName,
  PD_tbl.ProcessPacketName,
-- 01.08.2015/J.Fes removed
-- PD_tbl.ProcessOnSiteId,
  PD_tbl.ProcessState,
  PD_tbl.ProcessTimeStart,
  PD_tbl.ProcessTimeEnd,
  PD_tbl.ProcessCMDLine,
  PD_tbl.ProcessOnComputer ,
  PD_tbl.ProcessResultCode,
  PD_tbl.ProcessResultMsg ,
  -- eingetragen 08.07.2013 16:48 J.Fes
  PD_tbl.ProcessResultExtendedData ,

-- eingetragen 12.07.2009 J.Fes
  PD_tbl.ProcessResultHasWarning,
  PD_tbl.ProcessResultLink ,

-- eingetragen 20.08.2013 J.Fes
  PD_tbl.ProcessPrgUsedVer,
  PD_tbl.ProcessObjectSaveVersion,

--  eingetragen 22.10.2008 J.Fes
--  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_Id,
  PD_tbl.ProcessProcStateCol ,
--  eingetragen 22.2.2009 J.Fes
  PD_tbl.Row_isLastRes,

  --  eingetragen 26.08.2015/A.Becker um den SQL Like bei ItemName filtern zu verkürzen
  IRs_View.IrName AS N,
  -- #New 04.02.18 J.Fes to get easy process status of @ProcessProcStateCol
  ObjProcStateValue = CASE @ProcessProcStateCol
    WHEN 'ProcStateCol01' THEN [ProcStateCol01]
    WHEN 'ProcStateCol02' THEN [ProcStateCol02]
    WHEN 'ProcStateCol03' THEN [ProcStateCol03]
    WHEN 'ProcStateCol04' THEN [ProcStateCol04]
    WHEN 'ProcStateCol05' THEN [ProcStateCol05]
    WHEN 'ProcStateCol06' THEN [ProcStateCol06]
    WHEN 'ProcStateCol07' THEN [ProcStateCol07]
    WHEN 'ProcStateCol08' THEN [ProcStateCol08]
    WHEN 'ProcStateCol09' THEN [ProcStateCol09]
    WHEN 'ProcStateCol10' THEN [ProcStateCol10]
    WHEN 'ProcStateCol11' THEN [ProcStateCol11]
    WHEN 'ProcStateCol12' THEN [ProcStateCol12]
    WHEN 'ProcStateCol13' THEN [ProcStateCol13]
    WHEN 'ProcStateCol14' THEN [ProcStateCol14]
    WHEN 'ProcStateCol15' THEN [ProcStateCol15]
    WHEN 'ProcStateCol16' THEN [ProcStateCol16]
    WHEN 'ProcStateCol17' THEN [ProcStateCol17]
    WHEN 'ProcStateCol18' THEN [ProcStateCol18]
    WHEN 'ProcStateCol19' THEN [ProcStateCol19]
    WHEN 'ProcStateCol20' THEN [ProcStateCol20]
    WHEN 'ProcStateColMc' THEN [ProcStateColMc]
    WHEN 'ProcStateColRf' THEN [ProcStateColRf]
   END
  FROM
  -- DS_with_Irs_View
  -- 06.02.2011 13:27 WITH (nolock) WHERE Proc.. eingefuegt J.Fes
  -- 27.09.2011 20:37 IRs_View WITH (nolock) engefuegt J.Fes
  IRs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock)
       WHERE ProcessProcStateCol=@ProcessProcStateCol
      -- 01.08.2015/J.Fes removed
      -- and ObjectClass = 'IR'
      ) as PD_tbl
  ON (IRs_View.Id = PD_tbl.Objects_tbl_Id)
)
GO
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Print '-- [ITEMs_View #New: 19.08.2013 J.Fes] ----------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ITEMs_View" and type = "V")
Drop View ITEMs_View
GO

CREATE VIEW [ITEMs_View]
-- BasisView: Ausgegeben werden die Items
-- #Doc siehe filter Details in: \02-VB.NET\JobServer\MainForm\Objects\ObjectsFilterPanelUserControl.vb
AS
SELECT
  dbo.Objects_tbl.Id,
  dbo.Objects_tbl.ObjectPuId,
--  dbo.Objects_tbl.ObjectName , -- wird nicht benötigt
  dbo.Objects_tbl.ObjectPuId As ItemPuid,  -- 05.12.2016 Renamed ItemPuId -to- ItemPuid
  dbo.Objects_tbl.ObjectName As ItemId,
  dbo.Objects_tbl.ObjectName2 As ItemName,
  dbo.Objects_tbl.ObjectDescription As ItemDescription,
  dbo.Objects_tbl.ObjectFromSiteId,
  dbo.Objects_tbl.ObjectClass,
  dbo.Objects_tbl.ObjectType As ItemType, -- 20.12.2011 04:48 eingefuegt
  dbo.Objects_tbl.ObjectCliName,
  dbo.Objects_tbl.ObjectOwningSiteId,
  dbo.Objects_tbl.ObjectExportRecord,
  dbo.Objects_tbl.ObjectExportRecordDate,
  dbo.Objects_tbl.ObjectOwner,
  dbo.Objects_tbl.ObjectGroup,
  dbo.Objects_tbl.ObjectCreateDate,
  dbo.Objects_tbl.ObjectLastModUser,
  dbo.Objects_tbl.ObjectLastModDate,
  dbo.Objects_tbl.ObjectUgMaster_Type,
  dbo.Objects_tbl.ObjectUgMaster_CompCount,
  dbo.Objects_tbl.ObjectUgMaster_CompCountUnique,   -- new 22.08.2012 a.becker (KION)
  dbo.Objects_tbl.ObjectUgMaster_APLevels,
--  dbo.Objects_tbl.ObjectUgMaster_FsHitCount,
--  dbo.Objects_tbl.ObjectFs_DPNE,
  dbo.Objects_tbl.ObjectFs_Vol,
--  dbo.Objects_tbl.ObjectFs_NE,
--   dbo.Objects_tbl.ObjectFs_SizeBytes, -- #New: 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--   dbo.Objects_tbl.ObjectFs_HashCode,  -- #New: 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--  dbo.Objects_tbl.ObjectSaveVersion,   -- #New: 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--  dbo.Objects_tbl.ObjectUgMaster_PartFamMemName,
  dbo.Objects_tbl.ObjectReleaseState,
  dbo.Objects_tbl.ObjectReleaseDate,
--  dbo.Objects_tbl.ObjectNote,           -- #Removed 17.06.2016 J.Fes
  dbo.Objects_tbl.ObjectPrjIds,
  dbo.Objects_tbl.ObjectSyncTrigger,   -- #New: 17.06.2016/J.Fes
  dbo.Objects_tbl.ImportObjectErr,
  dbo.Objects_tbl.ProcStateCol01,
  dbo.Objects_tbl.ProcStateCol02,
  dbo.Objects_tbl.ProcStateCol03,
  dbo.Objects_tbl.ProcStateCol04,
  dbo.Objects_tbl.ProcStateCol05,
  dbo.Objects_tbl.ProcStateCol06,
  dbo.Objects_tbl.ProcStateCol07,
  dbo.Objects_tbl.ProcStateCol08,
  dbo.Objects_tbl.ProcStateCol09,
  dbo.Objects_tbl.ProcStateCol10,

  -- #New 07.08.2014/J.Fes
  dbo.Objects_tbl.ProcStateCol11,
  dbo.Objects_tbl.ProcStateCol12,
  dbo.Objects_tbl.ProcStateCol13,
  dbo.Objects_tbl.ProcStateCol14,
  dbo.Objects_tbl.ProcStateCol15,
  dbo.Objects_tbl.ProcStateCol16,
  dbo.Objects_tbl.ProcStateCol17,
  dbo.Objects_tbl.ProcStateCol18,
  dbo.Objects_tbl.ProcStateCol19,
  dbo.Objects_tbl.ProcStateCol20,
  dbo.Objects_tbl.ProcStateColMc, -- #New:02.03.2016/J.Fes
  dbo.Objects_tbl.ProcStateColRf,


  -- #New 07.08.2014/J.Fes
  dbo.Objects_tbl.ObjectAttr01,
  dbo.Objects_tbl.ObjectAttr02,
  dbo.Objects_tbl.ObjectAttr03,
  dbo.Objects_tbl.ObjectAttr04,
  dbo.Objects_tbl.ObjectAttr05,
  dbo.Objects_tbl.ObjectAttr06,
  dbo.Objects_tbl.ObjectAttr07,
  dbo.Objects_tbl.ObjectAttr08,
  dbo.Objects_tbl.ObjectAttr09,
  dbo.Objects_tbl.ObjectAttr10
FROM
  --  WITH (nolock) engefuegt 09.07.2010 J.Fes
  dbo.Objects_tbl WITH (nolock)
WHERE
  dbo.Objects_tbl.ObjectClass = 'I'
GO

Print '-- [ITEMs_AndProcessData_UDF #New: 19.08.2013/J.Fes] --------------------'
If Exists (select Name
           from sysobjects
            where Name = "ITEMs_AndProcessData_UDF")
DROP FUNCTION ITEMs_AndProcessData_UDF
GO

CREATE FUNCTION ITEMs_AndProcessData_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  ITEMs_View.*,
  PD_tbl.Id As zzzPDat_DRId ,
  PD_tbl.Objects_tbl_Id ,
-- 01.08.2015/J.Fes removed
--  PD_tbl.JobControlRuleName,
  PD_tbl.ProcessPacketName,
-- 01.08.2015/J.Fes removed
-- PD_tbl.ProcessOnSiteId,
  PD_tbl.ProcessState,
  PD_tbl.ProcessTimeStart,
  PD_tbl.ProcessTimeEnd,
  PD_tbl.ProcessCMDLine,
  PD_tbl.ProcessOnComputer,

  PD_tbl.ProcessResultCode,
  PD_tbl.ProcessResultMsg ,
  -- eingetragen 08.07.2013 16:48 J.Fes
  PD_tbl.ProcessResultExtendedData ,

  -- eingetragen 12.07.2009 J.Fes
  PD_tbl.ProcessResultHasWarning,
  PD_tbl.ProcessResultLink ,

  PD_tbl.ProcessPrgUsedVer,
  PD_tbl.ProcessObjectSaveVersion,

--  eingetragen 22.10.2008 J.Fes
--  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_Id,
  PD_tbl.ProcessProcStateCol ,
--  eingetragen 22.2.2009 J.Fes
  PD_tbl.Row_isLastRes,

  --  eingetragen 26.08.2015/A.Becker um den SQL Like bei ItemName filtern zu verkürzen
  ITEMs_View.ItemId AS N,
  -- #New 04.02.18 J.Fes to get easy process status of @ProcessProcStateCol
  ObjProcStateValue = CASE @ProcessProcStateCol
    WHEN 'ProcStateCol01' THEN [ProcStateCol01]
    WHEN 'ProcStateCol02' THEN [ProcStateCol02]
    WHEN 'ProcStateCol03' THEN [ProcStateCol03]
    WHEN 'ProcStateCol04' THEN [ProcStateCol04]
    WHEN 'ProcStateCol05' THEN [ProcStateCol05]
    WHEN 'ProcStateCol06' THEN [ProcStateCol06]
    WHEN 'ProcStateCol07' THEN [ProcStateCol07]
    WHEN 'ProcStateCol08' THEN [ProcStateCol08]
    WHEN 'ProcStateCol09' THEN [ProcStateCol09]
    WHEN 'ProcStateCol10' THEN [ProcStateCol10]
    WHEN 'ProcStateCol11' THEN [ProcStateCol11]
    WHEN 'ProcStateCol12' THEN [ProcStateCol12]
    WHEN 'ProcStateCol13' THEN [ProcStateCol13]
    WHEN 'ProcStateCol14' THEN [ProcStateCol14]
    WHEN 'ProcStateCol15' THEN [ProcStateCol15]
    WHEN 'ProcStateCol16' THEN [ProcStateCol16]
    WHEN 'ProcStateCol17' THEN [ProcStateCol17]
    WHEN 'ProcStateCol18' THEN [ProcStateCol18]
    WHEN 'ProcStateCol19' THEN [ProcStateCol19]
    WHEN 'ProcStateCol20' THEN [ProcStateCol20]
    WHEN 'ProcStateColMc' THEN [ProcStateColMc]
    WHEN 'ProcStateColRf' THEN [ProcStateColRf]
  END
FROM
  -- DS_with_ITEMs_View
  -- 06.02.2011 13:27 WITH (nolock) WHERE Proc.. eingefuegt J.Fes
  -- 27.09.2011 20:37 ITEMs_View WITH (nolock) engefuegt J.Fes
  ITEMs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock)
      WHERE ProcessProcStateCol=@ProcessProcStateCol
      -- 01.08.2015/J.Fes removed
      -- and ObjectClass = 'I'
      ) as PD_tbl
  ON (ITEMs_View.Id = PD_tbl.Objects_tbl_Id)
)
GO
--------------------------------------------------------------------------------
Print '-- [GetGroupKeysForObject_UDF] ----------------------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "GetGroupKeysForObject_UDF" )
DROP FUNCTION GetGroupKeysForObject_UDF
GO

-- =============================================
-- Author:		Alexander Becker
-- Create date: 17.11.2017
-- Description:	Gibt die Gruppen zurück in denen ein Objekt verknüpft ist
-- =============================================


CREATE FUNCTION GetGroupKeysForObject_UDF
(
	-- Add the parameters for the function here
	@objectId UNIQUEIDENTIFIER,
 @objectGroupSelected GUID_TABLE READONLY,
 @returnKind VARCHAR(16) -- Values: returnAll or returnSelected
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @res NVARCHAR(MAX)

	-- Add the T-SQL statements to compute the return value here  + '//' + [GroupName]

 IF @returnKind = 'returnAll'
 BEGIN

  SELECT @res = COALESCE(@res + ', ', '') + [GroupKey]
 	FROM Objects_ObjectGroups_Rel_tbl REL
 	JOIN ObjectGroups_tbl OG ON OG.Id=REL.ObjectGroupId
 	WHERE ObjectId=@objectId

 END ELSE BEGIN

  SELECT TOP 1 @res = [GroupKey]
 	FROM Objects_ObjectGroups_Rel_tbl REL
 	JOIN ObjectGroups_tbl OG ON OG.Id=REL.ObjectGroupId
 	WHERE ObjectId=@objectId AND
        EXISTS (SELECT 1 FROM @objectGroupSelected SEL WHERE SEL.Id=REL.ObjectGroupId)
  ORDER BY [GroupKey]

 END

	-- Return the result of the function
	RETURN @res

END
GO

--------------------------------------------------------------------------------




Print '-- [ObjectsAndProcessData_PrepareIrObjectNameList] ----------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = 'ObjectsAndProcessData_PrepareIrObjectNameList')
Drop Procedure ObjectsAndProcessData_PrepareIrObjectNameList
GO

CREATE PROCEDURE ObjectsAndProcessData_PrepareIrObjectNameList
	-- Add the parameters for the stored procedure here
	@whereIrObjectNameList NVARCHAR(MAX),
	@itemNameColumn SYSNAME,
	@includeLikeSQL NVARCHAR(MAX) OUTPUT,
	@excludeLikeSQL NVARCHAR(MAX) OUTPUT
AS
BEGIN

	-- NEW: 26.08.2015/A.Becker
    -- processes a string containing item name filters
	-- fills two temp tables (#irNameQueryListInclude and #irNameQueryListExclude) with full item names
	-- returns two sql-where strings with ORed Like filters for include and exclude wildcard item names

	SET NOCOUNT ON;

	-- a table for temporary store the splitted string along with metadata [rowNumber] and [isWildcard] indicator.
	DECLARE @irNameQueryListSplit 
		TABLE (irNameQuery NVARCHAR(512), rowNumber INT, isWildcard BIT, escapeChar NVARCHAR(1) NULL)

	-- split the item name list string to the table variable and generate the metadata
	INSERT INTO @irNameQueryListSplit (irNameQuery, rowNumber, isWildcard, escapeChar)
		SELECT CASE WHEN CHARINDEX(N'<LIKE', Value) = 1 
                    THEN dbo.StringTrim_UDF(SUBSTRING(Value, CHARINDEX(N'>', Value)+1, 512))
                    ELSE dbo.StringTrim_UDF(Value) 
               END AS irNameQuery, 
			   rowNumber,
			   CASE WHEN CHARINDEX(N'<LIKE', Value) = 1 
                    THEN 1 
                    ELSE 0 
               END AS isWildcard,
			   CASE WHEN CHARINDEX(N'<LIKE-ESCAPE:', Value) = 1 
                    THEN SUBSTRING(Value, CHARINDEX(N':', Value)+1, 1) 
                    ELSE NULL 
               END AS escapeChar
		FROM dbo.SplitStringToTable_UDF(@whereIrObjectNameList, CHAR(10))
		WHERE LEN(Value) <= 512 AND dbo.StringHasText_UDF(Value) = 1
		OPTION (MAXRECURSION 0)

    -- search for and store the include/exclude marker delimiter (remember its rowNumber)
	DECLARE @excludeMarkerrowNumber INT
		SELECT @excludeMarkerrowNumber = rowNumber
		FROM @irNameQueryListSplit
		WHERE irNameQuery IN (N'!NOT!', N'!not!', N'!NO!', N'!no!')

	-- build include list of wildcards
    DECLARE @wildcardIncludeList TABLE (wildcard NVARCHAR(512), escapeChar NVARCHAR(1) NULL)
	INSERT INTO @wildcardIncludeList
	  SELECT irNameQuery, escapeChar
	  FROM @irNameQueryListSplit
	  WHERE (@excludeMarkerrowNumber IS NULL OR rowNumber < @excludeMarkerrowNumber)
		    AND (isWildcard = 1)

	-- build exclude list of wildcards
	DECLARE @wildcardExcludeList TABLE (wildcard NVARCHAR(512), escapeChar NVARCHAR(1) NULL)
	INSERT INTO @wildcardExcludeList
	  SELECT irNameQuery, escapeChar
	  FROM @irNameQueryListSplit
	  WHERE (rowNumber > @excludeMarkerrowNumber)
		    AND (isWildcard = 1)

	-------------------------

	-- insert all full item names for include into the #temp table
	INSERT INTO #objectNameJoinListInclude (irNameInclude)
		SELECT DISTINCT qITEMS.irNameQuery 
		FROM @irNameQueryListSplit qITEMS
		WHERE (@excludeMarkerrowNumber IS NULL OR qITEMS.rowNumber < @excludeMarkerrowNumber)
		  AND (qITEMS.isWildcard = 0)
		  AND NOT EXISTS (SELECT 1 -- exclude all full item names which are covered by a wildcard search
		                  FROM @wildcardIncludeList
						  WHERE qITEMS.irNameQuery LIKE wildcard ESCAPE escapeChar
				 		 )

	-- insert all full item names for exclude into the #temp table
	INSERT INTO #objectNameJoinListExclude (irNameExclude)
		SELECT DISTINCT qITEMS.irNameQuery 
		FROM @irNameQueryListSplit qITEMS
		WHERE (@excludeMarkerrowNumber IS NOT NULL AND qITEMS.rowNumber > @excludeMarkerrowNumber)
		  AND (qITEMS.isWildcard = 0)
		  AND NOT EXISTS (SELECT 1 -- exclude all full item names which are covered by a wildcard search
		                  FROM @wildcardExcludeList
						  WHERE qITEMS.irNameQuery LIKE wildcard ESCAPE escapeChar
                          ) 

	-------------------------

	-- builds an ORed LIKE string from all include wildcards.
	-- source: 00123%, 0256%
	-- dest:   ItemName LIKE N'00123%' OR ItemName LIKE N'0256%'
	SELECT @includeLikeSQL = COALESCE(@includeLikeSQL + N' OR ', N'') 
	                       -- + N'(' 
						   + @itemNameColumn + N' LIKE ''' + wildcard + '''' + ISNULL(N' ESCAPE ''' + escapeChar + '''', N'')
						   -- + N')'
		FROM @wildcardIncludeList

	-- builds an ORed LIKE string from all exclude wildcards.
	-- source: 00123%, 0256%
	-- dest:   ItemName LIKE N'00123%' OR ItemName LIKE N'0256%'
	SELECT @excludeLikeSQL = COALESCE(@excludeLikeSQL + N' OR ', N'') 
	                       -- + N'(' 
						   + @itemNameColumn + N' LIKE ''' + wildcard + '''' + ISNULL(N' ESCAPE ''' + escapeChar + '''', N'')
						   -- + N')'
		FROM @wildcardExcludeList

END
GO
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------


Print '-- [ObjectsAndProcessData_DisSearch] ----------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectsAndProcessData_DisSearch" and type = "P")
Drop Procedure ObjectsAndProcessData_DisSearch
GO


CREATE PROCEDURE dbo.ObjectsAndProcessData_DisSearch
(
     @disType    varchar(5),      -- *, TXT, PIM, DIM
     @disIncludeFulltext nvarchar(4000),
     @disExcludeFulltext nvarchar(4000),
     @disIncludeLike     nvarchar(4000)
)
AS
BEGIN

	-- Result TABLE #disSearchMatches( objectId UNIQUEIDENTIFIER, disId UNIQUEIDENTIFIER )

	TRUNCATE TABLE #disSearchMatches
	TRUNCATE TABLE #disSearchObjects

    -- default to * when disType is null or empty
    SET @disType = CASE WHEN ISNULL(@disType, N'') = N'' THEN N'*' ELSE @disType END

    -- @disType Mapping
    --  *   -> AllValues
    -- TXT  -> AllText
    -- else -> AllNonText
    DECLARE @fulltextColumn SYSNAME
    SET @fulltextColumn = CASE @disType 
                               WHEN N'*'   THEN N'AllValues'
                               WHEN N'TXT' THEN N'AllText'
                               ELSE             N'AllNonText'
                          END

    DECLARE @searchAnyText BIT = CASE WHEN @disIncludeFulltext = N'*' AND dbo.StringHasText_UDF(@disExcludeFulltext) = 0 THEN 1 ELSE 0 END
    DECLARE @excludeAllText BIT = CASE WHEN @disExcludeFulltext = N'*' THEN 1 ELSE 0 END
    
	DECLARE @disSearchObjectIdFilterAvailable BIT = CASE WHEN EXISTS (SELECT 1 FROM #disSearchObjectIdFilter) THEN 1 ELSE 0 END
	DECLARE @dbg NVARCHAR(MAX) = (SELECT * FROM #disSearchObjectIdFilter FOR XML AUTO)
	print @dbg

    -- simple case 1, result is any object containing any text
    -- no fulltext query needed here
    IF @searchAnyText = 1 
    BEGIN
    
        IF @disSearchObjectIdFilterAvailable = 0
		BEGIN

			-- return all sheets where the requested type has any text
			INSERT INTO #disSearchMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE (DIS.[Type] = @disType OR @disType = N'*') AND 
					(DIS.[Value] IS NOT NULL AND DIS.[Value] <> N'')
			  ORDER BY Id
          
			-- return unique object ids from matches
			INSERT INTO #disSearchObjects(objectId)
			  SELECT DISTINCT ObjIdRef
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id)
			  ORDER BY DIS.ObjIdRef

		END
		ELSE
		BEGIN

			-- return all sheets where the requested type has any text
			INSERT INTO #disSearchMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE (DIS.[Type] = @disType OR @disType = N'*') AND 
					(DIS.[Value] IS NOT NULL AND DIS.[Value] <> N'') AND
					EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY Id
          
			-- return unique object ids from matches
			INSERT INTO #disSearchObjects(objectId)
			  SELECT DISTINCT ObjIdRef
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id) AND
					EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY DIS.ObjIdRef

		END
    
        RETURN 0
    
    END
    
    -- simple case 2, result is any object not containing any text
    -- no fulltext query needed here
    IF @excludeAllText = 1 
    BEGIN
    
        -- collect all sheets where the requested type has any text
        DECLARE @excludeMatches TABLE ( disId UNIQUEIDENTIFIER PRIMARY KEY )

		IF @disSearchObjectIdFilterAvailable = 0
		BEGIN

			INSERT INTO @excludeMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE (DIS.[Type] = @disType OR @disType = N'*') AND 
					(DIS.[Value] IS NOT NULL AND DIS.[Value] <> N'')
			  ORDER BY DIS.Id

			-- return all sheets not contained in the exclude matches
			INSERT INTO #disSearchMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE NOT EXISTS (SELECT 1 FROM @excludeMatches WHERE disId=DIS.Id)
			  ORDER BY Id
          
			-- return unique object ids from matches
			INSERT INTO #disSearchObjects(objectId)
			  SELECT DISTINCT ObjIdRef
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id)
			  ORDER BY DIS.ObjIdRef
    
	END
	ELSE
	BEGIN 
			INSERT INTO @excludeMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE (DIS.[Type] = @disType OR @disType = N'*') AND 
					(DIS.[Value] IS NOT NULL AND DIS.[Value] <> N'') AND
			EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY DIS.Id

			-- return all sheets not contained in the exclude matches
			INSERT INTO #disSearchMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE NOT EXISTS (SELECT 1 FROM @excludeMatches WHERE disId=DIS.Id) AND
			EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY Id
          
			-- return unique object ids from matches
			INSERT INTO #disSearchObjects(objectId)
			  SELECT DISTINCT ObjIdRef
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id) AND
			EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY DIS.ObjIdRef
	END

        RETURN 0
    
    END


	-- STEP: Query fulltext catalog for includes and excludes
    --       Collect resulting IDs in table variable

	DECLARE @fulltextMatches TABLE ( objectId UNIQUEIDENTIFIER, sheet NVARCHAR(128) )
    DECLARE @hasInclude BIT = CASE WHEN dbo.StringHasText_UDF(@disIncludeFulltext) = 1 AND @disIncludeFulltext <> N'*' THEN 1 ELSE 0 END
    DECLARE @hasExclude BIT = CASE WHEN dbo.StringHasText_UDF(@disExcludeFulltext) = 1 AND @disExcludeFulltext <> N'*' THEN 1 ELSE 0 END

    
    -- search with includes only
	IF @hasInclude = 1 AND @hasExclude = 0 
    BEGIN

      IF @fulltextColumn = N'AllValues'
      BEGIN
      
	    IF @disSearchObjectIdFilterAvailable = 0
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllValues, @disIncludeFulltext)
		END
		ELSE
		BEGIN
		    INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllValues, @disIncludeFulltext) AND
			EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
		END

      END 
      ELSE IF @fulltextColumn = N'AllText'
      BEGIN

	    IF @disSearchObjectIdFilterAvailable = 0
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
				SELECT ObjIdRef, inSheet 
				FROM DrawingIndexingSearchFullText_tbl DIS
				WHERE CONTAINS(AllText, @disIncludeFulltext)
		END
		ELSE
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
				SELECT ObjIdRef, inSheet 
				FROM DrawingIndexingSearchFullText_tbl DIS
				WHERE CONTAINS(AllText, @disIncludeFulltext) AND
						EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
		END

          

      END 
      ELSE 
      BEGIN

	   IF @disSearchObjectIdFilterAvailable = 0
		BEGIN

		  INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllNonText, @disIncludeFulltext) 

		END
		ELSE
		BEGIN

		  INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllNonText, @disIncludeFulltext) AND
					EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)

		END
	     
        
      
      END

    END

    -- search with excludes only
    IF @hasInclude = 0 AND @hasExclude = 1 
    BEGIN

      IF @fulltextColumn = N'AllValues'
      BEGIN
      
		IF @disSearchObjectIdFilterAvailable = 0
		BEGIN
			 INSERT INTO @fulltextMatches(objectId, sheet)
				SELECT ObjIdRef, inSheet 
				FROM DrawingIndexingSearchFullText_tbl DIS
				WHERE NOT CONTAINS(AllValues, @disExcludeFulltext)
		END
		ELSE
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
				SELECT ObjIdRef, inSheet 
				FROM DrawingIndexingSearchFullText_tbl DIS
				WHERE NOT CONTAINS(AllValues, @disExcludeFulltext) AND
					EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
		END
         

      END 
      ELSE IF @fulltextColumn = N'AllText'
      BEGIN


	    IF @disSearchObjectIdFilterAvailable = 0
		BEGIN
			 INSERT INTO @fulltextMatches(objectId, sheet)
				SELECT ObjIdRef, inSheet 
				FROM DrawingIndexingSearchFullText_tbl DIS
				WHERE NOT CONTAINS(AllText, @disExcludeFulltext)
		END
		ELSE
		BEGIN
			 INSERT INTO @fulltextMatches(objectId, sheet)
				SELECT ObjIdRef, inSheet 
				FROM DrawingIndexingSearchFullText_tbl DIS
				WHERE NOT CONTAINS(AllText, @disExcludeFulltext) AND
					EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
		END
         

      END 
      ELSE 
      BEGIN
	      
		IF @disSearchObjectIdFilterAvailable = 0
		BEGIN

			INSERT INTO @fulltextMatches(objectId, sheet)
				SELECT ObjIdRef, inSheet 
				FROM DrawingIndexingSearchFullText_tbl DIS
				WHERE NOT CONTAINS(AllNonText, @disExcludeFulltext)

		END
		ELSE
		BEGIN

			INSERT INTO @fulltextMatches(objectId, sheet)
				SELECT ObjIdRef, inSheet 
				FROM DrawingIndexingSearchFullText_tbl DIS
				WHERE NOT CONTAINS(AllNonText, @disExcludeFulltext) AND
						EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)

		END

          
      
      END

    END



    -- search with includes and excludes
    IF @hasInclude = 1 AND @hasExclude = 1 
    BEGIN

      IF @fulltextColumn = N'AllValues'
      BEGIN
      
		IF @disSearchObjectIdFilterAvailable = 0
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllValues, @disIncludeFulltext) AND NOT CONTAINS(AllValues, @disExcludeFulltext)
		END
		ELSE
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllValues, @disIncludeFulltext) AND NOT CONTAINS(AllValues, @disExcludeFulltext) AND
						EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
		END
          

      END 
      ELSE IF @fulltextColumn = N'AllText'
      BEGIN


		IF @disSearchObjectIdFilterAvailable = 0
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllText, @disIncludeFulltext) AND NOT CONTAINS(AllText, @disExcludeFulltext)
		END
		ELSE
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllText, @disIncludeFulltext) AND NOT CONTAINS(AllText, @disExcludeFulltext) AND
						EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
		END
         

      END 
      ELSE 
      BEGIN
		
		IF @disSearchObjectIdFilterAvailable = 0
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllNonText, @disIncludeFulltext) AND NOT CONTAINS(AllNonText, @disExcludeFulltext)
		END
		ELSE
		BEGIN
			INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet 
            FROM DrawingIndexingSearchFullText_tbl DIS
            WHERE CONTAINS(AllNonText, @disIncludeFulltext) AND NOT CONTAINS(AllNonText, @disExcludeFulltext) AND
						EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
		END
          
      
      END

    END
    
    
    
    -- Split like patterns into tables
    DECLARE @includeLikes TABLE (pattern NVARCHAR(1000))
	
    INSERT INTO @includeLikes
	SELECT Value FROM dbo.SplitStringToTable_UDF(@disIncludeLike, N'|')


	IF @disType = '*'
	BEGIN

		-- Return DIS texts matching fulltext and like
		INSERT INTO #disSearchMatches(disId) 
		  SELECT Id
		  FROM dbo.DrawingIndexingSearch_tbl DIS
		  WHERE EXISTS (SELECT 1 FROM @fulltextMatches WHERE objectId = DIS.ObjIdRef AND sheet = DIS.inSheet)
			AND EXISTS (SELECT 1 FROM @includeLikes WHERE DIS.Value LIKE pattern)
		  ORDER BY Id

		-- return unique object ids from matches
		INSERT INTO #disSearchObjects(objectId)
		  SELECT DISTINCT ObjIdRef
		  FROM dbo.DrawingIndexingSearch_tbl DIS
		  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id)
		  ORDER BY DIS.ObjIdRef

	END
	ELSE
	BEGIN

		-- Return DIS texts matching fulltext and like
		INSERT INTO #disSearchMatches(disId) 
		  SELECT Id
		  FROM dbo.DrawingIndexingSearch_tbl DIS
		  WHERE EXISTS (SELECT 1 FROM @fulltextMatches WHERE objectId = DIS.ObjIdRef AND sheet = DIS.inSheet)
			AND EXISTS (SELECT 1 FROM @includeLikes WHERE DIS.Value LIKE pattern)
			AND (DIS.Type = @disType)
		  ORDER BY Id

		-- return unique object ids from matches
		INSERT INTO #disSearchObjects(objectId)
		  SELECT DISTINCT ObjIdRef
		  FROM dbo.DrawingIndexingSearch_tbl DIS
		  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id)
			AND (DIS.Type = @disType)
		  ORDER BY DIS.ObjIdRef

	END
END
GO



Print '-- [ObjectsAndProcessData] ----------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectsAndProcessData" and type = "P")
Drop Procedure ObjectsAndProcessData
GO


CREATE PROCEDURE ObjectsAndProcessData
(
     @WhereObjectClassSQL   nvarchar(10) = null ,
     @whereIrObjectNameList nvarchar(max) = null ,  -- 25.08.2015 /A.Becker renamed from @WhereIrObjectNameSQL because it contains a value list now.
     @WhereObjectDataSQL    nvarchar(max) = null ,
     @WhereProcessDataSQL   nvarchar(max) = null ,
     @SelectTop             integer ,
     @ProcessProcStateCol   nvarchar(32),
     @ObjectGroupsSelectedIdList nvarchar(max),  -- 29.08.2015 /A.Becker added
			  @ReturnObjectIdsOnly   bit = 0,
     @ObjInVisibleColList nvarchar(max) = null, -- 16.02.2018 /A.Becker added
     @disType    varchar(5) = null,             -- 12.12.2018 /A.Becker added for Drawing-Index-Search
     @disIncludeFulltext nvarchar(4000) = null, -- 12.12.2018 /A.Becker added for Drawing-Index-Search
     @disExcludeFulltext nvarchar(4000) = null, -- 12.12.2018 /A.Becker added for Drawing-Index-Search
     @disIncludeLike nvarchar(4000) = null      -- 12.12.2018 /A.Becker added for Drawing-Index-Search
) AS

BEGIN


  -- Hinweise:
  -- Evtl. Indices fuer die #Temp Tabellen erzeugen
  -- Regel: immer die Datens?tze die die Datenmengen einschr?nken zuerst ber?cksistigen
  -- 03.09.2014 20:49 /J.Fes jetzt ohne schema tabellen ohne #zztmpObj1_tbl


  DECLARE @DebugPrintOn bit
 -- set @DebugPrintOn = 1
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='ObjectsAndProcessData'

  DECLARE @ExecuteSQLstr      nvarchar(max)
  DECLARE @WhereObjectsSQLstr nvarchar(max)

  DECLARE @SelectTopSQLstr    nvarchar(max)


  if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@WhereObjectClassSQL' ,        @WhereObjectClassSQL
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@whereIrObjectNameList' ,      @whereIrObjectNameList
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@WhereObjectDataSQL' ,         @WhereObjectDataSQL
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@WhereProcessDataSQL' ,        @WhereProcessDataSQL
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05-@SelectTop' ,                  @SelectTop
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '06-@ProcessProcStateCol' ,        @ProcessProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '07-@ObjectGroupsSelectedIdList' , @ObjectGroupsSelectedIdList
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '08-@ReturnObjectIdsOnly' ,        @ReturnObjectIdsOnly
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '09-@disType' ,                    @disType
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '10-@disIncludeFulltext' ,         @disIncludeFulltext
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '11-@disExcludeFulltext' ,         @disExcludeFulltext
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '12-@disIncludeLike' ,             @disIncludeLike
  end

    -- ### Step: Prepare @visibleColumnNames as table with the column names
    -- #########
  IF @ObjInVisibleColList IS NULL
    SET @ObjInVisibleColList = N'ObjInGroupKeys, ObjInSelectedGroupKeys'

  DECLARE @visibleColumnNames TABLE (colName SYSNAME NOT NULL PRIMARY KEY)
  INSERT INTO @visibleColumnNames (colName)
  SELECT DISTINCT LTRIM(RTRIM(Value))
  FROM dbo.SplitStringToTable_UDF(@ObjInVisibleColList, N',')
  WHERE Value IS NOT NULL

    -- ### Step: Prepare @objectGroupsSelectedIdList by populating an indexed temp table of the values
    -- #########

  DECLARE @objGrpSelGuidList NVARCHAR(MAX)
  DECLARE @objectGroupsSelectedAny BIT = 0
  SET @objGrpSelGuidList = REPLACE(@ObjectGroupsSelectedIdList, N'''', N'')

  DECLARE @objectGroupSelectedIds GUID_TABLE

  INSERT INTO @objectGroupSelectedIds (Id)
  SELECT DISTINCT CAST(Value AS uniqueidentifier)
  FROM dbo.SplitStringToTable_UDF(@objGrpSelGuidList, N',')
  -- 22.02.2018/J.Fes A.Becker change because TRY_CONVERT not avilable in SQL 2008-R2
--   WHERE TRY_CONVERT(UNIQUEIDENTIFIER, Value) IS NOT NULL
  WHERE Value like '[a-fA-F0-9]%'

  IF EXISTS (SELECT 1 FROM @objectGroupSelectedIds)
    SET @objectGroupsSelectedAny = 1



    -- ### Step: Prepare the ObjectName search lists
    -- #########

	-- create the #temp table for the including full item names (no wildcards)
	IF OBJECT_Id('tempdb..#objectNameJoinListInclude') IS NOT NULL 
		DROP TABLE #objectNameJoinListInclude
	CREATE TABLE #objectNameJoinListInclude( irNameInclude NVARCHAR(512),
									 	     UNIQUE CLUSTERED (irNameInclude) ) 

	-- create the #temp table for the excluding full item names (no wildcards)
	IF OBJECT_Id('tempdb..#objectNameJoinListExclude') IS NOT NULL 
		DROP TABLE #objectNameJoinListExclude
	CREATE TABLE #objectNameJoinListExclude( irNameExclude NVARCHAR(512),
										     UNIQUE CLUSTERED (irNameExclude) )

	-- filter for wildcard item name searches (will be NULL if not used)
	DECLARE @objectNameLikeSqlInclude NVARCHAR(MAX)
	DECLARE @objectNameLikeSqlExclude NVARCHAR(MAX)

	-- processes a string containing item name filters
	-- fill two temp tables (#irNameQueryListInclude and #irNameQueryListExclude) with full item names
	-- returns two sql-where strings with ORed Like filters for include and exclude wildcard item names
	EXECUTE dbo.ObjectsAndProcessData_PrepareIrObjectNameList
			@whereIrObjectNameList,
			N'N',  -- shortcut for ItemName, used to keep WHERE LIKE string short as possible
			@objectNameLikeSqlInclude OUTPUT,
			@objectNameLikeSqlExclude OUTPUT




    -- ### Step: DrawingIndexSearch (DIS) processing
    -- #########
    DECLARE @useDIS BIT
	DECLARE @processDISAfterObjectQuery BIT = 0
    SET @disType = ISNULL(@disType, N'*')
    SET @disIncludeFulltext = ISNULL(@disIncludeFulltext, N'')
    SET @disExcludeFulltext = ISNULL(@disExcludeFulltext, N'')
    SET @useDIS = CASE WHEN (dbo.StringHasText_UDF(@disIncludeFulltext) = 1 OR dbo.StringHasText_UDF(@disExcludeFulltext) = 1) THEN 1 ELSE 0 END
    SET @processDISAfterObjectQuery = CASE WHEN (@useDIS=1 AND (@disIncludeFulltext=N'*' OR dbo.StringHasText_UDF(@whereIrObjectNameList)=1)) THEN 1 ELSE 0 END
	IF @useDIS = 1
    BEGIN

        -- create the #temp table for the DIS matches
        IF OBJECT_Id('tempdb..#disSearchMatches') IS NOT NULL 
            DROP TABLE #disSearchMatches
        IF OBJECT_Id('tempdb..#disSearchObjects') IS NOT NULL 
            DROP TABLE #disSearchObjects
        IF OBJECT_Id('tempdb..#disSearchObjectIdFilter') IS NOT NULL 
            DROP TABLE #disSearchObjectIdFilter
        CREATE TABLE #disSearchMatches( disId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY )
        CREATE TABLE #disSearchObjects( objectId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY )
        CREATE TABLE #disSearchObjectIdFilter( objectId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY )

		IF @processDISAfterObjectQuery = 0
			EXECUTE dbo.ObjectsAndProcessData_DisSearch @disType, @disIncludeFulltext, @disExcludeFulltext, @disIncludeLike
        
    END
    



    -- ### Step: Build the SQL parts
    -- #########

  -- SelectTop zusammenstellen
  if @SelectTop > 0
       set @SelectTopSQLstr = N'TOP ' + CONVERT(nvarchar(64), @SelectTop)
  else
       set @SelectTopSQLstr = N''

  if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '06-@SelectTopSQLstr' ,  @SelectTopSQLstr
  end

  -- WHERE zusammenstellen
  set @WhereObjectsSQLstr = ' where' + ' [ObjectClass]=' + '''' +  @WhereObjectClassSQL  + ''''
  
  if @WhereObjectDataSQL <> ''
     begin
       set @WhereObjectsSQLstr = @WhereObjectsSQLstr + ' and (' + @WhereObjectDataSQL + ') '
     end

  if @WhereProcessDataSQL <> ''
     begin
       set @WhereObjectsSQLstr = @WhereObjectsSQLstr + ' and (' + @WhereProcessDataSQL + ') '
     end

  -- add DrawingIndexSearch Filter
  DECLARE @useDIS_SQLPlaceholder NVARCHAR(MAX) = '''useDIS_SQLInject''=''useDIS_SQLInject'''
  DECLARE @useDIS_SQLInject NVARCHAR(MAX) = 'EXISTS (SELECT 1 FROM #disSearchObjects WHERE #disSearchObjects.objectId=Id)'
  IF @useDIS = 1
  BEGIN
    IF @processDISAfterObjectQuery = 0
      set @WhereObjectsSQLstr = @WhereObjectsSQLstr + N' and (' + @useDIS_SQLInject + N') '
    ELSE
      set @WhereObjectsSQLstr = @WhereObjectsSQLstr + N' and (' + @useDIS_SQLPlaceholder + ')'
  END

  -- add selected groups to WHERE
  IF @objectGroupsSelectedAny=1
    BEGIN
      set @WhereObjectsSQLstr = @WhereObjectsSQLstr + N' and (' + 
           N'EXISTS (SELECT 1 FROM dbo.Objects_ObjectGroups_Rel_tbl tREL ' + 
           N'        WHERE tREL.ObjectId=qDATA.Id AND ' +
           N'              EXISTS (SELECT 1 FROM @objectGroupSelectedIds SEL WHERE SEL.Id=tREL.ObjectGroupId)' +
           N'       )'
       + N') '
    END

  -- FROM zusammenstellen
  DECLARE @FromUDFName NVARCHAR(MAX)
  SET @FromUDFName = CASE @WhereObjectClassSQL
                          WHEN N'DS' THEN N'dbo.DS_AndProcessData_UDF' 
                          WHEN N'IR' THEN N'dbo.IRs_AndProcessData_UDF' 
                                     ELSE N'dbo.ITEMs_AndProcessData_UDF'
                     END
  DECLARE @FromSQLstr NVARCHAR(MAX)
  SET @FromSQLstr = dbo.StringFormat_UDF(N' FROM {0}(''{1}'') qDATA ', 
                                         @FromUDFName + ',' + @ProcessProcStateCol)


    -- ### Step: Combine the SQL parts to the final execution SQL string
    -- #########


    DECLARE @hasObjectNameQuery           BIT
    DECLARE @hasObjectNameJoinList        BIT
    DECLARE @hasObjectNameJoinListInclude BIT
    DECLARE @hasObjectNameJoinListExclude BIT
    DECLARE @hasObjectNameLikeSql         BIT
    DECLARE @hasObjectNameLikeSqlInclude  BIT
    DECLARE @hasObjectNameLikeSqlExclude  BIT

    SET @hasObjectNameJoinListInclude = CASE WHEN EXISTS (SELECT 1 FROM #objectNameJoinListInclude) THEN 1 ELSE 0 END
    SET @hasObjectNameJoinListExclude = CASE WHEN EXISTS (SELECT 1 FROM #objectNameJoinListExclude) THEN 1 ELSE 0 END
    SET @hasObjectNameLikeSqlInclude  = dbo.StringHasText_UDF(@objectNameLikeSqlInclude)
    SET @hasObjectNameLikeSqlExclude  = dbo.StringHasText_UDF(@objectNameLikeSqlExclude)
    
    SET @hasObjectNameJoinList = @hasObjectNameJoinListInclude | @hasObjectNameJoinListExclude
    SET @hasObjectNameLikeSql  = @hasObjectNameLikeSqlInclude  | @hasObjectNameLikeSqlExclude
    SET @hasObjectNameQuery    = @hasObjectNameJoinList        | @hasObjectNameLikeSql  

    if @DebugPrintOn = 1
    begin
       DECLARE @hasObjectNameDebug NVARCHAR(MAX)
	   SET @hasObjectNameDebug = dbo.StringFormat_UDF(N'Query={0}, JoinList={1}, JoinListInclude={2}, JoinListExclude={3}, LikeSql={4}, LikeSqlInclude={5}, LikeSqlExclude={6}',
													  CONVERT(NVARCHAR(1), @hasObjectNameQuery)           + N',' +
													  CONVERT(NVARCHAR(1), @hasObjectNameJoinList)        + N',' +
													  CONVERT(NVARCHAR(1), @hasObjectNameJoinListInclude) + N',' +
													  CONVERT(NVARCHAR(1), @hasObjectNameJoinListExclude) + N',' +
													  CONVERT(NVARCHAR(1), @hasObjectNameLikeSql)         + N',' +
													  CONVERT(NVARCHAR(1), @hasObjectNameLikeSqlInclude)  + N',' +
													  CONVERT(NVARCHAR(1), @hasObjectNameLikeSqlExclude)
													  )
	   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '07-@hasObjectNameXx' ,  @hasObjectNameDebug
	   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '07-@objectNameLikeSqlInclude' ,  @objectNameLikeSqlInclude
	   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '07-@objectNameLikeSqlExclude' ,  @objectNameLikeSqlExclude
	   
	   DECLARE @objectNameJoinListCsv NVARCHAR(MAX)
	   DECLARE @objectNameJoinListCount INT

	   SET @objectNameJoinListCsv = NULL
	   SELECT @objectNameJoinListCount=COUNT(1) FROM #objectNameJoinListInclude
       SELECT TOP 100 @objectNameJoinListCsv = ISNULL(@objectNameJoinListCsv + N'; ', N'') + irNameInclude FROM #objectNameJoinListInclude
	   SET @objectNameJoinListCsv = N'TOP 100 of ' + CONVERT(NVARCHAR(10), @objectNameJoinListCount) + N': ' + @objectNameJoinListCsv
       EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '07-#objectNameJoinListInclude' ,  @objectNameJoinListCsv

	   SET @objectNameJoinListCsv = NULL
	   SELECT @objectNameJoinListCount=COUNT(1) FROM #objectNameJoinListExclude
	   SELECT TOP 100 @objectNameJoinListCsv = ISNULL(@objectNameJoinListCsv + N'; ', N'') + irNameExclude FROM #objectNameJoinListExclude
	   SET @objectNameJoinListCsv = N'TOP 100 of ' + CONVERT(NVARCHAR(10), @objectNameJoinListCount) + N': ' + @objectNameJoinListCsv
	   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '07-#objectNameJoinListExclude' ,  @objectNameJoinListCsv

    end

    DECLARE @UnionSql1 NVARCHAR(MAX)
    DECLARE @UnionSql2 NVARCHAR(MAX)

    -- check if any ObjectName filter is needed
    IF  @hasObjectNameQuery=0
    BEGIN
    
        -- no ObjectName filter is needed
        SET @UnionSql1=N'SELECT #InnerTopN# qDATA.* ' +
                       @FromSQLstr + N' ' +
                       @WhereObjectsSQLstr
        
		if @DebugPrintOn = 1
	       EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '08-BuildExecuteSQL(noObjectFilter)' , @UnionSql1
        
    END  

    -- check if any ObjectName join list is needed
    IF @hasObjectNameJoinListInclude=1
    BEGIN
        -- build SQL for ObjectName query with join list
        SET @UnionSql1 = N'SELECT #InnerTopN# qDATA.* ' 
                       + @FromSQLstr + N' '
                       + CASE WHEN @hasObjectNameJoinListInclude=1 THEN N' INNER JOIN #objectNameJoinListInclude tNameInc ON (irNameInclude = qDATA.N COLLATE Latin1_General_CI_AS) ' ELSE N'' END
                       + @WhereObjectsSQLstr
	    if @DebugPrintOn = 1
           EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '08-BuildExecuteSQL(JoinList)' , @UnionSql1
    END
    
    -- check if any ObjectName LIKE list is needed
    IF @hasObjectNameLikeSqlInclude=1
    BEGIN
        -- build SQL for ObjectName query with join list
        SET @UnionSql2 = N'SELECT #InnerTopN# qDATA.* ' 
                       + @FromSQLstr + N' '
                       + @WhereObjectsSQLstr
                       + CASE WHEN @hasObjectNameLikeSqlInclude=1 THEN N' AND (' + @objectNameLikeSqlInclude + ') ' ELSE N'' END
        -- remember this query for the case there is no UNION needed later
	    if @DebugPrintOn = 1
           EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '08-BuildExecuteSQL(LikeSQL)' , @UnionSql2
    END

    -- build resulting SQL
    DECLARE @needUnion BIT
    SET @needUnion = dbo.StringHasText_UDF(@UnionSql1) & dbo.StringHasText_UDF(@UnionSql2) 
    
    IF @needUnion=1 
    BEGIN

        -- UNION with subquery needed
        SET @ExecuteSQLstr = N'       ' + @UnionSql1
                           + CHAR(13) + CHAR(10) + N' UNION ALL ' + CHAR(13) + CHAR(10)
                           + N'       ' + @UnionSql2

        IF @DebugPrintOn = 1
	       EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '08-BuildExecuteSQL(UNION)' , @ExecuteSQLstr
    
    END ELSE BEGIN

        -- single query without UNION (one of the unionSql strings is empty)
        SET @ExecuteSQLstr = ISNULL(@UnionSql1,N'') + ISNULL(@UnionSql2,N'')
    
        IF @DebugPrintOn = 1
	       EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '08-BuildExecuteSQL(SINGLE)' , @ExecuteSQLstr
    END

    -- add objectName excludes to query
    
    -- when any exclude is needed, embed the query in another SELECT to enable excluding on the complete result set
    -- IF (@hasObjectNameJoinListExclude|@hasObjectNameLikeSqlExclude)=1
        SET @ExecuteSQLstr = N'SELECT #InnerTopN# qDATA.* ' + CHAR(13) + CHAR(10) +
                             N' FROM ( ' + CHAR(13) + CHAR(10)
                           + N'       ' + @ExecuteSQLstr
                           + CHAR(13) + CHAR(10) + N' ) AS qDATA ' + CHAR(13) + CHAR(10)
    
    -- add join to #objectNameJoinListExclude when needed 
    IF @hasObjectNameJoinListExclude=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' LEFT  JOIN #objectNameJoinListExclude tNameExc ON (irNameExclude = qDATA.N COLLATE Latin1_General_CI_AS) '

    -- add where if there is any exclude
    IF (@hasObjectNameJoinListExclude|@hasObjectNameLikeSqlExclude)=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' WHERE '

    -- add join-list exclude
    IF @hasObjectNameJoinListExclude=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' tNameExc.irNameExclude IS NULL '

    -- add and if there are both excludes (join and like-string)
    IF (@hasObjectNameJoinListExclude&@hasObjectNameLikeSqlExclude)=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' AND '

    -- add like string excludes
    IF @hasObjectNameLikeSqlExclude=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' NOT (' + @objectNameLikeSqlExclude + ') '
    
    -- 06.11.2015 A.Becker: Improved TOP-N Handling when both include and exclude is requested.
    -- replace the #InnerTopN# placeholders   
    IF (@hasObjectNameJoinListExclude|@hasObjectNameLikeSqlExclude)=1
       -- remove all #InnerTopN# when any excludes are needed
       SET @ExecuteSQLstr=REPLACE(@ExecuteSQLstr, N'#InnerTopN#', N'')
	ELSE
       -- activete all #InnerTopN# when only includes are requested
       SET @ExecuteSQLstr=REPLACE(@ExecuteSQLstr, N'#InnerTopN#', @SelectTopSQLstr)

	-- when the Id column is requested only, encapsulate the query once more to only return this single column
	IF @ReturnObjectIdsOnly = 1
       SET @ExecuteSQLstr=N'SELECT tDATA.Id FROM (' + CHAR(13) + CHAR(10) + @ExecuteSQLstr + CHAR(13) + CHAR(10) + + ') tDATA'
	ELSE
 BEGIN
       DECLARE @innerSQL NVARCHAR(MAX) = @ExecuteSQLstr
       SET @ExecuteSQLstr=N'SELECT ' + @SelectTopSQLstr + N' '

       -- Compute ObjInGroupKeys if column is visible, else return empty strings
       IF EXISTS (SELECT 1 FROM @visibleColumnNames WHERE colName = N'ObjInGroupKeys')
          SET @ExecuteSQLstr=@ExecuteSQLstr + N' dbo.GetGroupKeysForObject_UDF(qDATA.Id, @objectGroupSelectedIds, ''returnAll'') as ObjInGroupKeys, '
       ELSE
          SET @ExecuteSQLstr=@ExecuteSQLstr + N' N'''' as ObjInGroupKeys, '

       -- Compute ObjInSelectedGroupKeys if column is visible, else return empty strings
       IF EXISTS (SELECT 1 FROM @visibleColumnNames WHERE colName = N'ObjInSelectedGroupKeys')
          SET @ExecuteSQLstr=@ExecuteSQLstr + N' dbo.GetGroupKeysForObject_UDF(qDATA.Id, @objectGroupSelectedIds, ''returnSelected'') as ObjInSelectedGroupKeys, '
       ELSE
          SET @ExecuteSQLstr=@ExecuteSQLstr + N' N'''' as ObjInSelectedGroupKeys, '

       -- Add all not-computed columns
       SET @ExecuteSQLstr=@ExecuteSQLstr + N'
                            qDATA.*
                            FROM (' + CHAR(13) + CHAR(10) + @innerSQL + CHAR(13) + CHAR(10) + + ') qDATA'
 END
	
  if @DebugPrintOn = 1
   begin
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '12-@ExecuteSQLstr' , @ExecuteSQLstr
   end

   IF @useDIS = 0
   BEGIN

	   -- Output object list without DIS filtering
	   execute sp_executesql @ExecuteSQLstr, N'@objectGroupSelectedIds GUID_TABLE READONLY', @objectGroupSelectedIds
   
   END
   ELSE
   BEGIN
   
	  -- Collect IDs of all requested Objects (without DIS)
	  DECLARE @ExecuteSQLstrIdOnly NVARCHAR(MAX) = N'SELECT DISTINCT q.Id FROM (' + REPLACE(@ExecuteSQLstr, N'qDATA.*', N'qDATA.Id') + N' ) q'
	  IF @disType = '*'
	  BEGIN
	     SET @ExecuteSQLstrIdOnly = REPLACE(@ExecuteSQLstrIdOnly, @useDIS_SQLPlaceholder, N'EXISTS (SELECT 1 FROM dbo.DrawingIndexingSearchFullText_tbl DISFT WHERE DISFT.ObjIdRef = qDATA.Id)')
	  END	     
	  ELSE
	  BEGIN
	     SET @ExecuteSQLstrIdOnly = REPLACE(@ExecuteSQLstrIdOnly, @useDIS_SQLPlaceholder, N'EXISTS (SELECT 1 FROM dbo.DrawingIndexingSearch_tbl DIST WHERE DIST.ObjIdRef = qDATA.Id AND DIST.TYPE = ''' + @disType + ''')')
	  END
    INSERT INTO #disSearchObjectIdFilter(objectId)
	  execute sp_executesql @ExecuteSQLstrIdOnly, N'@objectGroupSelectedIds GUID_TABLE READONLY', @objectGroupSelectedIds 

	  -- process DIS query here if kannst du dir ausdenken...
	  IF @processDISAfterObjectQuery = 1
	  BEGIN
		EXECUTE dbo.ObjectsAndProcessData_DisSearch @disType, @disIncludeFulltext, @disExcludeFulltext, @disIncludeLike
		SET @ExecuteSQLstr = REPLACE(@ExecuteSQLstr, @useDIS_SQLPlaceholder, @useDIS_SQLInject)
	  END

	  -- Output Objects
	  execute sp_executesql @ExecuteSQLstr, N'@objectGroupSelectedIds GUID_TABLE READONLY', @objectGroupSelectedIds

      -- Output DIS Details
	  SELECT *
      FROM dbo.DrawingIndexingSearch_tbl DIS
      WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE #disSearchMatches.disId=DIS.Id)
	    AND EXISTS (SELECT 1 FROM #disSearchObjectIdFilter WHERE objectId=DIS.ObjIdRef)

   END

END
GO


-------------------------------------------------------
-------------------------------------------------------


Print '-- [ObjectGroups_UpdateRefCounts] -----------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectGroups_UpdateRefCounts" and type = "P")
DROP PROCEDURE ObjectGroups_UpdateRefCounts
GO

CREATE PROCEDURE [dbo].[ObjectGroups_UpdateRefCounts]
	@groupIdList NVARCHAR(MAX)
AS
BEGIN

    -- Calculates and updates the group reference counts
    -- Will be called from JobServer asynchronously for groups with added or removed objects

	SET NOCOUNT ON;

	-- #Step: split input GUId list to table
	DECLARE @groupIdTable TABLE (Id UNIQUEIdENTIFIER)
	IF dbo.StringHasText_UDF(@groupIdList) = 1
	  -- use group Ids from parameter
	  INSERT INTO @groupIdTable
	  SELECT Value FROM dbo.SplitStringToTable_UDF(@groupIdList, N',')
	  WHERE dbo.StringHasText_UDF(Value)=1
	  OPTION (MAXRECURSION 0)
	ELSE
	  -- use all group Ids when parameter is null
	  INSERT INTO @groupIdTable
	  SELECT Id
	  FROM dbo.ObjectGroups_tbl

    -- #Step: insert calculated statistics into temp table
	DECLARE @groupRefCounts TABLE (Id UNIQUEIdENTIFIER, ObjectReferenceCount_ITEM INT, ObjectReferenceCount_IR INT, ObjectReferenceCount_DS INT) 

	INSERT INTO @groupRefCounts(Id, ObjectReferenceCount_ITEM, ObjectReferenceCount_IR, ObjectReferenceCount_DS)
    SELECT tOG.Id,
           (SELECT COUNT(1) FROM dbo.Objects_ObjectGroups_Rel_tbl tREL WITH (NOLOCK)
			                JOIN dbo.Objects_tbl tOBJ WITH (NOLOCK) ON (tOBJ.Id = tREL.ObjectId) 
			                WHERE tREL.ObjectGroupId = tOG.Id AND tOBJ.ObjectClass = N'I') AS ObjectReferenceCount_ITEM,
           (SELECT COUNT(1) FROM dbo.Objects_ObjectGroups_Rel_tbl tREL WITH (NOLOCK)
			                JOIN dbo.Objects_tbl tOBJ WITH (NOLOCK) ON (tOBJ.Id = tREL.ObjectId)
			                WHERE tREL.ObjectGroupId = tOG.Id AND tOBJ.ObjectClass = N'IR') AS ObjectReferenceCount_IR,
           (SELECT COUNT(1) FROM dbo.Objects_ObjectGroups_Rel_tbl tREL WITH (NOLOCK)
			                JOIN dbo.Objects_tbl tOBJ WITH (NOLOCK) ON (tOBJ.Id = tREL.ObjectId)
							WHERE tREL.ObjectGroupId = tOG.Id AND tOBJ.ObjectClass = N'DS') AS ObjectReferenceCount_DS
    FROM dbo.ObjectGroups_tbl tOG 
	WITH (NOLOCK)
	WHERE tOG.Id IN (SELECT Id FROM @groupIdTable)
	
	-- #Step: update groups statistics
	UPDATE 
	       GROUPS
	SET
	       GROUPS.ObjectReferenceCount_ITEM = COUNTS.ObjectReferenceCount_ITEM,
	       GROUPS.ObjectReferenceCount_IR   = COUNTS.ObjectReferenceCount_IR,
		   GROUPS.ObjectReferenceCount_DS   = COUNTS.ObjectReferenceCount_DS
    FROM 
	       ObjectGroups_tbl GROUPS
		   JOIN @groupRefCounts COUNTS ON (COUNTS.Id = GROUPS.Id)

END
GO

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------




Print '-- [ObjectGroups_AddOrRemoveObjects] ----------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectGroups_AddOrRemoveObjects" and type = "P")
Drop Procedure ObjectGroups_AddOrRemoveObjects
GO


CREATE PROCEDURE [dbo].[ObjectGroups_AddOrRemoveObjects]
               @objectGroupsIdList nvarchar(max),
               @addOrRemove        int,  -- >=0 (positive) = add, <0 (negative) = remove
               @DebugPrintOn       bit
AS
BEGIN

    -- 2nd level worker procedure
    -- called by [ObjectGroups_AddObjectsByFilter] and [ObjectGroups_AddOrRemoveObjectsByIdList] 

	SET NOCOUNT ON;

	DECLARE @MyProcName nvarchar(32)
	set @MyProcName='ObjectGroups_AddOrRemoveObjects'
	
	-- split the objectGroup table Ids
	DECLARE @objectGroupIds TABLE (objectGroupId UNIQUEIdENTIFIER, rowNumber INT)
	INSERT INTO @objectGroupIds(objectGroupId, rowNumber)
		SELECT CAST(Value AS UNIQUEIdENTIFIER), rowNumber
		FROM dbo.SplitStringToTable_UDF(@objectGroupsIdList, N',')
		WHERE EXISTS (SELECT 1
		              FROM dbo.ObjectGroups_tbl
					  WHERE Id = CAST(Value AS UNIQUEIdENTIFIER))
		OPTION (MAXRECURSION 0)

	IF @DebugPrintOn = 1
	BEGIN
		DECLARE @count INT

		SELECT @count = COUNT(1) FROM @objectGroupIds
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '11-objectGroupIds.Count' ,   @count

		SELECT @count = COUNT(1) FROM #objectIds
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '12-objectIds.Count' ,        @count 

	END

	-- Loop helper variables
	DECLARE @objectGroupId UNIQUEIdENTIFIER
	DECLARE @nextrowNumber INT
	SELECT @nextrowNumber=MIN(rowNumber) FROM @objectGroupIds

	DECLARE @deltaCount INT

    -- Iterate over all objectGroups
	WHILE 1=1
	BEGIN

		-- Get the next groupId
		SELECT TOP 1 @objectGroupId = objectGroupId, 
		             @nextrowNumber = rowNumber+1
		FROM @objectGroupIds
		WHERE rowNumber >= @nextrowNumber

		-- Break if no more group is available
		IF @@ROWCOUNT = 0
		  BREAK

	    -- Update the current ref count to allow precise delta count after insert or delete
		DECLARE @relCount_ITEM INT
		DECLARE @relCount_IR INT
		DECLARE @relCount_DS INT
		DECLARE @objectGroupIdString NVARCHAR(50)
		  SET @objectGroupIdString = CONVERT(NVARCHAR(50), @objectGroupId)
		  EXEC dbo.ObjectGroups_UpdateRefCounts @objectGroupIdString
		SELECT @relCount_ITEM = ObjectReferenceCount_ITEM,
		       @relCount_IR   = ObjectReferenceCount_IR,
			   @relCount_DS   = ObjectReferenceCount_DS
		  FROM dbo.ObjectGroups_tbl
		  WHERE Id = @objectGroupId

		-- ####################################################
		-- #### HERE IS THE ACTUAL WORKLOAD OF INSERT OR DELETE
		-- ####################################################
		
		IF @addOrRemove >= 0
		BEGIN

			-- Insert all missing objects to the group
			INSERT INTO dbo.Objects_ObjectGroups_Rel_tbl(ObjectGroupId, ObjectId)
				SELECT @objectGroupId, tOId.Id
				FROM (SELECT DISTINCT Id FROM #objectIds) tOId
				WHERE NOT EXISTS (SELECT 1 
								  FROM dbo.Objects_ObjectGroups_Rel_tbl
								  WHERE ObjectGroupId = @objectGroupId AND ObjectId = tOId.Id)
			SET @deltaCount = @@ROWCOUNT

		END ELSE BEGIN

			-- Deleta the selected objects from the group
			DELETE FROM dbo.Objects_ObjectGroups_Rel_tbl
			WHERE ObjectGroupId = @objectGroupId AND 
			      ObjectId IN (SELECT Id FROM #objectIds)
			SET @deltaCount = 0-@@ROWCOUNT

		END

	    -- Update the ref count after update to calculate delta
		DECLARE @relCount_ITEM_Delta INT
		DECLARE @relCount_IR_Delta INT
		DECLARE @relCount_DS_Delta INT
		EXEC dbo.ObjectGroups_UpdateRefCounts @objectGroupIdString
		SELECT @relCount_ITEM_Delta = ObjectReferenceCount_ITEM - @relCount_ITEM,
		       @relCount_IR_Delta   = ObjectReferenceCount_IR   - @relCount_IR,
			   @relCount_DS_Delta   = ObjectReferenceCount_DS   - @relCount_DS
		  FROM dbo.ObjectGroups_tbl
		  WHERE Id = @objectGroupId


		IF @DebugPrintOn = 1
		BEGIN
			DECLARE @message NVARCHAR(MAX)
			SET @message = CONVERT(NVARCHAR(50), @objectGroupId) + N': ' 
			             + CONVERT(NVARCHAR(10), @relCount_ITEM_Delta) + N' ITEMs, '
			             + CONVERT(NVARCHAR(10), @relCount_IR_Delta)   + N' IRs, '
			             + CONVERT(NVARCHAR(10), @relCount_DS_Delta)   + N' DSs, '
			             + CONVERT(NVARCHAR(10), @deltaCount) + N' total.'
			EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20-GroupRefDelta.Count' ,   @message
		END

		-- Remember result
		INSERT INTO #results (groupId, relCount_ITEM_Delta, relCount_IR_Delta, relCount_DS_Delta, relCount_Delta)
		VALUES (@objectGroupId, @relCount_ITEM_Delta, @relCount_IR_Delta, @relCount_DS_Delta, @deltaCount)

	END

END
GO
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------




Print '-- [ObjectGroups_AddObjectsByFilter] ----------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectGroups_AddObjectsByFilter" and type = "P")
Drop Procedure ObjectGroups_AddObjectsByFilter
If Exists (select Name
           from sysobjects
           where Name = "ObjectGroups_AddOrRemoveObjectsByFilter" and type = "P")
Drop Procedure ObjectGroups_AddOrRemoveObjectsByFilter
GO


CREATE PROCEDURE [dbo].[ObjectGroups_AddOrRemoveObjectsByFilter]
               @objectGroupsIdList    nvarchar(max),
			   @WhereObjectClassSQL   nvarchar(10) = null ,
               @whereIrObjectNameList nvarchar(max) = null ,
               @WhereObjectDataSQL    nvarchar(max) = null ,
               @WhereProcessDataSQL   nvarchar(max) = null ,
               @SelectTop             integer ,
               @ProcessProcStateCol   nvarchar(32),
               @addOrRemove           int  -- >=0 (positive) = add, <0 (negative) = remove
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @DebugPrintOn bit
	set @DebugPrintOn = 0
	DECLARE @MyProcName nvarchar(32)
	set @MyProcName='ObjectGroups_AddObjectsByFilter'

	IF @DebugPrintOn = 1
	BEGIN
		-- Clear Debug Table
		EXECUTE zzzzzDebugTbl_Empty
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@objectGroupsIdList' ,        @objectGroupsIdList
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@WhereObjectClassSQL' ,       @WhereObjectClassSQL 
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@whereIrObjectNameList' ,     @whereIrObjectNameList
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@WhereObjectDataSQL' ,        @WhereObjectDataSQL
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05-@WhereProcessDataSQL' ,       @WhereProcessDataSQL
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '06-@SelectTop' ,                 @SelectTop
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '07-@ProcessProcStateCol' ,       @ProcessProcStateCol
	END
	
	-- Get the object Ids from filter conditions into temp table
    CREATE TABLE #objectIds (Id UNIQUEIdENTIFIER)
	INSERT #objectIds(Id)
		EXEC dbo.ObjectsAndProcessData @WhereObjectClassSQL, 
		                               @whereIrObjectNameList, 
									   @WhereObjectDataSQL, 
									   @WhereProcessDataSQL, 
									   @SelectTop, 
									   @ProcessProcStateCol, 
									   @ObjectGroupsSelectedIdList = null, 
									   @ReturnObjectIdsOnly = 1

    -- create the result table
	CREATE TABLE #results (groupId UNIQUEIdENTIFIER, relCount_ITEM_Delta INT, relCount_IR_Delta INT, relCount_DS_Delta INT, relCount_Delta INT)

    -- ##################################
    -- ## execute the worker procedure ##
    -- ##################################
    EXEC ObjectGroups_AddOrRemoveObjects @objectGroupsIdList, @addOrRemove, @DebugPrintOn

	-- return results
	SELECT * FROM #results

END
GO
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------




Print '-- [ObjectGroups_AddOrRemoveObjectsByIdList] ----------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectGroups_AddOrRemoveObjectsByIdList" and type = "P")
Drop Procedure ObjectGroups_AddOrRemoveObjectsByIdList
GO


CREATE PROCEDURE [dbo].[ObjectGroups_AddOrRemoveObjectsByIdList]
               @objectGroupsIdList nvarchar(max),
               @objectsIdList      nvarchar(max),
               @addOrRemove        int  -- >=0 (positive) = add, <0 (negative) = remove
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @DebugPrintOn bit
	set @DebugPrintOn = 0
	DECLARE @MyProcName nvarchar(32)
	set @MyProcName='ObjectGroups_AddOrRemoveObjectsByIdList'

	IF @DebugPrintOn = 1
	BEGIN
		-- Clear Debug Table
		EXECUTE zzzzzDebugTbl_Empty
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@objectGroupsIdList' ,  @objectGroupsIdList
		DECLARE @objectsIdListTrunc NVARCHAR(1024)
		SET @objectsIdListTrunc = SUBSTRING(@objectsIdList, 1, 1020) + '...'
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@objectsIdList' ,       @objectsIdListTrunc
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@addOrRemove' ,         @addOrRemove
	END
	
	-- split the objectsId table Ids
    CREATE TABLE #objectIds (Id UNIQUEIdENTIFIER)
	INSERT INTO #objectIds(Id)
		SELECT DISTINCT CAST(Value AS UNIQUEIdENTIFIER)
		FROM dbo.SplitStringToTable_UDF(@objectsIdList, N',')
		WHERE EXISTS (SELECT 1
		              FROM dbo.Objects_tbl
					  WHERE Id = CAST(Value AS UNIQUEIdENTIFIER))
		OPTION (MAXRECURSION 0)

    -- create the result table
	CREATE TABLE #results (groupId UNIQUEIdENTIFIER, relCount_ITEM_Delta INT, relCount_IR_Delta INT, relCount_DS_Delta INT, relCount_Delta INT)

    -- ##################################
    -- ## execute the worker procedure ##
    -- ##################################
    EXEC ObjectGroups_AddOrRemoveObjects @objectGroupsIdList, @addOrRemove, @DebugPrintOn

	-- return results
	SELECT * FROM #results

END
GO



-------------------------------------------------------
-------------------------------------------------------

Print '-- [ObjectGroups_ClearRelations] -----------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectGroups_ClearRelations" and type = "P")
DROP PROCEDURE ObjectGroups_ClearRelations
GO

CREATE PROCEDURE [dbo].[ObjectGroups_ClearRelations]
	@objectGroupId UNIQUEIdENTIFIER, 
	@objectClass NVARCHAR(10),
	@removedRelationsCount INT OUTPUT
AS
BEGIN

	SET NOCOUNT ON;

	-- remove the relations between groups and objects
	DELETE tREL
	FROM dbo.Objects_ObjectGroups_Rel_tbl tREL
		 INNER JOIN dbo.Objects_tbl tOBJ ON tOBJ.Id = tREL.ObjectId
	WHERE tREL.ObjectGroupId = @objectGroupId 
	  AND (@objectClass IS NULL OR tOBJ.ObjectClass = @objectClass)

	-- remember the count of removed relations
	SET @removedRelationsCount = @@ROWCOUNT

	-- when all objectClassed are cleared, set all reference counts to 0
	IF @objectClass IS NULL
		UPDATE dbo.ObjectGroups_tbl
		SET ObjectReferenceCount_ITEM = 0,
		    ObjectReferenceCount_IR = 0,
			ObjectReferenceCount_DS = 0
		WHERE Id = @objectGroupId

	-- set item reference counts to 0
	IF @objectClass = N'I'
		UPDATE dbo.ObjectGroups_tbl
		SET ObjectReferenceCount_ITEM = 0
		WHERE Id = @objectGroupId

	-- set itemrevision reference counts to 0
	IF @objectClass = N'IR'
		UPDATE dbo.ObjectGroups_tbl
		SET ObjectReferenceCount_IR = 0
		WHERE Id = @objectGroupId

	-- set dataset reference counts to 0
	IF @objectClass = N'DS'
		UPDATE dbo.ObjectGroups_tbl
		SET ObjectReferenceCount_DS = 0
		WHERE Id = @objectGroupId

END
GO

-------------------------------------------------------
-------------------------------------------------------

Print '-- [DataSetSelectByWhereClause] -----------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "DataSetSelectByWhereClause" and type = "P")
Drop Procedure DataSetSelectByWhereClause
GO

CREATE PROCEDURE DataSetSelectByWhereClause @WhereClauseSql nvarchar(max)
AS
BEGIN

/*
  -- Beispiel ObjectsDataSetSelectByWhereClause
  -- EXECUTE DataSetSelectByWhereClause 'where IrName LIKE ''07811002/%'' AND dbo.DS_with_Irs_View.ObjectType IN (''UGMASTER'',''UGPART'')'
*/

  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName = 'DataSetSelectByWhereClause'

  -- Debug
  -- EXECUTE zzzzzDebugTbl_Empty

  set @ExecuteSQLstr = 'SELECT '
                      + ' dbo.DS_with_Irs_View.IrName,'
                      + ' dbo.DS_with_Irs_View.Id,'
                      + ' dbo.DS_with_Irs_View.ObjectCliName,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol01,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol02,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol03,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol04,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol05,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol06,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol07,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol08,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol09,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol10,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol11'
                      + ' FROM'
                      + ' dbo.DS_with_Irs_View'

  if @WhereClauseSql <> ''
             begin
                    set @ExecuteSQLstr = @ExecuteSQLstr + ' ' +  @WhereClauseSql
             end

  -- EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , N'1-@ExecuteSQLstr' , @ExecuteSQLstr
  execute sp_executesql @ExecuteSQLstr

END
GO

Print '-- [Objects_tbl_Item_GetIRS #New: 18.02.2015 J.Fes] ---------------------'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_Item_GetIRS" and type = "P")
DROP PROCEDURE Objects_tbl_Item_GetIRS
GO

-- #Doc: Returns for  @ItemId and  @ObjectFromSiteId all IRS
CREATE PROCEDURE Objects_tbl_Item_GetIRS(@ItemId nvarchar(128), @ObjectFromSiteId nvarchar(32))
As
BEGIN

SELECT
  IR.*
FROM
  dbo.Objects_tbl I WITH (nolock)
  RIGHT OUTER JOIN dbo.Objects_Ir_Rel_tbl ON (I.Id = dbo.Objects_Ir_Rel_tbl.IrId)
  INNER JOIN dbo.Objects_tbl IR ON (dbo.Objects_Ir_Rel_tbl.ObjectId = IR.Id)
WHERE
  I.ObjectClass = 'I'
  and I.ObjectName = @ItemId
  and I.ObjectFromSiteId = @ObjectFromSiteId
end
GO

Print '-- [Objects_tbl_GetAllNeedUpdate #New: 30.11.2016 J.Fes] ----------------'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_GetAllNeedUpdate" and type = "P")
DROP PROCEDURE Objects_tbl_GetAllNeedUpdate
GO

-- #Doc: Returns for  @ObjectFromSiteId all Objects need Update
-- #Lup: 05.12.2016 Returns also for DS Types IrPuid IrItemRev
CREATE PROCEDURE Objects_tbl_GetAllNeedUpdate(@ObjectFromSiteId nvarchar(32))
As
BEGIN
SELECT
  I_IR_DS.*,
  Ir.ObjectPuid AS IrPuid,
  Ir.ObjectName AS IrItemRev
FROM
  dbo.Objects_tbl I_IR_DS WITH (nolock)
  LEFT OUTER JOIN dbo.Objects_Ir_Rel_tbl ON (I_IR_DS.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
  LEFT OUTER JOIN dbo.Objects_tbl Ir ON (dbo.Objects_Ir_Rel_tbl.IrId = Ir.Id)
  AND (Ir.ObjectClass = 'IR')
WHERE
  I_IR_DS.ObjectFromSiteId = @ObjectFromSiteId AND
  I_IR_DS.ObjectSyncTrigger > 0
ORDER BY
  ObjectUgMaster_APLevels ASC,
  ObjectUgMaster_CompCount ASC

end
GO

Print '-- [Objects_tbl_TcMultiSiteCheck_GetToProcess #New: 01.12.2016 J.Fes/S.Gueth] ----'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_TcMultiSiteCheck_GetToProcess" and type = "P")
DROP PROCEDURE Objects_tbl_TcMultiSiteCheck_GetToProcess
GO

-- #Doc: Returns for  @ObjectFromSiteId all Objects need Update
CREATE PROCEDURE Objects_tbl_TcMultiSiteCheck_GetToProcess(@SiteGroupMembers nvarchar(32))
As
BEGIN

 DECLARE @ExecuteSQLstr nvarchar(max)
 --  01.12.2016 J.Fes/S.Gueth
 DECLARE @DebugPrintOn bit
 set @DebugPrintOn = 0
 DECLARE @MyProcName nvarchar(32)
 set @MyProcName='Objects_tbl_TcMultiSiteCheck_GetToProcess'

 -- if @ObjectClass = 'I' set @DebugPrintOn = 1


 if @DebugPrintOn = 1
 begin
  -- Clear Debug Table
  EXECUTE zzzzzDebugTbl_Empty
 end


set @ExecuteSQLstr = N'
 SELECT DISTINCT
    ObjectName
 FROM
    dbo.Objects_tbl with (nolock)
 WHERE
        (ProcStateColMc = ''N'' or ProcStateColMc = ''M'' or ProcStateColMc = ''U'')
    AND dbo.Objects_tbl.ObjectClass = ''I''
    AND dbo.Objects_tbl.ObjectFromSiteID IN (' + @SiteGroupMembers + ')'

 if @DebugPrintOn = 1
 begin
  -- Clear Debug Table
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ExecuteSQLstr' ,    @ExecuteSQLstr
 end


 -- Ausgabe der Tablle
 execute sp_executesql @ExecuteSQLstr

end
GO

-- EXECUTE DataSetSelectByWhereClause 'where IrName LIKE ''07811002/%'' AND dbo.DS_with_Irs_View.ObjectType IN (''UGMASTER'',''UGPART'')'

Print ''
Print '-------------------------------------------------------------------------'
Print ' Procedures imported to #DB_N#'
Print '-------------------------------------------------------------------------'
GO
