Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Public Module SeriesDAO



    Public Function SiguienteCodigoInversion(ByVal vCodigoEmpresa As String, ByVal vCodigoInstrumentoFinanciero As String) As String
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim valor1 As Integer
        Dim valor2 As Integer
        Dim cmd As SqlCommand
        Dim cmd2 As SqlCommand
        Dim CodigoPIN As String = String.Empty
        Dim CodigoINV As String = String.Empty
        Dim CorrelativoPIN As Match
        Dim CorrelativoINV As Match
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        'SELECT COUNT('A') FROM [dbo].[INV0] WHERE CodInv=@CodInv


        ''''''''''''''''''''''''''
        If (String.IsNullOrEmpty(vCodigoEmpresa) And Not String.IsNullOrEmpty(vCodigoInstrumentoFinanciero)) Then
            cmd = New SqlCommand("SELECT CodInv FROM [dbo].[PIN0] WHERE (DocId = (SELECT MAX(DocId) FROM [dbo].[PIN0] where CodIF='" + vCodigoInstrumentoFinanciero + "')) AND (CodEmpr is NULL OR CodEmpr='' OR CodEmpr=' ')", cn)
            CodigoPIN = cmd.ExecuteScalar
            If String.IsNullOrEmpty(CodigoPIN) Then
                valor1 = 0

            Else

                CorrelativoPIN = Regex.Match(CodigoPIN, "\d+")
                If CorrelativoPIN.Success Then
                    valor1 = CInt(CorrelativoPIN.Value)
                Else
                    valor1 = 0
                End If

            End If


            cmd.Dispose()

            cmd2 = New SqlCommand("SELECT CodInv FROM [dbo].[INV0] WHERE (DocId = (SELECT MAX(DocId) FROM [dbo].[INV0] where CodIF='" + vCodigoInstrumentoFinanciero + "')) AND (CodEmpr is NULL OR CodEmpr='' OR CodEmpr=' ')", cn)
            CodigoINV = cmd2.ExecuteScalar
            If String.IsNullOrEmpty(CodigoINV) Then
                valor2 = 0

            Else

                CorrelativoINV = Regex.Match(CodigoINV, "\d+")
                If CorrelativoINV.Success Then
                    valor2 = CInt(CorrelativoINV.Value)
                Else
                    valor2 = 0
                End If

            End If


            cmd2.Dispose()
            If (valor1 < valor2) Then
                retorno = valor2
            ElseIf (valor1 > valor2) Then
                retorno = valor1
            ElseIf (valor1 = valor2) Then
                retorno = valor1
            End If
            cn.Dispose()
            Dim Codigo As String = String.Empty
            Dim Correlativo As Integer = CInt(retorno) + 1
            Codigo = Format(Correlativo, "00000")

            Return (vCodigoInstrumentoFinanciero + Codigo)





            ''''''''''''''''''''''''''''''''''''''''
        ElseIf (String.IsNullOrEmpty(vCodigoInstrumentoFinanciero) And Not String.IsNullOrEmpty(vCodigoEmpresa)) Then
            cmd = New SqlCommand("SELECT CodInv FROM [dbo].[PIN0] WHERE (DocId = (SELECT MAX(DocId) FROM [dbo].[PIN0] where CodEmpr='" + vCodigoEmpresa + "')) AND (CodIF is NULL OR CodIF='' OR CodIF=' ')", cn)
            CodigoPIN = cmd.ExecuteScalar
            If String.IsNullOrEmpty(CodigoPIN) Then
                valor1 = 0

            Else

                CorrelativoPIN = Regex.Match(CodigoPIN, "\d+")
                If CorrelativoPIN.Success Then
                    valor1 = CInt(CorrelativoPIN.Value)
                Else
                    valor1 = 0
                End If

            End If
            cmd.Dispose()


            cmd2 = New SqlCommand("SELECT CodInv FROM [dbo].[INV0] WHERE (DocId = (SELECT MAX(DocId) FROM [dbo].[INV0] where CodEmpr='" + vCodigoEmpresa + "')) AND (CodIF is NULL OR CodIF='' OR CodIF=' ')", cn)
            CodigoINV = cmd2.ExecuteScalar
            If String.IsNullOrEmpty(CodigoINV) Then
                valor2 = 0

            Else

                CorrelativoINV = Regex.Match(CodigoINV, "\d+")
                If CorrelativoINV.Success Then
                    valor2 = CInt(CorrelativoINV.Value)
                Else
                    valor2 = 0
                End If

            End If
            cmd2.Dispose()

            If (valor1 < valor2) Then
                retorno = valor2
            ElseIf (valor1 > valor2) Then
                retorno = valor1
            ElseIf (valor1 = valor2) Then
                retorno = valor1
            End If

            cn.Dispose()



            Dim Codigo As String = String.Empty
            Dim Correlativo As Integer = CInt(retorno) + 1
            Codigo = Format(Correlativo, "00000")

            Return (vCodigoEmpresa + Codigo)





            ''''''''''''''''''''''''''''''''''
        ElseIf (String.IsNullOrEmpty(vCodigoEmpresa) And String.IsNullOrEmpty(vCodigoInstrumentoFinanciero)) Then
                Return 0
            ElseIf (Not String.IsNullOrEmpty(vCodigoEmpresa) And Not String.IsNullOrEmpty(vCodigoInstrumentoFinanciero)) Then
                Dim suma As Integer

            cmd = New SqlCommand("SELECT CodInv FROM [dbo].[PIN0] WHERE (DocId = (SELECT MAX(DocId) FROM [dbo].[PIN0] where CodInv like '%" + vCodigoEmpresa + vCodigoInstrumentoFinanciero + "%'))", cn)

            CodigoPIN = cmd.ExecuteScalar
            If String.IsNullOrEmpty(CodigoPIN) Then
                valor1 = 0

            Else

                CorrelativoPIN = Regex.Match(CodigoPIN, "\d+")
                If CorrelativoPIN.Success Then
                    valor1 = CInt(CorrelativoPIN.Value)
                Else
                    valor1 = 0
                End If

            End If

            valor1 += 1

                cmd2 = New SqlCommand("SELECT CodInv FROM [dbo].[INV0] WHERE (DocId = (SELECT MAX(DocId) FROM [dbo].[INV0] where CodInv like '%" + vCodigoEmpresa + vCodigoInstrumentoFinanciero + "%'))", cn)
            CodigoINV = cmd2.ExecuteScalar
            If String.IsNullOrEmpty(CodigoINV) Then
                valor2 = 0

            Else

                CorrelativoINV = Regex.Match(CodigoINV, "\d+")
                If CorrelativoINV.Success Then
                    valor2 = CInt(CorrelativoINV.Value)
                Else
                    valor2 = 0
                End If

            End If
            valor2 += 1

                If (valor1 < valor2) Then
                    suma = valor2
                ElseIf (valor1 > valor2) Then
                    suma = valor1
                ElseIf (valor1 = valor2) Then
                    suma = valor1
                End If
            Dim Codigo As String = String.Empty
            Dim Correlativo As Integer = CInt(suma)
            Codigo = Format(Correlativo, "00000")
            Return (vCodigoEmpresa + vCodigoInstrumentoFinanciero + Codigo)
        End If

        '  cmd.CommandType = CommandType.StoredProcedure

        '.Parameters.Add("@vCodEmpresa", SqlDbType.VarChar).Value = vCodigoEmpresa
        'cmd.Parameters.Add("@vCodigoIF", SqlDbType.VarChar).Value = vCodigoInstrumentoFinanciero



    End Function
    Public Sub IncrementarCodigoInversion(ByVal vCodigoEmpresa As String, ByVal vCodigoInstrumentoFinanciero As String)
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand

        Dim res As Integer
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        sql = "UPDATE [dbo].[SER0]
   SET [Correlativo] = [Correlativo]+1
 WHERE [CodEmpr] = @CodEmpr AND [CodIF] = @CodIF"


        cmd = New SqlCommand
        cmd.CommandText = sql
        cmd.Parameters.Add("@CodEmpr", SqlDbType.VarChar).Value = vCodigoEmpresa
        cmd.Parameters.Add("@CodIF", SqlDbType.VarChar).Value = vCodigoInstrumentoFinanciero
        cmd.Connection = cn

        res = cmd.ExecuteNonQuery

        cmd.Dispose()
        cn.Dispose()


    End Sub
End Module