Imports System.Data.SqlClient
Imports BD.ABD
Imports Funcion.Funcion
Imports System.ComponentModel.Design.Serialization
Imports System.ComponentModel

Public Class DetPc
    Dim ser As New BD.ABD
    Dim max As New Funcion.Funcion
    Dim sql As String
    Dim cmd As SqlCommand
    Dim ds As DataSet
    Dim dr As SqlDataReader
    Dim da As SqlDataAdapter
    Dim cnn As SqlConnection
    Dim myTrans As SqlTransaction
    Dim strFecha As String
    Dim formato As New System.Globalization.CultureInfo("es-PY", True)
    Dim fecha, b As System.DateTime

    Public Function Insertar(ByVal Codigo_Empresa As Long, ByVal Codigo_Sucursal As Long, ByVal Codigo_Ip As String, ByVal Codigo_Comprobante As Long, ByVal Rango1 As Long, ByVal Rango2 As Long, ByVal ULTIMO As Long, ByVal Impresora As String, ByVal Codigo_Usuario As Long) As Boolean
        cnn = New SqlConnection
        cnn = ser.Abrir()
        'crea un comando y le asigna la conección
        Dim cmd As SqlCommand = cnn.CreateCommand()

        'comienza la Transacción y le asigna un nombre 
        'le asigna una conección al comando SQL
        'le asigna una transancción al comando SQL
        myTrans = cnn.BeginTransaction(IsolationLevel.ReadCommitted, "InsertarDetPc")
        cmd.Connection = cnn
        cmd.Transaction = myTrans
        Try
            sql = ""
            sql = "INSERT INTO DETPC(CODEMPRESA,CODSUCURSAL, IP,CODCOMPROBANTE, RANGO1, RANGO2, ULTIMO, IMPRESORA) VALUES ( " & Codigo_Empresa & ", " & Codigo_Sucursal & ", '" & Codigo_Ip & "', " & Codigo_Comprobante & " ," & Rango1 & ", " & Rango2 & "," & ULTIMO & ",'" & Impresora & "')"
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            myTrans.Commit()
            'si llega hasta acá es que se ha grabado correctamente
            '#######
            '# OK  #
            '#######
            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("InsertarDetPc")
            Catch ex As SqlException
                If Not myTrans.Connection Is Nothing Then
                    'cuando ocurre un  error de la transacción
                    Return False
                End If
            End Try
            Return False
            'cuando ocurre un error con el comit
        Finally

            ser.Cerrar(cnn)
        End Try
    End Function

    Public Function ExisteDetPc(ByVal Codigo_Ip As String, ByVal Codigo_Sucursal As Long, ByVal Codigo_Empresa As Long, ByVal Codigo_Comprobante As Long) As Boolean
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "select case " & _
        "when (SELECT count(*) as cantidad FROM DETPC where codempresa = " & Codigo_Empresa & " and ip = '" & Codigo_Ip & "' and codsucursal = " & Codigo_Sucursal & " and codcomprobante = " & Codigo_Comprobante & ") <= 0  then 'False' " & _
        "when (SELECT count(*) as cantidad FROM DETPC where codempresa = " & Codigo_Empresa & " and ip = '" & Codigo_Ip & "' and codsucursal = " & Codigo_Sucursal & " and codcomprobante = " & Codigo_Comprobante & ") > 0  then 'True' " & _
        "end as Existe "
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Try
                dr.Read()
                If dr("Existe") = "True" Then
                    Return True
                Else
                    Return False
                End If
            Catch
            Finally
                dr.Close()

                ser.Cerrar(cnn)
            End Try
        Finally
        End Try
    End Function

    Public Function Eliminar(ByVal Codigo_Empresa As Long, ByVal Codigo_Sucursal As Long, ByVal Codigo_Ip As String, ByVal Codigo_Comprobante As Long) As Boolean
        cnn = New SqlConnection
        cnn = ser.Abrir()
        'crea un comando y le asigna la conección
        Dim cmd As SqlCommand = cnn.CreateCommand()
        'comienza la Transacción y le asigna un nombre 
        'le asigna una conección al comando SQL
        'le asigna una transancción al comando SQL
        myTrans = cnn.BeginTransaction(IsolationLevel.ReadCommitted, "EliminarDetPc")
        cmd.Connection = cnn
        cmd.Transaction = myTrans
        Try
            'sintaxis SQL
            sql = ""
            sql = "DELETE FROM DETPC WHERE IP = '" & Codigo_Ip & "' AND CODEMPRESA = " & Codigo_Empresa & " AND CODSUCURSAL = " & Codigo_Sucursal & " AND CODCOMPROBANTE = " & Codigo_Comprobante & ""  'sintaxis sql
            'asigna sintaxis SQL al comando SQL y ejecuta el comando
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

            myTrans.Commit()
            'si llega hasta acá es que se ha grabado correctamente
            '#######
            '# OK  #
            '#######
            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("EliminarDetPc")
            Catch ex As SqlException
                If Not myTrans.Connection Is Nothing Then
                    'cuando ocurre un  error de la transacción
                    Return False
                End If
            End Try
            Return False
            'cuando ocurre un error con el comit
        Finally

            ser.Cerrar(cnn)
        End Try
    End Function

    Public Function Modificar(ByVal Codigo_Empresa As Long, ByVal Codigo_Sucursal As Long, ByVal Codigo_Ip As String, ByVal Codigo_Comprobante As Long, ByVal Rango1 As Long, ByVal Rango2 As Long, ByVal ultimo As Long, ByVal Impresora As String, ByVal Codigo_Usuario As Long) As Boolean
        cnn = New SqlConnection
        cnn = ser.Abrir()
        Dim cmd As SqlCommand = cnn.CreateCommand()
        'comienza la Transacción y le asigna un nombre 
        'le asigna una conección al comando SQL
        'le asigna una transancción al comando SQL
        myTrans = cnn.BeginTransaction(IsolationLevel.ReadCommitted, "ModificarPc")
        cmd.Connection = cnn
        cmd.Transaction = myTrans
        Try
            sql = ""
            sql = "UPDATE DETPC SET RANGO1 = " & Rango1 & ", RANGO2 = " & Rango2 & ", ULTIMO = " & ultimo & ", IMPRESORA = '" & Impresora & "' " & _
            "WHERE IP = '" & Codigo_Ip & "' AND CODEMPRESA = " & Codigo_Empresa & " AND CODSUCURSAL = " & Codigo_Sucursal & " AND CODCOMPROBANTE = " & Codigo_Comprobante & "" 'sintaxis sql
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()                             'ejecuta el comando
            myTrans.Commit()
            'si llega hasta acá es que se ha grabado correctamente
            '#######
            '# OK  #
            '#######
            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("ModificarPc")
            Catch ex As SqlException
                If Not myTrans.Connection Is Nothing Then
                    'cuando ocurre un  error de la transacción
                    Return False
                End If
            End Try
            Return False
            'cuando ocurre un error con el comit
        Finally

            ser.Cerrar(cnn)
        End Try
    End Function

    Public Function CargarDetPc(ByVal Codigo_empresa As Long, ByVal Codigo_Ip As String, ByVal Codigo_Sucursal As Long) As DataSet
        cnn = New SqlConnection
        cnn = ser.Abrir()
        da = New SqlDataAdapter

        sql = ""
        sql = "SELECT ISNULL(D.CODCOMPROBANTE,0) AS CODCOMPROBANTE, RTRIM(T.NUMTIPOCOMPRO) AS NUMTIPOCOMPRO, " & _
            "RTRIM(T.DESCOMPROBANTE) AS DESCOMPROBANTE, RTRIM(D.IMPRESORA) AS IMPRESORA, " & _
            "D.RANGO1, D.RANGO2, D.ULTIMO, D.CODSUCURSAL, D.IP " & _
            "FROM DETPC D INNER JOIN TIPOCOMPROBANTE T ON D.CODCOMPROBANTE = T.CODCOMPROBANTE " & _
            "WHERE D.CODEMPRESA = " & Codigo_empresa & " AND D.CODSUCURSAL = " & Codigo_Sucursal & "  AND D.IP = '" & Codigo_Ip & "'"

        da = New SqlDataAdapter(sql, cnn)
        ds = New DataSet

        da.Fill(ds)

        ser.Cerrar(cnn)

        Return ds
    End Function

    Public Function Ultimo(ByVal Codigo_comprobante As Long, ByVal Codigo_Ip As String, ByVal Codigo_empresa As Long, ByVal Codigo_Sucursal As Long) As SqlDataReader
        Dim temp As Integer
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "SELECT " & _
        "isnull(D.ULTIMO,0) AS ULTIMO, isnull(D.RANGO1,0) AS RANGO1, isnull(D.RANGO2,0) AS RANGO2, isnull(RTRIM(D.IMPRESORA),'') AS IMPRESORA " & _
        "FROM DETPC D WHERE D.codempresa = " & Codigo_empresa & " " & _
        "And D.CODSUCURSAL = " & Codigo_Sucursal & " " & _
        "AND D.IP = '" & Codigo_Ip & "' AND D.CODCOMPROBANTE = " & Codigo_comprobante & ""
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Return dr
        Finally
        End Try

    End Function

    Public Function ModificarUltimo(ByVal Codigo_Empresa As Long, ByVal Codigo_Sucursal As Long, ByVal Codigo_Ip As String, ByVal Codigo_Comprobante As Long, ByVal ultimo As Long) As Boolean
        cnn = New SqlConnection
        cnn = ser.Abrir()
        Dim cmd As SqlCommand = cnn.CreateCommand()
        'comienza la Transacción y le asigna un nombre 
        'le asigna una conección al comando SQL
        'le asigna una transancción al comando SQL
        myTrans = cnn.BeginTransaction(IsolationLevel.ReadCommitted, "ModificarUltimoPc")
        cmd.Connection = cnn
        cmd.Transaction = myTrans
        Try
            sql = ""
            sql = "UPDATE DETPC SET ULTIMO = " & ultimo & " " & _
            "WHERE IP = '" & Codigo_Ip & "' AND CODEMPRESA = " & Codigo_Empresa & " AND CODSUCURSAL = " & Codigo_Sucursal & " AND CODCOMPROBANTE = " & Codigo_Comprobante & "" 'sintaxis sql
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()                             'ejecuta el comando
            myTrans.Commit()
            'si llega hasta acá es que se ha grabado correctamente
            '#######
            '# OK  #
            '#######
            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("ModificarUltimoPc")
            Catch ex As SqlException
                If Not myTrans.Connection Is Nothing Then
                    'cuando ocurre un  error de la transacción
                    Return False
                End If
            End Try
            Return False
            'cuando ocurre un error con el comit
        Finally

            ser.Cerrar(cnn)
        End Try
    End Function

    Public Function ModificarDescuento(ByVal Codigo_Empresa As Long, ByVal Codigo_Sucursal As Long, ByVal Codigo_Ip As String, ByVal Codigo_Comprobante As Long, ByVal descuento As String, ByVal totaldescuento As String, ByVal Codigo_Usuario As Long) As Boolean
        cnn = New SqlConnection
        cnn = ser.Abrir()
        Dim cmd As SqlCommand = cnn.CreateCommand()
        'comienza la Transacción y le asigna un nombre 
        'le asigna una conección al comando SQL
        'le asigna una transancción al comando SQL
        myTrans = cnn.BeginTransaction(IsolationLevel.ReadCommitted, "ModificarDescuento")
        cmd.Connection = cnn
        cmd.Transaction = myTrans
        Try
            sql = ""
            sql = "UPDATE DETPC SET DESCUENTO = " & descuento & ", TOTALDESCUENTO = " & totaldescuento & ", USUDESCUENTO =  " & Codigo_Usuario & " " & _
            "WHERE IP = '" & Codigo_Ip & "' AND CODEMPRESA = " & Codigo_Empresa & " AND CODSUCURSAL = " & Codigo_Sucursal & " AND CODCOMPROBANTE = " & Codigo_Comprobante & "" 'sintaxis sql
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()                             'ejecuta el comando
            myTrans.Commit()
            'si llega hasta acá es que se ha grabado correctamente
            '#######
            '# OK  #
            '#######
            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("ModificarDescuento")
            Catch ex As SqlException
                If Not myTrans.Connection Is Nothing Then
                    'cuando ocurre un  error de la transacción
                    Return False
                End If
            End Try
            Return False
            'cuando ocurre un error con el comit
        Finally

            ser.Cerrar(cnn)
        End Try
    End Function

    Public Function ActivoDetPc(ByVal Codigo_Ip As String, ByVal Codigo_Sucursal As Long, ByVal Codigo_Empresa As Long, ByVal Codigo_Comprobante As Long) As Boolean
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "select case " & _
        "when (SELECT count(*) as cantidad FROM DETPC where codempresa = " & Codigo_Empresa & " and ip = " & Codigo_Ip & " and codsucursal = " & Codigo_Sucursal & " and codcomprobante = " & Codigo_Comprobante & " and activo=1) <= 0  then 'False' " & _
        "when (SELECT count(*) as cantidad FROM DETPC where codempresa = " & Codigo_Empresa & " and ip = " & Codigo_Ip & " and codsucursal = " & Codigo_Sucursal & " and codcomprobante = " & Codigo_Comprobante & " and activo=1) > 0  then 'True' " & _
        "end as Existe "
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Try
                dr.Read()
                If dr("Existe") = "True" Then
                    Return True
                Else
                    Return False
                End If
            Catch
            Finally
                dr.Close()

                ser.Cerrar(cnn)
            End Try
        Finally
        End Try
    End Function

    Public Function ActivoDetPc_sincomprobante(ByVal Codigo_Ip As String, ByVal Codigo_Sucursal As Long, ByVal Codigo_Empresa As Long) As Boolean
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "select case " & _
        "when (SELECT count(*) as cantidad FROM DETPC where codempresa = " & Codigo_Empresa & " and ip = " & Codigo_Ip & " and codsucursal = " & Codigo_Sucursal & " and activo=1) <= 0  then 'False' " & _
        "when (SELECT count(*) as cantidad FROM DETPC where codempresa = " & Codigo_Empresa & " and ip = " & Codigo_Ip & " and codsucursal = " & Codigo_Sucursal & " and activo=1) > 0  then 'True' " & _
        "end as Existe "
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Try
                dr.Read()
                If dr("Existe") = "True" Then
                    Return True
                Else
                    Return False
                End If
            Catch
            Finally
                dr.Close()
                'cnn.Close()
                ser.Cerrar(cnn)
            End Try
        Finally
        End Try
    End Function

    Public Function ModificarActivoDetpc(ByVal Codigo_Empresa As Long, ByVal Codigo_Sucursal As Long, ByVal Codigo_Ip As String, ByVal Codigo_Comprobante As Long, ByVal Codigo_Usuario As Long, ByVal activo As Long) As Boolean
        cnn = New SqlConnection
        cnn = ser.Abrir()
        Dim cmd As SqlCommand = cnn.CreateCommand()
        'comienza la Transacción y le asigna un nombre 
        'le asigna una conección al comando SQL
        'le asigna una transancción al comando SQL
        myTrans = cnn.BeginTransaction(IsolationLevel.ReadCommitted, "ModificarActivoDetpc")
        cmd.Connection = cnn
        cmd.Transaction = myTrans
        Try
            Select Case activo
                Case 0
                    sql = ""
                    If Codigo_Comprobante > 0 Then
                        sql = "UPDATE DETPC SET ACTIVO = " & activo & ", " & _
                        "DESCUENTO = 0, TOTALDESCUENTO = 0, USUDESCUENTO = NULL " & _
                        "WHERE IP = '" & Codigo_Ip & "' AND CODEMPRESA = " & Codigo_Empresa & " AND CODSUCURSAL = " & Codigo_Sucursal & " AND CODCOMPROBANTE = " & Codigo_Comprobante & "" 'sintaxis sql
                    Else
                        sql = "UPDATE DETPC SET ACTIVO = " & activo & ", " & _
                        "DESCUENTO = 0, TOTALDESCUENTO = 0, USUDESCUENTO = NULL " & _
                        "WHERE IP = '" & Codigo_Ip & "' AND CODEMPRESA = " & Codigo_Empresa & " AND CODSUCURSAL = " & Codigo_Sucursal & " " 'sintaxis sql
                    End If
                Case 1
                    sql = ""
                    If Codigo_Comprobante > 0 Then
                        sql = "UPDATE DETPC SET ACTIVO = " & activo & " " & _
                        "WHERE IP = '" & Codigo_Ip & "' AND CODEMPRESA = " & Codigo_Empresa & " AND CODSUCURSAL = " & Codigo_Sucursal & " AND CODCOMPROBANTE = " & Codigo_Comprobante & "" 'sintaxis sql
                    Else
                        sql = "UPDATE DETPC SET ACTIVO = " & activo & " " & _
                        "WHERE IP = '" & Codigo_Ip & "' AND CODEMPRESA = " & Codigo_Empresa & " AND CODSUCURSAL = " & Codigo_Sucursal & " " 'sintaxis sql
                    End If

            End Select

            cmd.CommandText = sql
            cmd.ExecuteNonQuery()                             'ejecuta el comando
            myTrans.Commit()

            'si llega hasta acá es que se ha grabado correctamente
            '#######
            '# OK  #
            '#######
            Return True
        Catch e As Exception
            Try
                myTrans.Rollback("ModificarActivoDetpc")
            Catch ex As SqlException
                If Not myTrans.Connection Is Nothing Then
                    'cuando ocurre un  error de la transacción
                    Return False
                End If
            End Try
            Return False
            'cuando ocurre un error con el comit
        Finally

            ser.Cerrar(cnn)
        End Try
    End Function

    ' para saber si la maquina esta activa en facturacion
    Public Function ActivoPc(ByVal Codigo_Ip As String, ByVal Codigo_Sucursal As Long, ByVal Codigo_Empresa As Long) As Boolean
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "select case " & _
        "when (SELECT count(*) as cantidad FROM DETPC where codempresa = " & Codigo_Empresa & " and ip = " & Codigo_Ip & " and codsucursal = " & Codigo_Sucursal & " and activo=1) <= 0  then 'False' " & _
        "when (SELECT count(*) as cantidad FROM DETPC where codempresa = " & Codigo_Empresa & " and ip = " & Codigo_Ip & " and codsucursal = " & Codigo_Sucursal & " and activo=1) > 0  then 'True' " & _
        "end as Existe "
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()
            Try
                dr.Read()
                If dr("Existe") = "True" Then
                    Return True
                Else
                    Return False
                End If
            Catch
            Finally
                dr.Close()

                ser.Cerrar(cnn)
            End Try
        Finally
        End Try
    End Function


    Public Function MaximoDescuento(ByVal Codigo_comprobante As Long, ByVal Codigo_Ip As String, ByVal Codigo_empresa As Long, ByVal Codigo_Sucursal As Long) As Decimal
        Dim temp As Integer
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "SELECT " & _
        "isnull(D.TOTALDESCUENTO,0) AS MAXIMO " & _
        "FROM DETPC D WHERE D.codempresa = " & Codigo_empresa & " " & _
        "And D.CODSUCURSAL = " & Codigo_Sucursal & " " & _
        "AND D.IP = '" & Codigo_Ip & "' AND D.CODCOMPROBANTE = " & Codigo_comprobante & ""
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()

            dr.Read()
            Return dr("maximo")
        Finally
            dr.Close()

            ser.Cerrar(cnn)
        End Try

    End Function

    Public Function MaximaComision(ByVal Codigo_comprobante As Long, ByVal Codigo_Ip As String, ByVal Codigo_empresa As Long, ByVal Codigo_Sucursal As Long) As Decimal
        Dim temp As Integer
        ser = New BD.ABD
        cnn = ser.Abrir
        sql = ""
        sql = "SELECT " & _
        "isnull(D.TOTALCOMISION,0) AS MAXIMO " & _
        "FROM DETPC D WHERE D.codempresa = " & Codigo_empresa & " " & _
        "And D.CODSUCURSAL = " & Codigo_Sucursal & " " & _
        "AND D.IP = '" & Codigo_Ip & "' AND D.CODCOMPROBANTE = " & Codigo_comprobante & ""
        Try
            cmd = New SqlCommand(sql, cnn)
            dr = cmd.ExecuteReader()

            dr.Read()
            Return dr("maximo")
        Finally
            dr.Close()

            ser.Cerrar(cnn)
        End Try

    End Function

End Class
