Imports System
Imports System.IO
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Web
Imports System.ServiceModel
Imports System.ServiceModel.Channels
Imports System.DirectoryServices
Imports System.Configuration
Imports System.Web.Configuration
Imports System.Data.SqlClient
Imports System.Data
Imports System.Net

Imports SVSCrypto

Public Class DAL
    Inherits SingletonBaseT(Of DAL)
    Implements IDAL

    Private SqlConnStr_SignSec_DB As String
    Private SqlConnStr_SignDB As String
    Private CommandTimeout As Integer
    Private SessionTimeout As Integer
    Private DebugMode As Boolean
    Private TraceMode As Boolean
    Private StressTestMode As Boolean

    'Public Sub New(ByVal SignDB_ConnectStr As String, ByVal SignSec_ConnectStr As String, ByVal cmdTimeOut As String)
    '    InitConnectionsStr(SignDB_ConnectStr, SignSec_ConnectStr, cmdTimeOut)
    'End Sub

    Public Sub InitConnectionsStr(ByVal SignDB_ConnectStr As String, ByVal SignSec_ConnectStr As String, ByVal cmdTimeOut As Integer)
        Dim keyFileLocation As String = ""
        DAL.Instance.GetAppSetting("SVSDBConnStr_key_location", keyFileLocation)
        Dim cm As New CryptoModule
        Dim keyFile As New StreamReader(keyFileLocation)

        Try
            Log.Instance.Info("Decrypting Connection String")
            Dim key As String = cm.UnMassageKeyString(keyFile.ReadLine)
            Me.SqlConnStr_SignSec_DB = cm.DecryptTripleDES(key, SignSec_ConnectStr)
            Me.SqlConnStr_SignDB = cm.DecryptTripleDES(key, SignDB_ConnectStr)
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try

        Me.CommandTimeout = cmdTimeOut

        Try
            Dim sTmOut As String = "20"
            DAL.Instance.GetAppSetting("session_timeout_min", sTmOut)
            Me.SessionTimeout = Integer.Parse(sTmOut)

            Dim debug_mode As String = "False"
            DAL.Instance.GetAppSetting("debug", debug_mode)
            Me.DebugMode = Boolean.Parse(debug_mode)

            Dim trace_mode As String = "False"
            DAL.Instance.GetAppSetting("trace", trace_mode)
            Me.TraceMode = Boolean.Parse(trace_mode)

            Dim stresstest_mode As String = "False"
            DAL.Instance.GetAppSetting("stresstest", stresstest_mode)
            Me.StressTestMode = Boolean.Parse(stresstest_mode)

        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
    End Sub

    Public Function GetAppSetting(ByVal key As String, ByRef value As String) As Boolean
        Try
            value = WebConfigurationManager.AppSettings(key).ToString
            Return True
        Catch ex As Exception
            Return False
        End Try
        Return False
    End Function

    Public Function IsDebugMode() As Boolean
        Return Me.DebugMode
    End Function

    Public Function IsTraceMode() As Boolean
        Return Me.TraceMode
    End Function

    Public Function IsStressTestMode() As Boolean
        Return Me.StressTestMode
    End Function

    Public Function GetDomainId(id As String) As String
        Try
            Return id.Split(New Char() {"\"c}).First()
        Catch ex As Exception
            Return id
        End Try
    End Function

    Public Function GetUserId(id As String) As String
        Try
            Return id.Split(New Char() {"\"c}).Last()
        Catch ex As Exception
            Return id
        End Try
    End Function

    Public Function FromUnixTime(unixTime As Long) As DateTime
        Dim epoch As DateTime = New DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
        Return epoch.AddSeconds(unixTime)
    End Function

    Public Function ToUnixTime(dtTime As DateTime) As Long
        If dtTime.Ticks = 599266080000000000 Or dtTime = "#1/1/1900#" Then   ' 1900-01-01
            Return 0
        End If
        Dim epoch As DateTime = New DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
        Return (dtTime - epoch).TotalSeconds
    End Function

    Public Function AuthenticatedLDAP(ByVal username As String, ByVal pwd As String) As Boolean
        Try
            Dim _path As String = ConfigurationManager.ConnectionStrings("LDAP_Authen_ConnectStr").ConnectionString
            Using entry As DirectoryEntry = New DirectoryEntry(_path, username, pwd)
                entry.RefreshCache()
            End Using

            Return True
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Return False
        End Try
    End Function

    Public Function GetRandom(ByVal Min As Integer, ByVal Max As Integer) As Integer
        Dim Generator As System.Random = New System.Random()
        Return Generator.Next(Min, Max)
    End Function

    Public Function GetSearchInfoByType(type As String, param1 As String, param2 As String, param3 As String) As DataTable
        Dim dt As DataTable = New DataTable()

        If type = "acct" Then
            type = param1
            param1 = param2
            param2 = param3
        End If

        Dim max_tot As String = "1000"
        DAL.Instance.GetAppSetting("max_search_result", max_tot)

        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = String.Format("EXEC dbo.{0} {1}, {2}, {3}, {4}, {5}", SPNames.SP_GetSearchInfoByType, max_tot, type, param1, param2, param3)
                Log.Instance.Info("GetSearchInfoByType: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try

        Return dt
    End Function

    Public Function ExecuteSQLReader(sql As String) As DataTable
        Dim dt As DataTable = New DataTable()

        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Log.Instance.Info("ExecuteSQLReader: " + sql)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try

        Return dt
    End Function

    Public Function GetDataInfoByType(type As String, number As String, res As AccountDataResponse) As DataTable
        Dim dt As DataTable = New DataTable()
        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = ""

                If IsNothing(res.data.acctNo) Then
                    sql = String.Format("EXEC dbo.{0} '{1}', '{2}', NULL", SPNames.SP_GetDataInfoByType, type, number)
                Else
                    sql = String.Format("EXEC dbo.{0} '{1}', '{2}', '{3}'", SPNames.SP_GetDataInfoByType, type, number, res.data.acctNo)
                End If

                Log.Instance.Info("GetDataInfoByType: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return dt
    End Function

    Public Function GetSignatureByType(type As String, number As String, res As AccountDataResponse) As DataTable
        Dim dt As DataTable = New DataTable()
        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = ""

                If IsNothing(res.data.acctNo) Then
                    sql = String.Format("EXEC dbo.{0} '{1}', '{2}', NULL", SPNames.SP_GetSignatureByType, type, number)
                Else
                    sql = String.Format("EXEC dbo.{0} '{1}', '{2}', '{3}'", SPNames.SP_GetSignatureByType, type, number, res.data.acctNo)
                End If

                Log.Instance.Info("GetSignatureByType: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return dt
    End Function

    Public Function GetSignatureHistory(ByRef type As String, ByRef param1 As String, ByRef param2 As String) As DataTable Implements IDAL.GetSignatureHistory
        Dim dt As DataTable = New DataTable()
        Try

            Dim max_tot As String = "20"
            DAL.Instance.GetAppSetting("max_history_result", max_tot)

            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = String.Format("EXEC dbo.{0} {1}, '{2}', '{3}', '{4}'", SPNames.SP_GetSignHist, max_tot, type, param1, param2)
                Log.Instance.Info("GetSignatureHistory: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return dt
    End Function

    Public Function GetImageHistory(ByRef type As String, ByRef param1 As String, ByRef param2 As String) As DataTable
        Dim dt As DataTable = New DataTable()
        Try

            Dim max_tot As String = "20"
            DAL.Instance.GetAppSetting("max_history_result", max_tot)

            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = String.Format("EXEC dbo.{0} {1}, '{2}', '{3}', '{4}'", SPNames.SP_GetImageHist, max_tot, type, param1, param2)
                Log.Instance.Info("GetImageHistory: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return dt
    End Function

    Public Function GetCustomerImage(docType As String, docId As String, signId As String) As Byte() Implements IDAL.GetCustomerImage
        Dim s As Byte() = Nothing
        Try
            Log.Instance.Info("GetCustomerImage: " + String.Format("{0}-{1}-{2}", docType, docId, signId))
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                s = DirectCast(conn.Execute(SPNames.SP_GetCustImage, CommandType.StoredProcedure, _
                             DBoxSqlConnection.ExecuteType.ExecuteScalar, _
                             docType, docId, signId), Byte())
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return s
    End Function

    Public Function GetSignatureImage(type As String, number As String, signId As String) As Byte()
        Dim s As Byte() = Nothing
        Try
            Log.Instance.Info("GetSignatureImage: " + String.Format("{0}-{1}-{2}", type, number, signId))
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                s = DirectCast(conn.Execute(SPNames.SP_GetSignImage, CommandType.StoredProcedure, _
                             DBoxSqlConnection.ExecuteType.ExecuteScalar, _
                             type, number, signId), Byte())
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return s
    End Function

    Public Function GetSignatureImageHist(type As String, number As String, signId As String, histdate As String) As Byte()
        Dim s As Byte() = Nothing
        Try
            Log.Instance.Info("GetSignatureImageHist: " + String.Format("{0}-{1}-{2}-{3}", type, number, signId, histdate))
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                s = DirectCast(conn.Execute(SPNames.SP_GetSignImageHist, CommandType.StoredProcedure, _
                             DBoxSqlConnection.ExecuteType.ExecuteScalar, _
                             type, number, signId, histdate), Byte())
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return s
    End Function

    Public Function GetAccViewGroup() As DataTable
        Dim sql As String = "SELECT AcctType, AcctNumLength, SortOrder FROM AcctTypeMaster WITH(NOLOCK) ORDER BY SortOrder"
        Return ExecuteSQLReader(sql)
    End Function

    Public Function GetSuspendInfo(type As String, number As String) As DataTable
        Dim dt As DataTable = New DataTable()

        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = String.Format("EXEC dbo.{0} '{1}', '{2}'", SPNames.SP_GetSuspendInfo, type, number)
                Log.Instance.Info("GetSuspendInfo: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try

        Return dt
    End Function

    Public Function GetUserInfo(userid As String) As DataTable
        Dim dt As DataTable = New DataTable()

        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = String.Format("EXEC dbo.{0} '{1}'", SPNames.SP_GetUserInfo, userid)
                Log.Instance.Info("GetUserInfo: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try

        Return dt
    End Function

    Public Function GetSessionInfo(sessionid As String, minimal As Boolean) As DataTable
        Dim dt As DataTable = New DataTable()
        If Not sessionid.Length = 36 Then   ' Must be 36-char len for GUID
            Return dt
        End If
        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = ""

                If minimal Then
                    sql = String.Format("EXEC dbo.{0} NULL, '{1}', '1'", SPNames.SP_GetUserInfo, sessionid)
                Else
                    sql = String.Format("EXEC dbo.{0} NULL, '{1}'", SPNames.SP_GetUserInfo, sessionid)
                End If

                Log.Instance.Info("GetSessionInfo: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return dt
    End Function

    Public Function UpdateSession(sessionid As String) As Boolean
        If Not sessionid.Length = 36 Then   ' Must be 36-char len for GUID
            Return False
        End If
        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = String.Format("EXEC dbo.{0} '{1}'", SPNames.SP_UpdateSession, sessionid)
                Log.Instance.Info("UpdateSession: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim rows As Integer = command.ExecuteNonQuery()
                command.Dispose()
                conn.Close()

                If rows = 1 Then
                    Return True
                End If

            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return False
    End Function

    Public Function GetClientIpAddress(ByRef ip As String) As Boolean
        Try
            Dim remp As String = RemoteEndpointMessageProperty.Name
            Dim imp As MessageProperties = OperationContext.Current.IncomingMessageProperties
            Dim client_ep As RemoteEndpointMessageProperty = CType(imp(remp), RemoteEndpointMessageProperty)
            ip = client_ep.Address
            Return True
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Unable to get client IP Address.")
            Return False
        End Try
    End Function

    Public Function GetWorkStationId(ip As String, ByRef wid As String) As Boolean
        Try
            wid = Dns.GetHostEntry(ip).HostName
            Return True
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Unable to get client workstation Id.")
            Return False
        End Try
    End Function

    Public Function LogView(type As String, number As String, user As String, workid As String, ip As String, teller As String, rmk As String) As Boolean
        If IsStressTestMode() Then
            Return False
        End If

        Dim cifNo As String = "NULL"
        Dim accTy As String = "NULL"
        Dim accNo As String = "NULL"
        Dim proId As String = "NULL"

        If type = "cif" Then
            cifNo = String.Format("""{0}""", number)
        ElseIf type = "time" Then
            proId = String.Format("""{0}""", number)
        Else
            accTy = String.Format("""{0}""", type)
            accNo = String.Format("""{0}""", number)
        End If

        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = String.Format("EXEC dbo.{0} '{1}', '{2}', '{3}', {4}, {5}, {6}, {7}, '{8}', '{9}'",
                SPNames.SP_LogView, user, workid, ip, cifNo, accTy, accNo, proId, teller, rmk)

                Log.Instance.Info("LogView: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                command.ExecuteNonQuery()
                command.Dispose()
                conn.Close()
                Return True
            End Using
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Return False
        End Try
    End Function

    Public Function InsertSession(user As LoginResponse) As String
        Dim dt As DataTable = New DataTable
        Dim session_id As String = ""
        Try
            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                conn.Open()
                Dim sql As String = String.Format("EXEC dbo.{0} '{1}', '{2}', '{3}'",
                SPNames.SP_InsertSession, user.username, user.workstation, user.ip_address)

                Log.Instance.Info("InsertSession: " + sql)
                Dim command As SqlCommand = New SqlCommand(sql, conn.InnerSQLConnection)
                Dim dr As SqlDataReader = command.ExecuteReader()
                dt.Load(dr)
                dr.Close()
                command.Dispose()
                conn.Close()
            End Using

            If (dt.Rows.Count > 0) Then
                session_id = dt.Rows(0).Item("sessionid").ToString
                session_id = session_id.ToUpper()
            End If
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
        End Try
        Return session_id
    End Function

    Public Function IsSessionTimeout(unixtime As Long) As Boolean
        Dim dtLast As DateTime = DAL.Instance.FromUnixTime(unixtime)
        Dim dtDiff = DateTime.Now - dtLast

        If (dtDiff.Minutes > Me.SessionTimeout) Then
            Return True
        End If
        Return False
    End Function

    Public Function DecryptImage(ByRef image As Byte()) As Boolean
        Try
            Dim cm As New SVSCrypto.CryptoModule
            Dim actual_key As String = ""   '"123456789012345678901234"

            Using conn As New DBoxSqlConnection(SqlConnStr_SignDB)
                Dim sql As String = "SELECT keyValue from DBEncryptKey"
                Dim keyBytes0 As Byte() = DirectCast(conn.Execute(sql, CommandType.Text, _
                    DBoxSqlConnection.ExecuteType.ExecuteScalar), Byte())
                actual_key = cm.UnMassageKey(keyBytes0)
            End Using

            image = cm.DecryptTripleDES(actual_key, image)
            Return True
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Return False
        End Try
        Return False
    End Function

End Class