The Easiest Way to Save and Share Code Snippets on the web

Untitled

vbnet

posted: Jan, 18th 2012 | jump to bottom

  1. Código Exportación Electricidad
  2.  
  3. Dim CadenaConexion As String
  4. Dim col As Integer
  5. Dim BNCR As String, Server1 As String
  6. Dim Server As String
  7. Dim Rs As New ADODB.Recordset, Rs2 As New ADODB.Recordset
  8. Dim Rs1 As New ADODB.Recordset
  9. Dim Sql As String
  10. Dim Sql1 As String
  11. Dim Descripcion As String, Factura As String, Monto As String
  12. Dim Factura1 As String, Monto1 As String, fecha As String, Linea As String
  13. Dim CnConectividad As New ADODB.Connection
  14. Dim Identificador As String, Nombre As String, Electricidad As String
  15. Dim LFecha As New clsFecha, x As Long
  16. LFecha.fecha = TraeFechaServer
  17.  
  18.         Rs.Open "select sum(d.monto)as monto,a.fchlectura,b.nombre,c.fchvence,b.codasociado,b.direccion," _
  19.         & " a.codmedsoc,a.idinterno,a.idfact" _
  20.         & " from fac_detfact as a, fac_asociados as b, fac_encfact as c,fac_detcob as d" _
  21.         & " where c.id=a.idfact and c.estado=0 and a.codsocio=b.codasociado And A.IdInterno = D.IdInterno" _
  22.         & " group by a.fchlectura,b.nombre,c.fchvence,b.codasociado,b.direccion,a.codmedsoc,a.idinterno,a.idfact", Cn
  23.  
  24. 'en lugar del numero de cedula se va a enviar el codigomedidor y contrato
  25. 'tipo de identificacion es 002
  26. 'la descripcion mejor separarla por dos punto e incluir numero valorservicio
  27. 'detallar si va a afectar al sincronizador
  28. 'incluir todos los rubros
  29.  
  30.     Server = Servidor1
  31.     BNCR = BaseDatos1
  32.  
  33.     CadenaConexion = "Provider=SQLOLEDB.1;Password=coop34LF4roRu1z;Persist Security " _
  34.         & "Info=False;User ID=sa;Initial Catalog=" & BNCR _
  35.         & ";Data Source=" & Server
  36.  
  37.     CnConectividad.Open CadenaConexion
  38.  
  39.     Barra.Value = 0
  40.     Barra.max = 7000
  41.  
  42.    If Not ConsultaVacia(Rs) Then
  43.         While Not FinDeTabla(Rs)
  44.  
  45.             Descripcion = Rs!CodAsociado & "," & Rs!Direccion
  46.             x = Len(Descripcion)
  47.             If x <= 60 Then
  48.                 Descripcion = Descripcion & String(60 - Len(Descripcion), " ")
  49.             Else
  50.                 x = Len(Rs!CodAsociado)
  51.                 x = 60 - x
  52.                 Descripcion = Rs!CodAsociado & Mid(Rs!Direccion, 1, x)
  53.             End If
  54.             Identificador = Rs!CodMedSoc & String(30 - Len(Rs!CodMedSoc), " ")
  55.             Nombre = Rs!Nombre & String(50 - Len(Rs!Nombre), " ")
  56.             Monto = Format(Rs!Monto, "000000000000000000")
  57.             Factura = Format(Rs!IdInterno, "00000000000000000000")
  58.             fecha = LFecha.fecha '8 & LFecha.Hora6
  59.  
  60.             Barra.Value = Barra.Value + 1
  61.  
  62.             Sql = "Insert into PARRecibosServicios(codigoconvenio,valorservicio,periodorecibo,numerofacturarecibo,descripcionservicio," _
  63.                     & "tipoidentificacion,identificacioncliente,nombrecliente,montototalrecibo,fechavencimientorecibo,estado,
  64.      fecharegistro,estadosincronizacion)values(" _ 
  65.       & "'001'" & "," & "'Electricidad   '" & "," & Rs!FchLectura & "," & PonerComillas(Factura) & "," & PonerComillas(Electricidad & Descripcion) & "," & "'002'" & "," & PonerComillas(Identificador) & "," & PonerComillas(Nombre) & ","   & PonerComillas(Monto) & "," & Rs!FchVence & "," & 1 & "," & PonerComillas(fecha) & "," & 0 & ")"
  66.  
  67.             Rs2.Open Sql, CnConectividad
  68.             Set Rs2 = Nothing
  69.  
  70.             Rs1.Open "Select b.nolinea,b.monto,c.mes,c.ano,a.idinterno" _
  71.                 & " from fac_detfact as a, fac_detcob as b, fac_encfact as c" _
  72.                 & " where a.idinterno=b.idinterno and c.estado=0 and " _
  73.                 & " c.ID = A.IdFact and a.idinterno=" & Rs!IdInterno, Cn
  74.  
  75.             If Not ConsultaVacia(Rs1) Then
  76.              While Not FinDeTabla(Rs1)
  77.  
  78.             Factura1 = Format(Rs1!IdInterno, "00000000000000000000")
  79.             Monto1 = Format(Rs1!Monto, "000000000000000000")
  80.             Linea = Format(Rs1!NoLinea, "0000")
  81.  
  82.                  Sql1 = "Insert into parrubrosrecibosservicios(codigoconvenio,valorservicio,periodorecibo,
  83.   numerofacturarecibo, codigorubro,montorubro)values(" _
  84.                     & "'001'" & "," & "'Electricidad                  '" & "," & PonerComillas(Rs!FchLectura) & "," & PonerComillas(Factura1) & "," & PonerComillas(Linea) & "," & Monto1 & ")"
  85.  
  86.                 Rs2.Open Sql1, CnConectividad
  87.                 Set Rs2 = Nothing
  88.  
  89.                 Rs1.MoveNext
  90.              Wend
  91.  
  92.             End If
  93.  
  94.             Rs.MoveNext
  95.  
  96.             Set Rs1 = Nothing
  97.  
  98.        Wend
  99.         MsgBox "Proceso concluido.  Medidores procesados: " & Barra.Value
  100.    Else
  101.         MsgBox "No hay datos para exportar"
  102.    End If
  103. Set Rs = Nothing
  104.  
  105. End Sub
  106.  
  107.  
  108.  
  109. Código Exportación Infocomunicaciones
  110.  
  111. Dim CadenaConexion As String
  112. Dim col As Integer
  113. Dim BNCR As String
  114. Dim Server As String
  115. Dim rs As New ADODB.Recordset, Rs2 As New ADODB.Recordset
  116. Dim Rs1 As New ADODB.Recordset
  117. Dim Sql As String
  118. Dim Sql1 As String
  119. Dim Descripcion As String, Factura As String, Monto As String
  120. Dim Factura1 As String, Monto1 As String, Fecha As String, Linea As String
  121. Dim CnConectividad As New ADODB.Connection
  122. Dim Identificador As String, Nombre As String, Infocomunicaciones As String
  123. Dim x As Long
  124. Dim LFecha As New clsFecha
  125. LFecha.Fecha = TraeFechaServer
  126.  
  127.         rs.Open "select sum(d.monto)as monto,d.fecha,b.nombre,c.fchvence,b.codasociado,b.direccion," _
  128.         & " a.codservicio,a.idinterno" _
  129.         & " from atp_factcatvdet as a, fac_asociados as b, atp_factcatvenc as c,atp_factcatvdetcob as d,      atp_suscripcionescatv as e" _
  130.         & " where c.id=a.idfact and c.estado=0 and e.codsocio=b.codasociado And A.IdInterno = D.IdInterno and a.codservicio=e.id" _
  131.         & " group by d.fecha,b.nombre,c.fchvence,b.codasociado,b.direccion,a.codservicio,a.idinterno", Cn
  132.  
  133. Server = Servidor1
  134. BNCR = BaseDatos1
  135.  
  136. CadenaConexion = "Provider=SQLOLEDB.1;Password=coop34LF4roRu1z;Persist Security " _
  137.     & "Info=False;User ID=sa;Initial Catalog=" & BNCR _
  138.     & ";Data Source=" & Server
  139.  
  140.     CnConectividad.Open CadenaConexion
  141.  
  142.     Barra.Value = 0
  143.     Barra.Max = 7000
  144.  
  145.    If Not ConsultaVacia(rs) Then
  146.         While Not FinDeTabla(rs)
  147.  
  148.             Descripcion = Infocomunicaciones & "," & rs!CodAsociado & "," & rs!Direccion
  149.             x = Len(Descripcion)
  150.  
  151.             If x <= 60 Then
  152.                 Descripcion = Descripcion & String(60 - Len(Descripcion), " ")
  153.             Else
  154.                 x = Len(rs!CodAsociado)
  155.                 x = 60 - x
  156.                 Descripcion = Infocomunicaciones & rs!CodAsociado & Mid(rs!Direccion, 1, x)
  157.             End If
  158.  
  159.             Identificador = rs!codservicio & String(30 - Len(rs!codservicio), " ")
  160.             Nombre = rs!Nombre & String(50 - Len(rs!Nombre), " ")
  161.             Monto = Format(rs!Monto, "000000000000000000")
  162.             Factura = Format(rs!IdInterno, "00000000000000000000")
  163.             Fecha = LFecha.Fecha '8 & LFecha.Hora6
  164.  
  165.             Barra.Value = Barra.Value + 1
  166.  
  167.             Sql = "Insert into PARRecibosServicios(codigoconvenio,valorservicio,periodorecibo,numerofacturarecibo,descripcionservicio," _
  168.                     & "tipoidentificacion,identificacioncliente,nombrecliente,montototalrecibo,fechavencimientorecibo,estado,
  169. fecharegistro,estadosincronizacion)values(" _
  170.                     & "'001'" & "," & "'Infocomunicaciones            '" & "," & rs!Fecha & "," & PonerComillas(Factura) & "," & PonerComillas(Descripcion) & "," & "'002'" & "," & PonerComillas(Identificador) & "," & PonerComillas(Nombre) & "," _
  171.                     & PonerComillas(Monto) & "," & rs!FchVence & "," & 1 & "," & PonerComillas(Fecha) & "," & 0 & ")"
  172.  
  173.             Rs2.Open Sql, CnConectividad
  174.             Set Rs2 = Nothing
  175.  
  176.             Rs1.Open "Select b.nolinea,b.descripcion,b.monto,c.mes,c.ano,a.idinterno" _
  177.                 & " from atp_factcatvdet as a, atp_factcatvdetcob as b, atp_factcatvenc as c" _
  178.                 & " where a.idinterno=b.idinterno and c.estado=0 and a.idinterno=" & rs!IdInterno _
  179.                 & " and c.ID = A.IdFact ", Cn
  180.  
  181.  
  182.             If Not ConsultaVacia(Rs1) Then
  183.              While Not FinDeTabla(Rs1)
  184.  
  185.              Factura1 = Format(Rs1!IdInterno, "00000000000000000000")
  186.              Monto1 = Format(Rs1!Monto, "000000000000000000")
  187.  
  188.              Select Case Trim(Rs1!Descripcion)
  189.                 Case "I.Venta": Linea = "0004"
  190.                 Case "Multa": Linea = "0250"
  191.                 Case "Contenido CoopeAlfaroRuiz": Linea = "1001"
  192.                 Case "Costo Administrativo": Linea = "1003"
  193.                 Case "Infraestructura": Linea = "1004"
  194.                 Case "Acceso Internet": Linea = "1010"
  195.  
  196.              End Select
  197.              'Linea = Format(Rs1!nolinea, "1000")             
  198.  
  199.                  Sql1 = "Insert into parrubrosrecibosservicios(codigoconvenio,valorservicio,periodorecibo,  numerofacturarecibo,codigorubro,montorubro)values(" _
  200.                     & "'001'" & "," & "'Infocomunicaciones            '" & "," & PonerComillas(rs!Fecha) & "," & PonerComillas(Factura1) & "," & PonerComillas(Linea) & "," & Monto1 & ")"
  201.  
  202.                 Rs2.Open Sql1, CnConectividad
  203.                 Set Rs2 = Nothing
  204.  
  205.                 Rs1.MoveNext
  206.              Wend
  207.  
  208.             End If
  209.  
  210.             rs.MoveNext
  211.  
  212.             Set Rs1 = Nothing
  213.  
  214.        Wend
  215.         MsgBox "Proceso concluido.  Medidores procesados: " & Barra.Value
  216.    Else
  217.         MsgBox "No hay datos para exportar"
  218.    End If
  219. Set rs = Nothing
  220.  
  221. End Sub
  222.  
  223.  
jump to top
24 views
plaintext | download | copy to clipboard | embed