Loading...

All project content is available for reading, but you need to be a member of the project for Subversion checkout of source code, or to create/modify any information.
Login if you are a member. Apply here to request membership (open to all).

AdvancedLanguageTool: ChangePageBranchMasterLanguage.sql

File ChangePageBranchMasterLanguage.sql, 3.4 KB (added by mari@…, 8 years ago)

Adding sql script used by module

Line 
1/****** Object:  StoredProcedure [dbo].[cogChangePageBranchMasterLanguage]    Script Date: 04/01/2009 11:49:59 ******/
2IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EPiCode_ChangePageBranchMasterLanguage]') AND type in (N'P', N'PC'))
3DROP PROCEDURE [dbo].[EPiCode_ChangePageBranchMasterLanguage]
4
5/****** Object:  StoredProcedure [dbo].[EPiCode_ChangePageBranchMasterLanguage]    Script Date: 04/01/2009 11:22:32 ******/
6SET ANSI_NULLS ON
7GO
8SET QUOTED_IDENTIFIER ON
9GO
10CREATE PROCEDURE [dbo].[EPiCode_ChangePageBranchMasterLanguage]
11    @page_id    int,
12    @language_branch varchar(20),
13    @recursive bit,
14    @switch_only bit
15AS
16
17DECLARE @language_branch_id nchar(17);
18DECLARE @language_branch_nid int;
19DECLARE @prev_language_branch_nid int;
20DECLARE @child_Id int;
21DECLARE @Fetch int;
22DECLARE @target_lang_version_exist int;
23
24SET @language_branch_nid = (SELECT pkID FROM tblLanguageBranch WHERE (LanguageID = @language_branch))
25SET @language_branch_id = (SELECT LanguageID FROM tblLanguageBranch WHERE (LanguageID = @language_branch))
26SET @prev_language_branch_nid = (SELECT fkMasterLanguageBranchID FROM tblPage WHERE pkID = @page_id)
27SET @target_lang_version_exist = (SELECT count(*) FROM tblPageLanguage WHERE (fkPageID = @page_id AND fkLanguageBranchID = @language_branch_nid))
28
29-- for switch only check if the page exist in the selected language and only then switch
30IF 1 = @switch_only
31  BEGIN
32    print 'target_lang_version_exist' + str(@target_lang_version_exist);
33    IF @target_lang_version_exist > 0
34    BEGIN
35    UPDATE tblPage
36        SET fkMasterLanguageBranchID = @language_branch_nid
37        WHERE pkID = @page_id AND fkMasterLanguageBranchID = @prev_language_branch_nid
38    END
39    ELSE
40    BEGIN
41    RAISERROR
42    (N'The Selected page with ID:%d, cannot switch master branch since there is no version in the selected target language: %s.',
43    11, 1, @page_id,  @language_branch); 
44    END
45  END
46ELSE
47  BEGIN
48    IF @target_lang_version_exist > 0
49    BEGIN
50      RAISERROR
51        (N'The Selected page with ID:%d, cannot be translated since there already is a version in the selected target language: %s.',
52        11, 1, @page_id,  @language_branch); 
53      END
54    ELSE
55      BEGIN
56        UPDATE tblPage
57           SET --fkLanguageID = @language_branch_id,
58              fkMasterLanguageBranchID = @language_branch_nid
59         WHERE pkID = @page_id
60
61        UPDATE tblProperty
62            SET fkLanguageBranchID = @language_branch_nid
63         WHERE fkPageID = @page_id AND fkLanguageBranchID = @prev_language_branch_nid
64
65        UPDATE tblPageLanguage
66           SET fkLanguageBranchID = @language_branch_nid
67              --,fkLanguageID = @language_branch_id
68        WHERE fkPageID = @page_id AND fkLanguageBranchID = @prev_language_branch_nid
69
70        UPDATE tblWorkPage
71           SET fkLanguageBranchID = @language_branch_nid
72              --,fkLanguageID = @language_branch_id
73        WHERE fkPageID = @page_id AND fkLanguageBranchID = @prev_language_branch_nid
74      END
75  END
76
77IF 1 = @recursive 
78BEGIN
79    DECLARE children_cursor CURSOR LOCAL FOR
80        select pkID from tblPage where fkParentID = @page_id
81
82    OPEN children_cursor
83
84    FETCH NEXT FROM children_cursor INTO @child_Id
85    SET @Fetch=@@FETCH_STATUS
86
87    WHILE @Fetch = 0
88    BEGIN
89        print @child_id
90        print @language_branch_id
91        exec [dbo].[EPiCode_ChangePageBranchMasterLanguage] @child_id, @language_branch_id, @recursive, @switch_only
92        FETCH NEXT FROM children_cursor INTO @child_Id
93        SET @Fetch=@@FETCH_STATUS
94    END
95
96    CLOSE children_cursor
97    DEALLOCATE children_cursor
98END