Imports System.Data.SqlClient
Imports System.IO
Imports System.Security.Cryptography
Imports System.Xml

Module sierra_maggs


    ''' URL Servicio referencia
    '''
    ''' https://www.dagal.com.mx/servicios/contadosDagal/ContadosService.asmx?WSDL
    '''
    '''
    ''' com.dagal.ws
    '''


    Public MaggsUsername As String = "S13rr4Web"
    Public MaggsPassword As String = "C0nex10nDag1l"
    Public MaggsUsuario As String = "122"

    Public mRootPath As String = "C:\SierraDocs\WebApi_Maggs"
    Public mCfgFile As String = "EnvioMaggs.cfg"
    Public mLogFile As String = "EnvioMaggs.log"

    Dim BdMsgError As String
    Dim bolBDCheck As Boolean = False

    Dim opeConnStr As String = modstrcnnBDOpe

    Public Function SierraMaggsValidarProveedor(ByVal rfc As String) As ConsultarProveedorResponse

        Dim valResp As New ConsultarProveedorResponse

        Dim msg As String
        Dim res As String
        Dim sta As Boolean

        Dim status As Integer

        ReadCfgFile()

        opeConnStr = modstrcnnBDOpe

        If RevisarBdOpe() Then

            Dim r = ValidarProveedor(rfc)

            sta = r.Estado
            msg = r.Mensaje
            res = r.Respuesta

            status = 1

        Else

            sta = False
            msg = BdMsgError
            res = "Error al preparar la base de datos"

            status = 0

        End If

        GuardarValidarProveedorCdx(status, rfc, sta, msg, res, mstrIdSuc)

        WriteLog("SierraMaggsValidarProveedor -- rfc: " & rfc & ", estado: " & sta & ", mensaje: " & msg & ", respuesta: " & res & "")

        valResp.Estado = sta
        valResp.Mensaje = msg
        valResp.Respuesta = res

        Dim vecCom As String = GetVecCom()
        If vecCom.Substring(107, 1) = "1" Then				' Guardar en servidor corporativo tambien

            modstrBDOpe = "BDOpe_" + mstrLicSvrCorpCxp + "_" + modAno
            opeConnStr = String.Concat(New String() {"Data Source=", mstrUrlSvrCorpCxp, ";User ID=sa;Password=", modPswSql, ";Initial Catalog=", modstrBDOpe, ";POOLING=FALSE;Connection Timeout=10"})

            If RevisarBdOpe() Then
                GuardarValidarProveedorCdx(status, rfc, sta, msg, res, mstrIdSuc)
            Else
                sta = False
                msg = BdMsgError
                res = "Error al preparar la base de datos corporativo"
                status = 0
            End If

            WriteLog("SierraMaggsValidarProveedor corporativo -- rfc: " & rfc & ", estado: " & sta & ", mensaje: " & msg & ", respuesta: " & res & "")

        End If

        Return valResp

    End Function

    Public Function SierraMaggsEnvioOrdenCompra(ByVal compra As String, ByVal fechaCompra As DateTime, ByVal almacen As String, ByVal rfcproveedor As String, ByVal tipoMoneda As Integer, ByVal subtotal As Decimal, ByVal descuento As Decimal, ByVal ieps As Decimal, ByVal impuesto As Decimal, ByVal total As Decimal) As EnvioOrdenCompraResponse

        Dim envioResponse As New EnvioOrdenCompraResponse

        Dim msg As String
        Dim res As String
        Dim sta As Boolean
        Dim idordencompra As String
        Dim comentario As String = "C/" & compra & "  " & fechaCompra.ToString("dd/MM/yyyy")

        Dim status As Integer

        ReadCfgFile()

        opeConnStr = modstrcnnBDOpe

        If RevisarBdOpe() Then

            Dim r = EnviarOrdenCompra(almacen, rfcproveedor, tipoMoneda, subtotal, descuento, ieps, impuesto, total, comentario)

            sta = r.Estado
            msg = r.Mensaje
            res = r.Respuesta
            idordencompra = r.IdOrdenCompra

            status = 1

        Else

            sta = False
            msg = BdMsgError
            res = "Error al preparar la base de datos"
            idordencompra = ""

            status = 0

        End If

        GuardarEnvioOrdenCompraCdx(compra, fechaCompra, status, almacen, rfcproveedor, tipoMoneda, subtotal, descuento, ieps, impuesto, total, comentario, sta, idordencompra, msg, res, mstrIdSuc)

        WriteLog("SierraMaggsEnvioOrdenCompra -- compra: " & compra & ", fechaCompra: " & fechaCompra.ToString("dd/MM/yyyy") & ", status: " & status & ", sucursal: " & almacen & ", rfcproveedor: " & rfcproveedor & ", tipoMoneda: " & tipoMoneda & ", subtotal: " & subtotal & ", descuento: " & descuento & ", ieps: " & ieps & ", impuesto: " & impuesto & ", total: " & total & ", comentario: " & comentario & ", estado: " & sta & ", idordencompra: " & idordencompra & ", mensaje: " & msg & ", respuesta: " & res & ", suc: " & mstrIdSuc & "")

        envioResponse.IdOrdenCompra = idordencompra
        envioResponse.Estado = sta
        envioResponse.Mensaje = msg
        envioResponse.Respuesta = res

        '=============== 2026/16/02
        If String.Equals(envioResponse.Respuesta, "RFC del Proveedor no Encontrado", StringComparison.OrdinalIgnoreCase) Then
            envioResponse.Respuesta = envioResponse.Respuesta & " en Servicio DAGAL."
        End If
        '====================================

        Dim vecCom As String = GetVecCom()
        If vecCom.Substring(107, 1) = "1" Then              ' Guardar en servidor corporativo tambien

            modstrBDOpe = "BDOpe_" + mstrLicSvrCorpCxp + "_" + modAno
            opeConnStr = String.Concat(New String() {"Data Source=", mstrUrlSvrCorpCxp, ";User ID=sa;Password=", modPswSql, ";Initial Catalog=", modstrBDOpe, ";POOLING=FALSE;Connection Timeout=10"})

            If RevisarBdOpe() Then

                GuardarEnvioOrdenCompraCdx(compra, fechaCompra, status, almacen, rfcproveedor, tipoMoneda, subtotal, descuento, ieps, impuesto, total, comentario, sta, idordencompra, msg, res, mstrIdSuc)

            Else

                sta = False
                msg = BdMsgError
                res = "Error al preparar la base de datos corporativo"
                idordencompra = ""
                status = 0

            End If

            WriteLog("SierraMaggsEnvioOrdenCompra Corporativo -- compra: " & compra & ", fechaCompra: " & fechaCompra.ToString("dd/MM/yyyy") & ", status: " & status & ", sucursal: " & almacen & ", rfcproveedor: " & rfcproveedor & ", tipoMoneda: " & tipoMoneda & ", subtotal: " & subtotal & ", descuento: " & descuento & ", ieps: " & ieps & ", impuesto: " & impuesto & ", total: " & total & ", comentario: " & comentario & ", estado: " & sta & ", idordencompra: " & idordencompra & ", mensaje: " & msg & ", respuesta: " & res & ", suc: " & mstrIdSuc & "")

        End If

        Return envioResponse

    End Function


    Function ValidarProveedor(ByVal rfc As String) As ConsultarProveedorResponse

        Dim respConsulta As New ConsultarProveedorResponse

        Try

            Dim auth = New com.dagal.ws.AuthenticationHeader With {.UserName = MaggsUsername, .Password = MaggsPassword}
            Dim client = New com.dagal.ws.ContadosServiceSoapClient()
            Dim res As String = String.Empty, msg As String = String.Empty

            Dim response = client.consultaProveedor(auth, rfc.Trim(), res, msg)

            respConsulta.Estado = response.ToString()
            respConsulta.Mensaje = msg
            respConsulta.Respuesta = res

        Catch ex As Exception

            respConsulta.Estado = False
            respConsulta.Mensaje = ex.Message
            respConsulta.Respuesta = "Error"

            WriteLog("ValidarProveedor Error: " & ex.Message)

        End Try

        Return respConsulta
    End Function


    Function EnviarOrdenCompra(ByVal sucursal As String, ByVal rfcproveedor As String, ByVal tipoMoneda As Integer, ByVal subtotal As Decimal, ByVal descuento As Decimal, ByVal ieps As Decimal, ByVal impuesto As Decimal, ByVal total As Decimal, ByVal comentario As String) As EnvioOrdenCompraResponse

        Dim envioOrdenCompra As New EnvioOrdenCompraResponse With {.Estado = False, .IdOrdenCompra = "", .Mensaje = "", .Respuesta = ""}

        Try

            Dim auth = New com.dagal.ws.AuthenticationHeader With {.UserName = MaggsUsername, .Password = MaggsPassword}
            Dim client = New com.dagal.ws.ContadosServiceSoapClient()
            Dim respuesta As String = String.Empty, mensaje As String = String.Empty, idordencompra As String = String.Empty

            Dim response = client.nuevaOrdenCompra(auth, sucursal, rfcproveedor, MaggsUsuario, tipoMoneda, subtotal, descuento, ieps, impuesto, total, comentario, idordencompra, respuesta, mensaje)

            envioOrdenCompra.Estado = response.ToString()
            envioOrdenCompra.IdOrdenCompra = idordencompra
            envioOrdenCompra.Mensaje = mensaje
            envioOrdenCompra.Respuesta = respuesta

        Catch ex As Exception

            envioOrdenCompra.Estado = False
            envioOrdenCompra.Mensaje = ex.Message
            envioOrdenCompra.Respuesta = "Error de comunicacion al solicitar ID orden de compra a Dagal"
            envioOrdenCompra.IdOrdenCompra = ""

            WriteLog("EnviarOrdenCompra Error: " & ex.Message)

        End Try

        Return envioOrdenCompra

    End Function


#Region "SQL"

    Function GuardarValidarProveedorCdx(ByVal status As Integer, ByVal rfc As String, ByVal estado As Boolean, ByVal mensaje As String, ByVal respuesta As String, ByVal suc As String) As Boolean

        Dim bolR As Boolean = False

        If String.IsNullOrEmpty(suc) Then suc = String.Empty

        Try

            Const strSQLQuery As String = "" & _
                                          "INSERT INTO OpeMaggsValidaPrv ( " & _
                                          "OpeMaggsValidaPrv_Fec, OpeMaggsValidaPrv_Status, OpeMaggsValidaPrv_Request_Rfc, OpeMaggsValidaPrv_Response_Status," & _
                                          "OpeMaggsValidaPrv_Response_Respuesta, OpeMaggsValidaPrv_Response_Mensaje, OpeMaggsValidaPrv_Suc ) " & _
                                          "VALUES ( " & _
                                          "@OpeMaggsValidaPrv_Fec, @OpeMaggsValidaPrv_Status, @OpeMaggsValidaPrv_Request_Rfc, @OpeMaggsValidaPrv_Response_Status," & _
                                          "@OpeMaggsValidaPrv_Response_Respuesta, @OpeMaggsValidaPrv_Response_Mensaje, @OpeMaggsValidaPrv_Suc ); "

            Dim sta As Integer

            If estado Then sta = 1 Else sta = 0

            Using connection As New SqlConnection(opeConnStr)

                Using comm As New SqlCommand()

                    With comm

                        .Connection = connection
                        .CommandType = CommandType.Text
                        .CommandText = strSQLQuery

                        .Parameters.Add("@OpeMaggsValidaPrv_Fec", SqlDbType.DateTime).Value = DateTime.Now
                        .Parameters.Add("@OpeMaggsValidaPrv_Status", SqlDbType.Int).Value = status
                        .Parameters.Add("@OpeMaggsValidaPrv_Request_Rfc", SqlDbType.VarChar).Value = rfc
                        .Parameters.Add("@OpeMaggsValidaPrv_Response_Status", SqlDbType.Int).Value = sta
                        .Parameters.Add("@OpeMaggsValidaPrv_Response_Respuesta", SqlDbType.VarChar).Value = mensaje
                        .Parameters.Add("@OpeMaggsValidaPrv_Response_Mensaje", SqlDbType.VarChar).Value = respuesta
                        .Parameters.Add("@OpeMaggsValidaPrv_Suc", SqlDbType.VarChar).Value = suc

                    End With

                    connection.Open()

                    comm.ExecuteNonQuery()

                    comm.Parameters.Clear()

                    bolR = True

                End Using

            End Using

        Catch ex As Exception

            WriteLog("GuardarValidarProveedorCdx Error: " & ex.Message)

            bolR = False

        End Try

        Return bolR

    End Function


    Function GuardarEnvioOrdenCompraCdx(ByVal compra As String, ByVal fecCompra As DateTime, ByVal status As Integer, ByVal sucursal As String, ByVal rfcproveedor As String, ByVal tipomoneda As Integer, ByVal subtotal As Decimal, ByVal descuento As Decimal, ByVal ieps As Decimal, ByVal impuesto As Decimal, ByVal total As Decimal, ByVal comentario As String, ByVal estado As Boolean, ByVal idordencompra As String, ByVal mensaje As String, ByVal respuesta As String, ByVal suc As String) As Boolean

        Dim bolR As Boolean = False

        Try

            If String.IsNullOrEmpty(suc) Then suc = String.Empty

            'sucursal, rfcproveedor, tipoMoneda, subtotal, descuento, ieps, impuesto,
            'total, comentario, idordencompra, respuesta, mensaje

            Const strSQLQuery As String = "" & _
                                          "INSERT INTO OpeMaggsCxpEnvio (" & _
                                          "OpeMaggsCxpEnvio_Fec,OpeMaggsCxpEnvio_Status,OpeMaggsCxpEnvio_OrdenSierra,OpeMaggsCxpEnvio_OrdenMaggs,OpeMaggsCxpEnvio_Compra,OpeMaggsCxpEnvio_Fec_Compra,OpeMaggsCxpEnvio_Request_Sucursal," & _
                                          "OpeMaggsCxpEnvio_Request_RfcProveedor,OpeMaggsCxpEnvio_Request_Usuario,OpeMaggsCxpEnvio_Request_TipoMoneda,OpeMaggsCxpEnvio_Request_Subtotal," & _
                                          "OpeMaggsCxpEnvio_Request_Descuento,OpeMaggsCxpEnvio_Request_Ieps,OpeMaggsCxpEnvio_Request_Impuesto,OpeMaggsCxpEnvio_Request_Total," & _
                                          "OpeMaggsCxpEnvio_Request_Comentario,OpeMaggsCxpEnvio_Response_IdOrdenCompra,OpeMaggsCxpEnvio_Response_Respuesta,OpeMaggsCxpEnvio_Response_Mensaje,OpeMaggsCxpEnvio_Suc )" & _
                                          "VALUES (" & _
                                          "@OpeMaggsCxpEnvio_Fec,@OpeMaggsCxpEnvio_Status,@OpeMaggsCxpEnvio_OrdenSierra,@OpeMaggsCxpEnvio_OrdenMaggs,@OpeMaggsCxpEnvio_Compra,@OpeMaggsCxpEnvio_Fec_Compra,@OpeMaggsCxpEnvio_Request_Sucursal," & _
                                          "@OpeMaggsCxpEnvio_Request_RfcProveedor,@OpeMaggsCxpEnvio_Request_Usuario,@OpeMaggsCxpEnvio_Request_TipoMoneda,@OpeMaggsCxpEnvio_Request_Subtotal," & _
                                          "@OpeMaggsCxpEnvio_Request_Descuento,@OpeMaggsCxpEnvio_Request_Ieps,@OpeMaggsCxpEnvio_Request_Impuesto,@OpeMaggsCxpEnvio_Request_Total," & _
                                          "@OpeMaggsCxpEnvio_Request_Comentario,@OpeMaggsCxpEnvio_Response_IdOrdenCompra,@OpeMaggsCxpEnvio_Response_Respuesta,@OpeMaggsCxpEnvio_Response_Mensaje,@OpeMaggsCxpEnvio_Suc );"
            Dim sta As Integer

            If estado Then sta = 1 Else sta = 0

            Using connection As New SqlConnection(opeConnStr)

                Using comm As New SqlCommand()

                    With comm

                        .Connection = connection
                        .CommandType = CommandType.Text
                        .CommandText = strSQLQuery

                        .Parameters.Add("@OpeMaggsCxpEnvio_Fec", SqlDbType.DateTime).Value = DateTime.Now
                        .Parameters.Add("@OpeMaggsCxpEnvio_Status", SqlDbType.Int).Value = status
                        .Parameters.Add("@OpeMaggsCxpEnvio_OrdenSierra", SqlDbType.VarChar).Value = compra
                        .Parameters.Add("@OpeMaggsCxpEnvio_OrdenMaggs", SqlDbType.VarChar).Value = idordencompra
                        .Parameters.Add("@OpeMaggsCxpEnvio_Compra", SqlDbType.VarChar).Value = compra
                        .Parameters.Add("@OpeMaggsCxpEnvio_Fec_Compra", SqlDbType.DateTime).Value = fecCompra
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_Sucursal", SqlDbType.VarChar).Value = sucursal
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_RfcProveedor", SqlDbType.VarChar).Value = rfcproveedor
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_Usuario", SqlDbType.VarChar).Value = MaggsUsuario
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_TipoMoneda", SqlDbType.Int).Value = tipomoneda
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_Subtotal", SqlDbType.Decimal).Value = subtotal
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_Descuento", SqlDbType.Decimal).Value = descuento
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_Ieps", SqlDbType.Decimal).Value = ieps
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_Impuesto", SqlDbType.Decimal).Value = impuesto
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_Total", SqlDbType.Decimal).Value = total
                        .Parameters.Add("@OpeMaggsCxpEnvio_Request_Comentario", SqlDbType.VarChar).Value = comentario
                        .Parameters.Add("@OpeMaggsCxpEnvio_Response_IdOrdenCompra", SqlDbType.VarChar).Value = idordencompra
                        .Parameters.Add("@OpeMaggsCxpEnvio_Response_Respuesta", SqlDbType.VarChar).Value = respuesta
                        .Parameters.Add("@OpeMaggsCxpEnvio_Response_Mensaje", SqlDbType.VarChar).Value = mensaje
                        .Parameters.Add("@OpeMaggsCxpEnvio_Suc", SqlDbType.VarChar).Value = suc

                    End With

                    connection.Open()

                    comm.ExecuteNonQuery()

                    comm.Parameters.Clear()

                    bolR = True

                End Using

            End Using

        Catch ex As Exception

            WriteLog("GuardarEnvioOrdenCompraCdx Error: " & ex.Message)

            bolR = False

        End Try

        Return bolR

    End Function

#End Region

    Function RevisarBdOpe() As Boolean

        If bolBDCheck Then Return True

        Dim bolR As Boolean
        Dim strSQLQuery As String

        Try

            Dim con As New SqlConnection
            Dim cmd As New SqlCommand

            con = New SqlConnection With {.ConnectionString = opeConnStr}
            con.Open()
            cmd.Connection = con

            Dim dtB As DataTable = con.GetSchema("Columns")

            If dtB.Select("TABLE_NAME = 'OpeMaggsCxpEnvio'").Length = 0 Then

                strSQLQuery = "" & _
                              "CREATE TABLE [dbo].[OpeMaggsCxpEnvio](" & _
                              "	 [OpeMaggsCxpEnvio_id] [int] IDENTITY(1,1) NOT NULL," & _
                              "	 [OpeMaggsCxpEnvio_Fec] [datetime] NOT NULL," & _
                              "	 [OpeMaggsCxpEnvio_Status] [int] NOT NULL," & _
                              "	 [OpeMaggsCxpEnvio_OrdenSierra] [varchar](30) NOT NULL," & _
                              "	 [OpeMaggsCxpEnvio_OrdenMaggs] [varchar](30) NOT NULL," & _
                              "  [OpeMaggsCxpEnvio_Compra] [varchar](30) NULL," & _
                              "  [OpeMaggsCxpEnvio_Fec_Compra] [datetime] NOT NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_Sucursal] [varchar](5) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_RfcProveedor] [varchar](15) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_Usuario] [varchar](10) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_TipoMoneda] [int] NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_Subtotal] [decimal](18, 2) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_Descuento] [decimal](18, 2) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_Ieps] [decimal](18, 2) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_Impuesto] [decimal](18, 2) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_Total] [decimal](18, 2) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Request_Comentario] [varchar](255) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Response_IdOrdenCompra] [varchar](30) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Response_Respuesta] [varchar](255) NULL," & _
                              "	 [OpeMaggsCxpEnvio_Response_Mensaje] [varchar](255) NULL," & _
                              "  CONSTRAINT [PK_OpeMaggsCxpEnvio] PRIMARY KEY CLUSTERED " & _
                              "( [OpeMaggsCxpEnvio_id] ASC ) ) ON [PRIMARY];" & _
                              "ALTER TABLE [dbo].[OpeMaggsCxpEnvio] ADD DEFAULT (getdate()) FOR [OpeMaggsCxpEnvio_Fec];" & _
                              "ALTER TABLE [dbo].[OpeMaggsCxpEnvio] ADD DEFAULT (getdate()) FOR [OpeMaggsCxpEnvio_Fec_Compra];" & _
                              "ALTER TABLE [dbo].[OpeMaggsCxpEnvio] ADD DEFAULT ((0)) FOR [OpeMaggsCxpEnvio_Status];" & _
                              "CREATE NONCLUSTERED INDEX [IX_OpeMaggsCxpEnvio_OrdenMaggs] On [dbo].[OpeMaggsCxpEnvio] ( [OpeMaggsCxpEnvio_OrdenMaggs] ASC );" & _
                              "CREATE NONCLUSTERED INDEX [IX_OpeMaggsCxpEnvio_OrdenSierra] On [dbo].[OpeMaggsCxpEnvio] ( [OpeMaggsCxpEnvio_OrdenSierra] ASC );"

                cmd.CommandText = strSQLQuery
                cmd.ExecuteNonQuery()

            End If

            dtB = con.GetSchema("Columns")

            If dtB.Select("TABLE_NAME = 'OpeMaggsCxpRecep'").Length = 0 Then

                strSQLQuery = "" & _
                              "CREATE TABLE [dbo].[OpeMaggsCxpRecep](" & _
                              "	 [OpeMaggsCxpRecep_id] [int] IDENTITY(1,1) NOT NULL," & _
                              "	 [OpeMaggsCxpRecep_Fec] [datetime] NOT NULL," & _
                              "	 [OpeMaggsCxpRecep_Status] [int] NOT NULL," & _
                              "	 [OpeMaggsCxpRecep_OrdenSierra] [varchar](30) NULL," & _
                              "	 [OpeMaggsCxpRecep_OrdenMaggs] [varchar](30) NULL," & _
                              "	 [OpeMaggsCxpRecep_Request_IdOrdenCompra] [varchar](30) NULL," & _
                              "	 [OpeMaggsCxpRecep_Request_Rfc] [varchar](15) NULL," & _
                              "	 [OpeMaggsCxpRecep_Request_Total] [decimal](18, 2) NULL," & _
                              "	 [OpeMaggsCxpRecep_Request_TipoMoneda] [int] NULL," & _
                              "	 [OpeMaggsCxpRecep_Request_TipoCambio] [decimal](18, 2) NULL," & _
                              "	 [OpeMaggsCxpRecep_Response_Status] [int] NOT NULL," & _
                              "	 [OpeMaggsCxpRecep_Response_Respuesta] [varchar](255) NULL," & _
                              "	 [OpeMaggsCxpRecep_Response_Mensaje] [varchar](255) NULL," & _
                              "	 [OpeMaggsCxpRecep_Response_Guid] [varchar](255) NULL," & _
                              "  CONSTRAINT [PK_OpeMaggsCxpRecep] PRIMARY KEY CLUSTERED " & _
                              "  ( [OpeMaggsCxpRecep_id] ASC ) ) ON [PRIMARY];" & _
                              "ALTER TABLE [dbo].[OpeMaggsCxpRecep] ADD DEFAULT (getdate()) FOR [OpeMaggsCxpRecep_Fec];" & _
                              "ALTER TABLE [dbo].[OpeMaggsCxpRecep] ADD DEFAULT ((0)) FOR [OpeMaggsCxpRecep_Status];" & _
                              "ALTER TABLE [dbo].[OpeMaggsCxpRecep] ADD DEFAULT ((0)) FOR [OpeMaggsCxpRecep_Response_Status];" & _
                              "CREATE NONCLUSTERED INDEX [IX_OpeMaggsCxpRecep_OrdenMaggs] ON [dbo].[OpeMaggsCxpRecep] ( [OpeMaggsCxpRecep_OrdenMaggs] ASC );"

                cmd.CommandText = strSQLQuery
                cmd.ExecuteNonQuery()

            End If

            dtB = con.GetSchema("Columns")

            If dtB.Select("TABLE_NAME = 'OpeMaggsValidaPrv'").Length = 0 Then

                strSQLQuery = "" & _
                              "CREATE TABLE [dbo].[OpeMaggsValidaPrv](" & _
                              "	 [OpeMaggsValidaPrv_id] [int] IDENTITY(1,1) NOT NULL," & _
                              "	 [OpeMaggsValidaPrv_Fec] [datetime] NOT NULL," & _
                              "	 [OpeMaggsValidaPrv_Status] [int] NOT NULL," & _
                              "	 [OpeMaggsValidaPrv_Request_Rfc] [varchar](15) NULL," & _
                              "	 [OpeMaggsValidaPrv_Response_Status] [int] NOT NULL," & _
                              "	 [OpeMaggsValidaPrv_Response_Respuesta] [varchar](255) NULL," & _
                              "	 [OpeMaggsValidaPrv_Response_Mensaje] [varchar](255) NULL," & _
                              "  CONSTRAINT [PK_OpeMaggsValidaPrv] PRIMARY KEY CLUSTERED" & _
                              "  ( [OpeMaggsValidaPrv_id] ASC ) ) ON [PRIMARY];" & _
                              "ALTER TABLE [dbo].[OpeMaggsValidaPrv] ADD DEFAULT (getdate()) FOR [OpeMaggsValidaPrv_Fec];" & _
                              "ALTER TABLE [dbo].[OpeMaggsValidaPrv] ADD DEFAULT ((0)) FOR [OpeMaggsValidaPrv_Status];" & _
                              "ALTER TABLE [dbo].[OpeMaggsValidaPrv] ADD DEFAULT ((0)) FOR [OpeMaggsValidaPrv_Response_Status];" & _
                              "CREATE NONCLUSTERED INDEX [IX_OpeMaggsValidaPrv] ON [dbo].[OpeMaggsValidaPrv] ( [OpeMaggsValidaPrv_Request_Rfc] ASC );"

                cmd.CommandText = strSQLQuery
                cmd.ExecuteNonQuery()

            End If

            If dtB.Select("COLUMN_NAME = 'OpeMaggsValidaPrv_Suc' AND TABLE_NAME = 'OpeMaggsValidaPrv'").Length = 0 Then
                strSQLQuery = "ALTER TABLE [OpeMaggsValidaPrv] ADD [OpeMaggsValidaPrv_Suc] varchar (3);"
                cmd.CommandText = strSQLQuery
                cmd.ExecuteNonQuery()
            End If

            If dtB.Select("COLUMN_NAME = 'OpeMaggsCxpRecep_Suc' AND TABLE_NAME = 'OpeMaggsCxpRecep'").Length = 0 Then
                strSQLQuery = "ALTER TABLE [OpeMaggsCxpRecep] ADD [OpeMaggsCxpRecep_Suc] varchar (3);"
                cmd.CommandText = strSQLQuery
                cmd.ExecuteNonQuery()
            End If

            If dtB.Select("COLUMN_NAME = 'OpeMaggsCxpEnvio_Suc' AND TABLE_NAME = 'OpeMaggsCxpEnvio'").Length = 0 Then
                strSQLQuery = "ALTER TABLE [OpeMaggsCxpEnvio] ADD [OpeMaggsCxpEnvio_Suc] varchar (3);"
                cmd.CommandText = strSQLQuery
                cmd.ExecuteNonQuery()
            End If

            con.Close()

            con.Dispose()

            bolR = True

            bolBDCheck = True

        Catch ex As Exception
            WriteLog("RevisarBdOpe Error: " & ex.Message)

            bolR = False
            BdMsgError = ex.Message
        End Try

        Return bolR

    End Function

    Function GetVecCom() As String

        Dim vecCom As String = StrDup(250, "0")

        Try

            Using connection As New SqlConnection(modstrcnnBDOpe)
                Using command As New SqlCommand("SELECT OpePrm_VecCom FROM OpePrm", connection)
                    command.CommandType = CommandType.Text
                    connection.Open()
                    Using reader As SqlDataReader = command.ExecuteReader()
                        If reader.HasRows Then
                            While reader.Read()
                                vecCom = reader.Item(0)
                            End While
                        End If
                    End Using
                    connection.Close()
                End Using
            End Using

        Catch ex As Exception
            WriteLog("GetVecCom Error: " & ex.Message)
        End Try

        Return vecCom

    End Function

#Region "Funciones"

    Function ReadCfgFile() As Boolean
        Dim bolR As Boolean = False

        Try

            IO.Directory.CreateDirectory(mRootPath & "\Data\")

            If IO.File.Exists(mRootPath & "\Data\" & mCfgFile) Then

                Dim fs As New FileStream(mRootPath & "\Data\" & mCfgFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)

                Using sr = New StreamReader(fs)
                    MaggsUsername = sr.ReadLine()
                    MaggsPassword = sr.ReadLine()
                    MaggsUsuario = sr.ReadLine()
                End Using

            Else

                Using sw As New StreamWriter(mRootPath & "\Data\" & mCfgFile, False)
                    sw.WriteLine(MaggsUsername)
                    sw.WriteLine(MaggsPassword)
                    sw.WriteLine(MaggsUsuario)
                End Using

                ReadCfgFile()

            End If

            bolR = True

        Catch ex As Exception
            bolR = False
            WriteLog("Error: " & ex.Message)
        End Try

        Return bolR
    End Function

    Sub WriteLog(ByVal str As String)

        Try
            IO.Directory.CreateDirectory(mRootPath & "\Logs\")
            Dim dtn As String = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss:ffff")
            Dim objWriter As System.IO.StreamWriter
            objWriter = New System.IO.StreamWriter(mRootPath & "\Logs\" & mLogFile, True)
            objWriter.WriteLine("<<- " & dtn & " ->> " & str)
            objWriter.Close()
        Catch ex As Exception

        End Try
    End Sub

    Friend Function Trim_Datatable(ByVal dt As DataTable) As DataTable
        For Each Row As DataRow In dt.Rows
            For Each Col As DataColumn In dt.Columns
                If Col.DataType IsNot System.Type.GetType("System.String") Then
                    Continue For
                Else
                    If IsDBNull(Row.Item(Col.ColumnName)) Then
                        Row.Item(Col.ColumnName) = ""
                    Else
                        Row.Item(Col.ColumnName) = Row.Item(Col.ColumnName).trim
                    End If
                End If
            Next
        Next
        Return dt
    End Function

#End Region

End Module


Public Class ConsultarProveedorResponse
    Private _estado As Boolean
    Public Property Estado() As Boolean
        Get
            Return _estado
        End Get
        Set(ByVal value As Boolean)
            _estado = value
        End Set
    End Property

    Private _respuesta As String
    Public Property Respuesta() As String
        Get
            Return _respuesta
        End Get
        Set(ByVal value As String)
            _respuesta = value
        End Set
    End Property

    Private _mensaje As String
    Public Property Mensaje() As String
        Get
            Return _mensaje
        End Get
        Set(ByVal value As String)
            _mensaje = value
        End Set
    End Property

End Class

Public Class EnvioOrdenCompraResponse
    Private _estado As Boolean
    Public Property Estado() As Boolean
        Get
            Return _estado
        End Get
        Set(ByVal value As Boolean)
            _estado = value
        End Set
    End Property

    Private _idordencompra As String
    Public Property IdOrdenCompra() As String
        Get
            Return _idordencompra
        End Get
        Set(ByVal value As String)
            _idordencompra = value
        End Set

    End Property

    Private _respuesta As String
    Public Property Respuesta() As String
        Get
            Return _respuesta
        End Get
        Set(ByVal value As String)
            _respuesta = value
        End Set
    End Property

    Private _mensaje As String
    Public Property Mensaje() As String
        Get
            Return _mensaje
        End Get
        Set(ByVal value As String)
            _mensaje = value
        End Set
    End Property

End Class