Welcome Guest! To enable all features please Login. New Registrations are disabled.



Go to last post Go to first unread
#1 Posted : Wednesday, May 9, 2012 10:55:09 AM(UTC)

Rank: Administration


Groups: Administration
Joined: 10/8/2008(UTC)
Posts: 188

Was thanked: 1 time(s) in 1 post(s)

I want to build a translation database for Sienn, so parts of the website can be multi-language.
I have done this a couple of times in the past and the basics I still remember but not everything and I didn't documented anything about this.
so this Is my change to document my steps and directly place it in this forum so other people can take advantage of this.
I have to warn you, some of the code I use is lent from a colleague of mine so I didn't type all of this myself.

First of all, we need a table with our keywords and translations
And we need a table to combine the language-id with the table field, we can of course refer this to the language-name in the cms but at that moment you'll get an error when someone changes the language-name.

The Tables We are going to create for this are:
-keyword Text8000 Unique, mandatory
-english Text8000
-dutch Text8000
-<any other language you like> Text8000
(Text8000 can also be another type but the display of Text__X at this moment isn't nice in sienn 4)

-Tid Integer Unique, mandatory
-translationfield Text___X mandatory

Now we will fill the table TranslateLanguages, here we combine the LanguageId with the actual language field in the Translations table

Now I am able to create some code witch is using the to tables above. The translate actions for different parts of the website are basically the same so I'm going to create some global functions for this.
Function Replace_Language(ByVal OrgineleTekst As String, ByVal cid As String, ByVal constring As String) As String
Dim LArray(500) As String
Dim RArray(500) As String
Dim Lindex As Integer = 0
Dim i as integer 

    Dim WorkString As String = OrgineleTekst
    Dim RetString As String = ""
    Dim texterror As Boolean = False
    While WorkString.Length > 0 And microsoft.visualbasic.UCase(WorkString).IndexOf("[[") >= 0 And Not texterror
        Dim Vertaalstuk As String = ""
        Dim Deel2 As String = ""
        Dim PosStartVertaal As Integer = microsoft.visualbasic.UCase(WorkString).IndexOf("[[")
        Dim PosEndVertaal As Integer = microsoft.visualbasic.UCase(WorkString).IndexOf("]]")
        If PosEndVertaal > PosStartVertaal Then
            Vertaalstuk = WorkString.Substring(PosStartVertaal +2, PosEndVertaal - PosStartVertaal -2)
            Deel2 = WorkString.Substring(PosEndVertaal+2)
            If Vertaalstuk.Length > 0 Then
                LArray(Lindex) = "[[" & Vertaalstuk & "]]"
                RArray(Lindex) = ""
                Lindex = Lindex + 1
            End If
            WorkString = Deel2
            texterror = True
        End If
    End While

    If texterror Then
        Return "error" & Lindex.tostring
       retstring = ""
       For i=0 to lindex-1
          retstring = retstring & LArray(i) & "<br>"
        RetString = OrgineleTekst
        If Lindex > 0 Then

            For i = 0 To Lindex - 1
                If RArray(i).Length > 0 Then
                    RetString = RetString.Replace(LArray(i),RArray(i))
                End If
        End If
        Return RetString
    End If
End Function

sub Get_Vertaling_Block(Byval Lindex as integer, ByRef RArray() as string, ByRef LArray() as string, ByVal constring as string)
  Dim Tid as integer = getTid()
  Dim Cid as string = pbw.get_Cid_Currentdomain()
  Dim prefix as string = Cid & "U_"
  Dim language as string = "english"
  Using Con As New SqlConnection(pbw.get_constring)
      Dim Query As String = ""
	  Query &= " Select "
	  Query &= " translationfield "
	  Query &= " from " & prefix  & "TranslateLanguages "
	  Query &= " where Tid=@Tid "
      Dim Com As New SqlCommand(Query, Con)
      Com.Parameters.AddWithValue("@Tid", Tid)
      Dim Reader As SqlDataReader = Com.ExecuteReader()
      while Reader.Read()
      end while
    Catch ex As Exception

    End Try
  End Using
  Dim i As Integer = 0
  Dim Qr As String = " Select keyword, "
  Qr &= language
  Qr = Qr & " from " & prefix & "Translations where "
  For i = 0 To Lindex - 1
    If i > 0 Then
      Qr = Qr & " or "
    End If
    Qr = Qr & " keyword='" & LArray(i) & "' "
    RArray(i) = ""

    Dim Con2 As New SqlConnection(constring)
    Dim Com2 As New SqlCommand(Qr, Con2)
    Dim Readerx As SqlDataReader = Com2.ExecuteReader()
    while Readerx.Read()
      Dim tveld as string = readerx.getstring(0)
      Dim dveld as string = readerx.getstring(1)
      for i=0 to lindex-1
        if LArray(i).trim=tveld.trim then
        end if
    end while
end sub

function getTid() as integer
    Dim Constring As String = pbw.get_constring()
    Dim CID As String = pbw.get_Cid_Currentdomain()
    Dim Taalid As Integer
    Dim Menuid As Integer
    Dim Selectedid As Integer
    Dim Lositem As Boolean 
    Dim Lositemismenu As Boolean 
    Dim Lositemid As Integer 
    Dim Lositemtype As String 
    Dim Printversie As Boolean 
    Dim Wordversie As Boolean = False
    Dim Voettekst As Boolean 
    Dim Stijlnaam As String 
    Dim Stijlindex As String
    Dim Cidalternatief As String =""
    Dim Cidindex As String
    Dim GekozenHMI As Integer 
    Dim GekozenSMI As Integer
    Dim ActieSelectedId As Integer

   pbw.Get_Url_Parameters(httpcontext.current.Session, httpcontext.current.Request, GekozenHMI, GekozenSMI, CID, Cidalternatief, Cidindex, Printversie, Wordversie, Voettekst, Taalid, Menuid, Selectedid, Lositemid, Lositemismenu, Lositemtype, Lositem, "", Stijlnaam, Stijlindex, ActieSelectedId, false, Constring) 
   return Taalid
end function

Now we have created the translate function. Everything between "[[" and "]]" will be translated on places where the translate function is triggered which is actually nowhere at this point.
But we're going to implement this for views and forms

UserEscape view text layout start
HeaderTextLayout = Replace_Language(HeaderTextLayout,cid,constring)
SubheaderTextLayout = Replace_Language(SubheaderTextLayout,cid,constring)
Bodytextlayout = Replace_Language(Bodytextlayout,cid,constring)
Bodyeventextlayout = Replace_Language(Bodyeventextlayout,cid,constring)
subfootertextlayout = Replace_Language(subfootertextlayout,cid,constring)
footertextlayout = Replace_Language(footertextlayout,cid,constring)

UserEscape view text layout end
Finallayout = Replace_Language(Finallayout , Cid, constring)

UserEscape form text layout
TextLayout = Replace_Language(TextLayout,cid,constring)
TextButton = Replace_Language(TextButton,cid,constring)

UserEscape form datacheck
Dim RowStartPos as integer = startpos 
Dim Fieldcount as integer = 20
Dim i as integer = 0
 For i=RowStartPos to RowStartPos + Fieldcount
  Dim StartPosError as integer = definition(i).indexof("[[")
  Dim EndPosError as integer = definition(i).indexof("]]")
   if StartPosError<EndPosError then
     Dim ErrorString as string = definition(i).substring(StartPosError+2,EndPosError-StartPosError-2)
     Dim NewError as string = Replace_Language("[[" & ErrorString & "]]",cid,constring)
'     Dim NewError as string = "testerror" 'Replace here the translation of ErrorString into NewError by function
     definition(i) = definition(i).replace("[[" & ErrorString & "]]",NewError)
   end if
End Try

UserEscape form before mail
MailText = Replace_Language(MailText,cid,constring)

UserEscape form overrule dropdown list
dim aantal as integer = Dropdown.items.count
dim i as integer = 0
for i = 0 to (aantal-1)
   Dropdown.items(i).text = Replace_Language(Dropdown.items(i).text,cid,constring)

This is it, now all views and forms are automatically translated if you use the right tags.

Edited by user Wednesday, May 9, 2012 12:30:16 PM(UTC)  | Reason: Not specified

bart attached the following image(s):

Wanna join the discussion?! Login to your Sienn Forum forum account. New Registrations are disabled.

#2 Posted : Monday, February 18, 2019 11:23:35 AM(UTC)

Rank: Administration


Groups: Administration
Joined: 10/8/2008(UTC)
Posts: 188

Was thanked: 1 time(s) in 1 post(s)
this morning I rewrote the translate method a bit to improve performance.
the Get_Vertaling_Block method is removed at all.
and Replace_Language looks like the following:
Function Replace_Language(ByVal Text As String, ByVal constring As String) As String
    Dim Cid as string = pbw.get_Cid_Currentdomain()
    Dim prefix as string = Cid & "U_"
    Dim language as string = GetLanguageField()
    Dim pattern As String = "\[\[*.*?\]\]"
    Dim whereString as string = ""

    For Each match As string In System.Text.RegularExpressions.Regex.Matches(Text, pattern).OfType(Of System.Text.RegularExpressions.Match)().[Select](Function(m) m.Groups(0).Value).Distinct()
        Dim addMatch as string = "'" & match.Replace("'","''") &  "'"
        if not whereString.Contains(addMatch) Then
            If Not string.IsNullOrEmpty(whereString) Then
                whereString &= ","
            End If
            whereString &= addMatch
        End If
    If Not string.IsNullOrEmpty(whereString) Then
        whereString = "Where keyword in (" & whereString & ")"
        Dim ds As New DataSet
        Using Con As New SqlConnection(Constring)
            Dim Qry as string = ""
            Qry &= " Select keyword, "
            Qry &= language
            Qry &= " from " & prefix & "Translations "
            Qry &= whereString
            Dim Com As New SqlCommand(Qry, Con)
            Dim adt As New SqlDataAdapter()
            adt.SelectCommand = Com 
        Catch ex As Exception
        End Try
      End Using
      if ds.Tables.count > 0 then
        For Each row As DataRow In ds.tables(0).Rows
           Text = Text.Replace(row("keyword"),row(language))
      End If
    End If
    If language = "nl" Then
        For Each match As string In System.Text.RegularExpressions.Regex.Matches(Text, pattern).OfType(Of System.Text.RegularExpressions.Match)().[Select](Function(m) m.Groups(0).Value).Distinct()
            Text = Text.Replace(match,match.Replace("[[","").Replace("]]",""))
    End if
  Return Text
End Function

- obsolete paramter Cid is removed
- tags are retrieved by regex
- double matches are removed
- only 1 query to select all matches
- for dutch language(can be changed) translation tags will be cut off,
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.