Wednesday, June 15, 2011

sql conn

Imports System.Data.Odbc
Imports System.IO
Imports System.Web

Public Class cmnfunctions
    Dim objdbconn As New dbconn
    Dim objOdbcDataReader As OdbcDataReader
    Dim msSQL As String
    Dim mnResult As Integer

    Public Const DEFAULT_OPTION_ALL = "ALL"
    Public Const DEFAULT_OPTION = "-- Select One --"
    Public Const DEFAULT_OPTION_SHORT = "--"
    Public Const DEFAULT_VALUE = "-1"
    Public Const DEFAULT_ZERO = "0"
    Public Const DEFAULT_EMPTY_STRING = ""

  
    Dim mspassword_suspend As String
    Dim msEncryptedPassword As String
    Dim lnChangeDuration As Integer

    Dim lstLeftDate As String
    Dim mstemp As String()
    Dim msDate As String

    Dim lsTempGid As String
    Dim mserrormessage As String

    Public gsUser_GID As String
    Public gsUser As String
   
    Public Function InitializeTable(ByVal colNames As ArrayList, ByRef xTable As DataTable) As String

        xTable = New DataTable("ddlMultiColumn")

        For iCnt As Integer = 0 To (colNames.Count - 1)
            xTable.Columns.Add(CStr(colNames.Item(iCnt)), Type.GetType("System.String"))
        Next

        Return xTable.TableName

    End Function

    Public Function GetErrMsg(ByVal ErrMsgCode As String) As String
        objdbconn.OpenConn()
        msSQL = "select error_gid,error_code,error_message,error_type from " & _
                " adm_mst_terror where error_code = '" & ErrMsgCode & "'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)
        If objOdbcDataReader.HasRows = True Then
            While objOdbcDataReader.Read
                mserrormessage = objOdbcDataReader.Item("error_message")
            End While
        Else
            mserrormessage = "General Error Occurred"
        End If
        Return mserrormessage
        objOdbcDataReader.Close()
        objdbconn.CloseConn()
    End Function
    Public Function GetRandomString(ByVal length As Integer) As String
        Dim guidResult As String = System.Guid.NewGuid().ToString()
        guidResult = guidResult.Replace("-", String.Empty)
        guidResult = UCase(guidResult)
        If length <= 0 OrElse length > guidResult.Length Then
            Throw New ArgumentException("Length must be between 1 and " & guidResult.Length)
        End If
        Return guidResult.Substring(0, length)
    End Function
    ''//-------------------------------------------------------------------
    ''  Author  : Pavithra
    ''  Date    :
    ''  Purpose : To generate biometric gid
    ''-------------------------------------------------------------------//
    Public Function GetBiometricGID() As String
        Dim lsSeqNo As String
        objdbconn.OpenConn()
        msSQL = "Select max(biometric_gid) as maxgid from hrm_mst_tbiometric"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)
        If objOdbcDataReader.HasRows = True Then
            lsSeqNo = objOdbcDataReader.Item("maxgid").ToString
            If lsSeqNo = "" Then
                lsSeqNo = "1000"
            Else
                lsSeqNo = Val(lsSeqNo) + 1
            End If
        End If
        objOdbcDataReader.Close()
        objdbconn.CloseConn()
        Return lsSeqNo
    End Function


    Public Function getMasterGID(ByVal pModule_Code As String) As String
        Dim lsSeqNo As Integer
        objdbconn.OpenConn()
        msSQL = " select sequence_curval, sequence_format, " & _
                " sequence_code from adm_mst_tsequence " & _
                " where sequence_code = '" & pModule_Code & "'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)

        If objOdbcDataReader.HasRows = True Then
            While objOdbcDataReader.Read
                lsSeqNo = objOdbcDataReader.Item("sequence_curval") + 1
                lsTempGid = pModule_Code & Format(Now(), "yyMMdd") & Format(lsSeqNo, "0000")
            End While
        End If
        objOdbcDataReader.Close()

        msSQL = " update adm_mst_tsequence set " & _
                " sequence_curval = '" & lsSeqNo & "' where " & _
                " sequence_code = '" & pModule_Code & "'"
        mnResult = objdbconn.ExecuteNonQuerySQL(msSQL)
        objdbconn.CloseConn()

        If mnResult = 1 Then
            Return lsTempGid
        Else
            Return "E"
        End If
    End Function
    Public Function getlastGID(ByVal pModule_Code As String) As String
        Dim lsSeqNo As Integer
        objdbconn.OpenConn()
        msSQL = " select sequence_curval, sequence_format, " & _
                " sequence_code from adm_mst_tsequence " & _
                " where sequence_code = '" & pModule_Code & "'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)

        If objOdbcDataReader.HasRows = True Then
            While objOdbcDataReader.Read
                lsSeqNo = objOdbcDataReader.Item("sequence_curval") + 1
                lsTempGid = pModule_Code & Format(Now(), "yyMMdd") & Format(lsSeqNo, "0000")
            End While
        End If
        objOdbcDataReader.Close()
        objdbconn.CloseConn()
        Return lsTempGid
    End Function

    Public Sub getMaxColumnLengths(ByVal colNames As ArrayList, ByVal refTableName As String, ByRef colMaxLengths As ArrayList)

        Dim dr_tdatareader As OdbcDataReader = Nothing

        '' Append query Prefix
        msSQL = "SELECT "

        '' Append Query field sets to get maxlengths
        For iCnt As Integer = 0 To (colNames.Count - 1)
            If (iCnt > 0) Then
                msSQL = msSQL + ", "
            End If
            msSQL = msSQL + "MAX(LENGTH(" + CStr(colNames.Item(iCnt)) + ")) as column" + iCnt.ToString
        Next

        '' Append query suffix tablename
        msSQL = msSQL + " FROM " + refTableName

        ''//------ Fetch the max length of the sepecified columns from database-------->
        objdbconn.OpenConn()

        dr_tdatareader = objdbconn.GetDataReader(msSQL)

        If dr_tdatareader.HasRows Then

            If dr_tdatareader.Read() Then

                For iCnt As Integer = 0 To (colNames.Count - 1)
                    colMaxLengths.Add(CStr(dr_tdatareader.Item("column" + iCnt.ToString)))
                Next

            End If

        End If

        dr_tdatareader.Close()

        objdbconn.CloseConn()
        ''<---------------------------------------------------------------------------//

    End Sub
    ''//-------------------------------------------------------------------
    ''  Author  : Hariharan
    ''  Date    : 16.11.2010
    ''  Purpose : For DropDown Multi Column Purpose
    ''-------------------------------------------------------------------//
    Public Function popMultiColumnDropdown(ByVal keyFieldName As String, ByVal fieldNames As ArrayList, ByVal colMaxLengths As ArrayList, ByRef sqlResults As DataTable) As DataTable

        Dim ddlTable As DataTable
        Dim colNames As New ArrayList

        colNames.AddRange(New String() {"dataTextField", "dataValueField"})

        InitializeTable(colNames, ddlTable)

        '' Iterate through query result table to concatenate columns
        For iCnt As Integer = 0 To (sqlResults.Rows.Count - 1)

            Dim columnString As String = Nothing

            For jCnt As Integer = 0 To colMaxLengths.Count - 1

                If jCnt > 0 Then
                    columnString = columnString + "|"
                End If

                Dim columnValue As String = CStr(sqlResults.Rows(iCnt)(CStr(fieldNames.Item(jCnt))))

                columnString = columnString + String.Format("{0}", concatSpaces(columnValue, CInt(colMaxLengths(jCnt))))

            Next

            ddlTable.Rows.Add(ddlTable.NewRow)

            ddlTable.Rows(ddlTable.Rows.Count - 1)(CStr(colNames.Item(0))) = columnString
            ddlTable.Rows(ddlTable.Rows.Count - 1)(CStr(colNames.Item(1))) = sqlResults.Rows(iCnt)(keyFieldName).ToString

        Next

        Return ddlTable

    End Function
    Public Shared Function concatSpaces(ByVal inputStr As String, ByVal totalLength As Integer) As String
        Dim spaceCount As Integer = totalLength - inputStr.Length
        Dim spaces As String = inputStr
        For i As Integer = 0 To spaceCount - 1
            spaces &= " "
        Next
        Return HttpUtility.HtmlDecode(spaces)
    End Function
    Public Function getCODEGID(ByVal pModule_Code As String) As String
        Dim lsSeqNo As Integer
        objdbconn.OpenConn()
        msSQL = " select sequence_curval, sequence_format, " & _
                " sequence_code from adm_mst_tsequence " & _
                " where sequence_code = '" & pModule_Code & "'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)

        If objOdbcDataReader.HasRows = True Then
            While objOdbcDataReader.Read
                lsSeqNo = objOdbcDataReader.Item("sequence_curval") + 1
                lsTempGid = pModule_Code & Format(lsSeqNo, "0000")
            End While
        End If
        objOdbcDataReader.Close()

        msSQL = " update adm_mst_tsequence set " & _
                " sequence_curval = '" & lsSeqNo & "' where " & _
                " sequence_code = '" & pModule_Code & "'"
        mnResult = objdbconn.ExecuteNonQuerySQL(msSQL)
        objdbconn.CloseConn()

        If mnResult = 1 Then
            Return lsTempGid
        Else
            Return "E"
        End If
    End Function
    Public Function getassetCODEGID(ByVal pModule_Code As String) As String
        Dim lsSeqNo As Integer
        objdbconn.OpenConn()
        msSQL = " select sequence_curval, sequence_format, " & _
                " sequence_code from ams_mst_tassetsequence " & _
                " where sequence_code = '" & pModule_Code & "'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)

        If objOdbcDataReader.HasRows = True Then
            While objOdbcDataReader.Read
                lsSeqNo = objOdbcDataReader.Item("sequence_curval") + 1
                lsTempGid = pModule_Code & Format(lsSeqNo, "0000")
            End While
        End If
        objOdbcDataReader.Close()

        msSQL = " update ams_mst_tassetsequence set " & _
                " sequence_curval = '" & lsSeqNo & "' where " & _
                " sequence_code = '" & pModule_Code & "'"
        mnResult = objdbconn.ExecuteNonQuerySQL(msSQL)
        objdbconn.CloseConn()

        If mnResult = 1 Then
            Return lsTempGid
        Else
            Return "E"
        End If
    End Function
    Public Sub LogForAudit(ByVal strVal As String)
        Try
            Dim sw As StreamWriter = File.AppendText(ConfigurationManager.AppSettings("LOG_FILENAME").ToString)
            sw.WriteLine(Now() & " : " & strVal)
            sw.Close()
        Catch ex As Exception
        End Try
    End Sub
    Public Function getTransGID(ByVal pModule_Code As String, ByVal pTable_Name As String, ByVal pPrimaryKey As String) As String
        Dim lsSeqNo As Integer
        Dim lsTempGid As String

        objdbconn.OpenConn()
        msSQL = " select * from  " & pTable_Name & " where " & pPrimaryKey & " like '%" & Format(Now(), "yyMMdd") & "%'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)

        If objOdbcDataReader.HasRows = False Then
            objOdbcDataReader.Close()
            msSQL = " update adm_mst_tsequence set " & _
                    " sequence_curval = '1' where " & _
                    " sequence_code = '" & pModule_Code & "'"
            mnResult = objdbconn.ExecuteNonQuerySQL(msSQL)
            Return pModule_Code & Format(Now(), "yyMMdd") & "0001"
        End If
        objOdbcDataReader.Close()

        msSQL = " select sequence_curval, sequence_format, " & _
                " sequence_code from adm_mst_tsequence " & _
                " where sequence_code = '" & pModule_Code & "'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)
        If objOdbcDataReader.HasRows = True Then
            objOdbcDataReader.Read()
            lsSeqNo = objOdbcDataReader.Item("sequence_curval") + 1
            lsTempGid = pModule_Code & Format(Now(), "yyMMdd") & Format(lsSeqNo, "0000")
            objOdbcDataReader.Close()
        Else
            objOdbcDataReader.Close()
            Return "E"
        End If

        msSQL = " update adm_mst_tsequence set " & _
                " sequence_curval = '" & lsSeqNo & "' where " & _
                " sequence_code = '" & pModule_Code & "'"
        mnResult = objdbconn.ExecuteNonQuerySQL(msSQL)

        If mnResult = 1 Then
            Return lsTempGid
        Else
            Return "E"
        End If
    End Function

    Protected Friend Function GetDateFormat(ByVal pDate As String) As String
        If Len(pDate) = 0 Then
            Return "0000-00-00"
        End If
        lstLeftDate = Microsoft.VisualBasic.Left(pDate, 3)
        lstLeftDate = Microsoft.VisualBasic.Right(lstLeftDate, 1)
        Select Case lstLeftDate
            Case "-"
                mstemp = Split(pDate, "-")
            Case "/"
                mstemp = Split(pDate, "/")
        End Select

        If mstemp.Length < 3 Then
            Return "E"
        End If

        If Len(mstemp(2)) = 2 Then
            mstemp(2) = "20" & mstemp(2)
        ElseIf Len(mstemp(2)) = 1 Then
            mstemp(2) = "200" & mstemp(2)
        ElseIf Len(mstemp(2)) <> 4 Then
            Return "E"
        End If
        msDate = mstemp(2) & "-" & mstemp(1) & "-" & mstemp(0)
        Return msDate
    End Function
    'Encrypt the password
    Public Function ConvertToAscii(ByVal userText As String) As String
        Dim iIndex As Integer
        Dim lenOfUserString As Integer
        Dim newUserPass As String
        Dim tmp As String
        lenOfUserString = Len(userText)
        newUserPass = ""
        For iIndex = 1 To lenOfUserString
            tmp = Mid(userText, iIndex, 1)
            tmp = Asc(tmp) - lenOfUserString
            newUserPass = newUserPass & IIf(tmp.Length < 3, "0", "") & tmp
        Next
        ConvertToAscii = newUserPass
    End Function
    Public Function ValidatePwd(ByVal pwd As String) As String
        ValidatePwd = ""
        If Len(pwd) < 6 Then
            'ValidatePwd = "LengthError"
            'Exit Function
            Return "ERR_PC006"
        End If
    End Function
    Private Function SearchChar(ByVal SrcStr As String, ByVal SearchStartChar As String, ByVal SearchEndChar As String) As Boolean
        Dim i As Integer, l As Integer
        Dim CharAsc As Integer
        Dim StartCharAsc As Integer
        Dim EndCharAsc As Integer
        l = Len(SrcStr)
        SearchChar = False
        For i = 1 To l
            CharAsc = Asc(Mid(SrcStr, i, 1))
            StartCharAsc = Asc(SearchStartChar)
            EndCharAsc = Asc(SearchEndChar)

            If CharAsc >= StartCharAsc And CharAsc <= EndCharAsc Then
                SearchChar = True
                Exit Function
            End If
        Next i
    End Function

    Private Function SearchStr(ByVal SrcStr As String, ByVal SearchChar As String) As Boolean
        Dim l As Integer
        Const SpecialChar As String = "!#@$+^"

        l = Len(SpecialChar)
        SearchStr = False

        Select Case True
            Case CBool(InStr(1, SrcStr, "!")), _
                 CBool(InStr(1, SrcStr, "#")), _
                 CBool(InStr(1, SrcStr, "@")), _
                 CBool(InStr(1, SrcStr, "$")), _
                 CBool(InStr(1, SrcStr, "+")), _
                 CBool(InStr(1, SrcStr, "^"))
                SearchStr = True
        End Select
    End Function

    Public Function QuoteFilter(ByVal txt As String) As String
        QuoteFilter = Trim(Replace(Replace(Replace(txt, "'", "''"), """", """"""), "\", "/"))
    End Function
    Public Function LoginValidate(ByVal PassUserCode As String, ByVal PassPassword As String) As String
        msEncryptedPassword = ConvertToAscii(PassPassword)

        objdbconn.OpenConn()
        msSQL = "select password_suspend from adm_mst_tuser where user_code='" & PassUserCode & "' "
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)
        If objOdbcDataReader.HasRows = False Then
            objOdbcDataReader.Close()
            objdbconn.CloseConn()
            Return "ERR_LG001"
        End If

        While objOdbcDataReader.Read
            mspassword_suspend = objOdbcDataReader.Item("password_suspend") & ""
        End While
        objOdbcDataReader.Close()

        If mspassword_suspend = "Y" Then
            objdbconn.CloseConn()
            Return "ERR_LG002"
        End If

        msSQL = "select user_code, password_changedate, " & _
                " user_password, password_suspend from " & _
                " adm_mst_tuser where " & _
                " user_code='" & PassUserCode & "' and  " & _
                " user_password = '" & msEncryptedPassword & "'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)

        If objOdbcDataReader.HasRows = False Then
            objOdbcDataReader.Close()
            objdbconn.CloseConn()
            Return "ERR_LG003"
        End If
        objOdbcDataReader.Close()
        objdbconn.CloseConn()
        Return "SUC_LG001"
    End Function

    Public Function ChangePassword(ByVal PassUserCode As String, ByVal PassOldPassword As String, ByVal PassNewPassword As String, ByVal PassConfirmPassword As String) As String
        Dim msEncryptedOldPassword As String
        Dim msEncryptedNewPassword As String

        If Not PassConfirmPassword = PassNewPassword Then
            'ChangePassword = "Confirm Password Mismatch"
            Return "ERR_CP008"
        End If

        msEncryptedOldPassword = ConvertToAscii(PassOldPassword)
        msEncryptedNewPassword = ConvertToAscii(PassNewPassword)

        objdbconn.OpenConn()

        msSQL = " select user_gid from adm_mst_tuser where  " & _
                " user_code = '" & PassUserCode & "' and " & _
                " user_password ='" & msEncryptedOldPassword & "' "
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)
        If objOdbcDataReader.HasRows = False Then
            objOdbcDataReader.Close()
            objdbconn.CloseConn()
            Return "ERR_CP006"
        End If
        objOdbcDataReader.Close()

        msSQL = " select user_gid from adm_mst_tuser where  " & _
                " user_code = '" & PassUserCode & "' and (" & _
                " user_password1 = '" & msEncryptedNewPassword & "' OR " & _
                " user_password2 = '" & msEncryptedNewPassword & "' OR " & _
                " user_password3 = '" & msEncryptedNewPassword & "' OR " & _
                " user_password4 = '" & msEncryptedNewPassword & "' OR " & _
                " user_password5 = '" & msEncryptedNewPassword & "' )"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)
        If objOdbcDataReader.HasRows = True Then
            objOdbcDataReader.Close()
            objdbconn.CloseConn()
            Return "ERR_CP007"
        End If
        objOdbcDataReader.Close()

        msSQL = " update adm_mst_tuser set " & _
                " user_password1 = user_password2, " & _
                " user_password2 = user_password3, " & _
                " user_password3 = user_password4, " & _
                " user_password4 = user_password5, " & _
                " user_password5 = user_password, " & _
                " user_password = '" & msEncryptedNewPassword & "'," & _
                " password_changedate = '" & Format(DateAndTime.Now, "yyyy-MM-dd hh:mm:ss") & "'" & _
                " where user_code='" & PassUserCode & "' "
        mnResult = objdbconn.ExecuteNonQuerySQL(msSQL)
        objdbconn.CloseConn()
        Return "SUC_CP001"
    End Function

    Public Function ResetPassword(ByVal PassUserGID As String, ByVal PassNewPassword As String, ByVal PassConfirmPassword As String)

        Dim msEncryptedNewPassword As String

        If Not PassConfirmPassword = PassNewPassword Then
            'ChangePassword = "Confirm Password Mismatch"
            Return "ERR_CP008"
        End If

        msEncryptedNewPassword = ConvertToAscii(PassNewPassword)

        objdbconn.OpenConn()
        msSQL = " select user_gid from adm_mst_tuser where  " & _
                " user_gid = '" & PassUserGID & "' and (" & _
                " user_password1 = '" & msEncryptedNewPassword & "' OR " & _
                " user_password2 = '" & msEncryptedNewPassword & "' OR " & _
                " user_password3 = '" & msEncryptedNewPassword & "' OR " & _
                " user_password4 = '" & msEncryptedNewPassword & "' OR " & _
                " user_password5 = '" & msEncryptedNewPassword & "' )"

        objOdbcDataReader = objdbconn.GetDataReader(msSQL)
        If objOdbcDataReader.HasRows = True Then
            objOdbcDataReader.Close()
            objdbconn.CloseConn()
            Return "ERR_CP007"
        End If
        objOdbcDataReader.Close()

        msSQL = " update adm_mst_tuser set" & _
                " user_password1 = user_password2, " & _
                " user_password2 = user_password3, " & _
                " user_password3 = user_password4, " & _
                " user_password4 = user_password5, " & _
                " user_password5 = user_password, " & _
                " user_password = '" & msEncryptedNewPassword & "'," & _
                " password_changedate = '" & Format(DateAndTime.Now, "yyyy-MM-dd hh:mm:ss") & "'" & _
                " where user_gid = '" & PassUserGID & "' "
        mnResult = objdbconn.ExecuteNonQuerySQL(msSQL)
        objdbconn.CloseConn()
        Return "SUC_CP001"
    End Function
  

  
    Public Function getBatchGID(ByVal pModule_Code As String) As String
        Dim lsSeqNo As Integer
        objdbconn.OpenConn()
        msSQL = " select sequence_curval, sequence_format, " & _
                " sequence_code from adm_mst_tsequence " & _
                " where sequence_code = '" & pModule_Code & "'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)

        If objOdbcDataReader.HasRows = True Then
            While objOdbcDataReader.Read
                lsSeqNo = objOdbcDataReader.Item("sequence_curval") + 1
                lsTempGid = pModule_Code & Format(lsSeqNo, "0000")
            End While
        End If
        objOdbcDataReader.Close()

        msSQL = " update adm_mst_tsequence set " & _
                " sequence_curval = '" & lsSeqNo & "' where " & _
                " sequence_code = '" & pModule_Code & "'"
        mnResult = objdbconn.ExecuteNonQuerySQL(msSQL)
        objdbconn.CloseConn()

        If mnResult = 1 Then
            Return lsTempGid
        Else
            Return "E"
        End If
    End Function
    Public Shared Function StrToByteArray(ByVal str As String) As Byte()
        'create our encoder
        Dim encoding As New System.Text.ASCIIEncoding()
        'the byte[] containing our string
        Return encoding.GetBytes(str)
    End Function
    Public Shared Function ByteToStringArray(ByVal byteStr As Byte()) As String
        'Create our encoder
        Dim enc As New System.Text.ASCIIEncoding()
        'return our decoded string
        Return enc.GetString(byteStr)
    End Function
    Public Function HttpEncodeFilePath(ByVal ValueToEncode As Object) As String
        'variable to hold our path
        Dim fp As String = TryCast(ValueToEncode, String)
        'variable to olde our byte array
        Dim To64 As String = Convert.ToBase64String(StrToByteArray(fp))
        'return the formatted base64 string
        Return String.Format(To64, HttpUtility.UrlEncode(To64))
    End Function
    Public Function HttpDecodeFilePath(ByVal ValueToDecode As Object) As String
        'Get the value of our filepath and URL Decode it
        Dim fp As String = HttpUtility.UrlDecode(TryCast(ValueToDecode, String))
        'Convert our string back to Base64
        Dim From64 As Byte() = Convert.FromBase64String(fp)
        'return a Base64 string
        Dim Base64 As String = ByteToStringArray(From64)
        Return String.Format(Base64, ByteToStringArray(From64))
    End Function
    Public Function isEmail(ByVal inputEmail As String) As Boolean
        'inputEmail = NulltoString(inputEmail)
        Dim strRegex As String = "^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" & "\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" & ".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"
        Dim re As New Regex(strRegex)
        If re.IsMatch(inputEmail) Then
            Return (True)
        Else
            Return (False)
        End If

    End Function
    Public Function Menus(ByVal module_code As String, ByVal usergid As String)
        Dim strStringBuilder1 As New StringBuilder("")
        Dim grouptype, grouptype2 As String
        Dim i As Integer = 0
        objdbconn.OpenConn()
        msSQL = "select * from adm_mst_tmodule where module_code='" & module_code & "'"
        objOdbcDataReader = objdbconn.GetDataReader(msSQL)
        If objOdbcDataReader.HasRows = True Then
            msSQL = " select a.* from adm_mst_tmodule a,adm_mst_tprivilege b,adm_mst_tuser c where a.module_gid_parent='" & objOdbcDataReader.Item("module_gid").ToString & "'" & _
                " and a.module_gid=b.module_gid and c.user_gid='" & usergid & "' and b.usergroup_gid=c.usergroup_gid order by display_order"
            objOdbcDataReader = objdbconn.GetDataReader(msSQL)
            If objOdbcDataReader.HasRows = True Then
                'panelsubmenus.Controls.Add(New LiteralControl(strStringBuilder1.ToString))
                strStringBuilder1.Remove(0, strStringBuilder1.Length)
                strStringBuilder1.Append("
")
                strStringBuilder1.Append("")
                While objOdbcDataReader.Read
                    If i = 0 Then
                        strStringBuilder1.Append("
")
                        strStringBuilder1.Append("")
                    Else
                        If grouptype = objOdbcDataReader.Item("group_type") Then
                            strStringBuilder1.Append("")
                            strStringBuilder1.Append("")
                strStringBuilder1.Append("")
                strStringBuilder1.Append("
" & objOdbcDataReader.Item("module_name") & "
")
                        strStringBuilder1.Append("")
                        strStringBuilder1.Append("
")
                        strStringBuilder1.Append("
")
                            strStringBuilder1.Append("")
                        Else
                            strStringBuilder1.Append("
" & objOdbcDataReader.Item("module_name") & "
")
                            strStringBuilder1.Append("")
                            strStringBuilder1.Append("
")
                            strStringBuilder1.Append("
")
                            strStringBuilder1.Append("")
                            strStringBuilder1.Append("
")
                            strStringBuilder1.Append("")

                        End If
                    End If
                    i = i + 1
                    grouptype = objOdbcDataReader.Item("group_type")
                End While
                strStringBuilder1.Append("
" & objOdbcDataReader.Item("module_name") & "
")
                            strStringBuilder1.Append("")
                            strStringBuilder1.Append("
")
                            strStringBuilder1.Append("
")
                strStringBuilder1.Append("")
                strStringBuilder1.Append("
")
                'panelsubmenus.Controls.Add(New LiteralControl(strStringBuilder1.ToString))
                'strStringBuilder1.Remove(0, strStringBuilder1.Length)
            End If

        End If
        objdbconn.CloseConn()
        Return strStringBuilder1
    End Function
End Class




No comments: