IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_SetDeleteRecursive') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetDeleteRecursive
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_SoftDeleteEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SoftDeleteEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SoftDeleteEntry
 @PortalId Int,
 @EntryId Int,
 @Delete Bit
AS

EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;

DECLARE @LastVersionId INT
SELECT @LastVersionId=(SELECT LastVersionId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)
DECLARE @Path VARCHAR(4000)
SELECT @Path = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@LastVersionId AND PortalId=@PortalId)
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET Deleted=@Delete
 WHERE LastVersionId=@LastVersionId AND PortalId=@PortalId;
IF @Delete=1
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET Deleted=1
  WHERE Path LIKE @Path + '%' AND PortalId=@PortalId;

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryByTitleInCollection') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
 @PortalId INT,
 @CollectionId INT,
 @Title NVARCHAR(255),
 @Locale NVarChar(10)
AS

SELECT e.*, ISNULL(stLoc.[Text], stDef.[Text]) Title, ISNULL(ltLoc.[Text], ltDef.[Text]) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stDef ON e.EntryId=stDef.ObjectId AND stDef.TypeKey='ENT' AND stDef.SubTypeKey='TITLE' AND stDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stLoc ON e.EntryId=stLoc.ObjectId AND stLoc.TypeKey='ENT' AND stLoc.SubTypeKey='TITLE' AND stLoc.Locale=@Locale
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_LongTexts ltDef ON e.EntryId=ltDef.ObjectId AND ltDef.TypeKey='ENT' AND ltDef.SubTypeKey='REMARKS' AND ltDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_LongTexts ltLoc ON e.EntryId=ltLoc.ObjectId AND ltLoc.TypeKey='ENT' AND ltLoc.SubTypeKey='REMARKS' AND ltLoc.Locale=@Locale
WHERE e.CollectionId=@CollectionId
AND ((stDef.Text=@Title AND stLoc.Text IS NULL) OR (stLoc.Text=@Title AND (stLoc.Locale=@Locale OR @Locale='')))
AND PortalId=@PortalId
AND e.LastVersionId=e.EntryId


GO


