Projecte/Treball Final de Carrera

(1)

Projecte/Treball Final de Carrera

Estudi:

Eng. Tècn. Informàtica de Gestió. Pla 1993

Títol:

Aplicació de comptabilitat

Document:

Annex I – Codi font i script SQL server

Alumne:

Joan Carles Jiménez Carmona

Director/Tutor:

Didac Barragan Pascual

Departament:

Informàtica i Matemàtica Aplicada

Àrea:

LSI

(2)

Aplicació:

(3)

Índex del document:

ANNEX I – CODI FONT ... 4

1. U

SUARIOS

D

ELEGACIONES

.

CLS

... 5

2. A

CRDEU

.

CLS

... 7

3. A

PUNTES

.

CLS

... 10

4. A

SIENTOS

.

CLS

... 14

5. A

UXILIAR

.

CLS

... 17

6. C

ARTERA

.

CLS

... 20

7. C

IERRE

.

CLS

... 23

8. C

UENTAS

.

CLS

... 26

9. D

ELEGACIONES

.

CLS

... 28

10. D

IARIOS

.

CLS

... 32

11. D

OCUMENTOS

.

CLS

... 34

12. E

JERCICIOS

.

CLS

... 37

13. E

MPRESAS

.

CLS

... 41

14. F

ORMAS

P

AGO

.

CLS

... 47

15. I

MPUESTOS

.

CLS

... 50

16. I

MPUESTOS

A

CRDEU

.

CLS

... 53

17. I

NFORMES

.

CLS

... 54

18. I

RPF

.

CLS

... 58

19. M

ENUS

.

CLS

... 60

20. M

ENUS

U

SUARIOS

.

CLS

... 61

21. N

UMERACION

.

CLS

... 64

22. P

ERGAN

.

CLS

... 67

23. P

ERSONAL

.

CLS

... 70

24. R

EGISTRO

I

RPF

.

CLS

... 73

25. R

EGISTRO

I

VA

.

CLS

... 76

26. R

EGULARIZACION

.

CLS

... 79

27. S

ITUACION

.

CLS

... 82

28. U

SUARIOS

.

CLS

... 85

29. T

EMPORALES

.

BAS

... 88

30.

FRM

A

BOUT

.

FRM

... 89

31.

FRM

A

CRDEU

.

FRM

... 93

32.

FRM

A

PUNTE

.

FRM

... 107

33.

FRM

A

SIENTOS

.

FRM

... 154

34.

FRM

B

ALANCE

P

Y

G.

FRM

... 169

35.

FRM

B

ALANCE

S

ITUACION

.

FRM

... 177

36.

FRM

C

IERRE

.

FRM

... 197

37.

FRM

C

UENTAS

.

FRM

... 202

38.

FRM

D

ELEGACIONES

.

FRM

... 209

39.

FRM

D

IARIOS

.

FRM

... 218

40.

FRM

D

OCUMENTOS

.

FRM

... 224

41.

FRM

E

JERCICIOS

.

FRM

... 234

42.

FRM

E

MPRESAS

.

FRM

... 243

43.

FRM

E

XTRACTOS

.

FRM

... 254

44.

FRM

F

ORMAS

P

AGO

.

FRM

... 261

45.

FRM

I

MPUESTOS

.

FRM

... 271

46.

FRM

I

NVERTIR

.

FRM

... 281

47.

FRM

I

RPF

.

FRM

... 284

48.

FRM

L

IBRO

D

IARIO

.

FRM

... 292

49.

FRM

L

IBRO

I

VA

.

FRM

... 298

50.

FRM

L

ISTA

E

MPRESAS

.

FRM

... 303

51.

FRM

L

OGIN

.

FRM

... 307

52.

FRM

L

UPA

.

FRM

... 312

53.

FRM

M

AIN

.

FRM

... 316

54.

FRM

M

ENU

.

FRM

... 319

55.

FRM

M

ENUS

U

SUARIOS

.

FRM

... 322

(4)

56.

FRM

N

UMERACION

.

FRM

... 330

57.

FRM

O

PERACIONES

T

ERCEROS

.

FRM

... 338

58.

FRM

P

ERGAN

.

FRM

... 343

59.

FRM

P

ERSONAL

.

FRM

... 352

60.

FRM

R

EGISTRO

I

VA

.

FRM

... 362

61.

FRM

R

EGULARIZACION

.

FRM

... 377

62.

FRM

S

ELECCION

C

ARTERA

.

FRM

... 383

63.

FRM

S

ITUACION

.

FRM

... 390

64.

FRM

S

PLASH

.

FRM

... 399

65.

FRM

S

UMAS

S

ALDOS

.

FRM

... 402

66.

FRM

U

SUARIOS

.

FRM

... 412

67.

FRM

U

SUARIOS

D

ELEGACIONS

.

FRM

... 418

68. A

PI

.

BAS

... 422

69. G

LOBAL

.

BAS

... 425

70. I

DENTIFICACIO

.

BAS

... 434

71. I

NIFUNC

.

BAS

... 435

72. N

IF

.

BAS

... 436

73. P

RINCIPAL

.

BAS

... 438

74. S

EGURIDAD

.

BAS

... 439

(5)

(6)

1. UsuariosDelegaciones.cls

VERSION 1.0 CLASS BEGIN MultiUse = -1 'True Persistable = 0 'NotPersistable DataBindingBehavior = 0 'vbNone DataSourceBehavior = 0 'vbNone MTSTransactionMode = 0 'NotAnMTSObject END

Attribute VB_Name = "UsuariosDelegaciones" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False

Attribute VB_Ext_KEY = "SavedWithClassBuilder6" ,"Yes" Attribute VB_Ext_KEY = "Top_Level" ,"Yes"

Option Explicit

Public Enum ErroresUsuariosDelegaciones iInsertError = (vbObjectError + 1) iUpdateError = (vbObjectError + 2) iDeleteError = (vbObjectError + 3) iListError = (vbObjectError + 4) iValidarError = (vbObjectError + 5) End Enum

Dim mAuxiliar As Auxiliar

Private Function sDate(fecha As Variant) As String Set mAuxiliar = New Auxiliar

sDate = mAuxiliar.sDate(fecha) Set mAuxiliar = Nothing End Function

Public Sub Update(cn As ADODB.Connection, codigo_usuario As String, codigo_empresa As String, _

codigo_delegacion As String, activo As Integer, ultimo_usuario As String) Dim strCmd As String

Dim rs As New ADODB.Recordset

On Error GoTo UpdateError

strCmd = "SELECT * FROM UsuariosDelegaciones WHERE codigo_usuario = '" & codigo_usuario _

& "' AND codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion & "'"

rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs("activo") = activo rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub UpdateError:

Err.Raise iUpdateError, , FormatError(cn, "Error en Update.") End Sub

Public Function GetList(cn As ADODB.Connection, Optional strWhere As String, Optional strOrder As String) As Object

Dim strCmd As String Dim rs As ADODB.Recordset

strCmd = "SELECT * FROM UsuariosDelegaciones"

If Len(strWhere) > 0 Then

strCmd = strCmd & " WHERE " & strWhere End If

(7)

If Len(strOrder) > 0 Then

strCmd = strCmd & " ORDER BY " & strOrder End If

Set rs = New ADODB.Recordset

On Error GoTo GetListError

rs.Open strCmd, cn, adOpenStatic, adLockOptimistic

Set GetList = rs

Exit Function GetListError:

Err.Raise iListError, , FormatError(cn, "Error en GetList.") End Function

Public Function ValidarUsuario(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, _

usuario As String, contraseña As String) As Boolean Dim strCmd As String

strCmd = "SELECT Usuarios.codigo_usuario, Usuarios.contraseña FROM Usuarios " _ & "INNER JOIN UsuariosDelegaciones ON (Usuarios.codigo_usuario =

UsuariosDelegaciones.codigo_usuario) " _

& "WHERE (UsuariosDelegaciones.codigo_empresa='" & codigo_empresa & " ') AND " _ & "(UsuariosDelegaciones.codigo_delegacion='" & codigo_delegacion & "') AND " _ & "(Usuarios.codigo_usuario='" & usuario & "') AND (Usuarios.contraseña='" _ & contraseña & "') AND (UsuariosDelegaciones.activo <> 0)"

On Error GoTo ValidarUsuarioError

ValidarUsuario = Not rs.EOF

Exit Function ValidarUsuarioError:

Err.Raise iValidarError, , FormatError(cn, "Error en GetList.") End Function

Private Function FormatError(cn As ADODB.Connection, strError As String) As String Dim eError As ADODB.Error

Dim sAux As String

If Len(strError) > 0 Then sAux = strError & vbCrLf End If

For Each eError In cn.Errors

sAux = sAux & eError.Source & " (Referencia " & eError.Number & "): " & eError.Description & vbCrLf

Next eError

FormatError = sAux End Function

(8)

2. Acrdeu.cls

Attribute VB_Name = "Acrdeu"

Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False

Option Explicit

Public Enum ErroresAcrdeu

iInsertError = (vbObjectError + 1) iUpdateError = (vbObjectError + 2) iDeleteError = (vbObjectError + 3) iListError = (vbObjectError + 4) End Enum

Public Sub Insert(cn As ADODB.Connection, codigo_empresa As String, cuenta As String, codigo As String, _

tipo As String, nacionalidad As String, impuesto As String, forma_pago As String, primer_dia As Integer, _

descuento As Double, irpf As String, contrapartida As String, ultimo_usuario As String) Dim strCmd As String

On Error GoTo InsertError

strCmd = "SELECT * FROM Acrdeu WHERE 1 = 0" rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs.AddNew rs("codigo_empresa") = codigo_empresa rs("cuenta") = cuenta rs("codigo") = codigo rs("tipo") = tipo rs("nacionalidad") = nacionalidad rs("impuesto") = impuesto rs("forma_pago") = forma_pago rs("primer_dia") = primer_dia rs("descuento") = descuento rs("irpf") = irpf rs("contrapartida") = contrapartida rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub InsertError:

Err.Raise iInsertError, , FormatError(cn, "Error en Insert.") End Sub

Public Sub Update(cn As ADODB.Connection, codigo_empresa As String, cuenta As String, codigo As String, _

(9)

tipo As String, nacionalidad As String, impuesto As String, forma_pago As String, primer_dia As Integer, _

descuento As Double, irpf As String, contrapartida As String, ultimo_usuario As String) Dim strCmd As String

strCmd = "SELECT * FROM Acrdeu WHERE codigo_empresa = '" & codigo_empresa & "' AND cuenta = '" & cuenta _

& "' AND codigo = '" & codigo & "'"

rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs("tipo") = tipo rs("nacionalidad") = nacionalidad rs("impuesto") = impuesto rs("forma_pago") = forma_pago rs("primer_dia") = primer_dia rs("descuento") = descuento rs("irpf") = irpf rs("contrapartida") = contrapartida rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub UpdateError:

Public Sub Delete(cn As ADODB.Connection, codigo_empresa As String, cuenta As String, codigo As String)

Dim strCmd As String

On Error GoTo DeleteError

strCmd = "DELETE FROM Acrdeu WHERE codigo_empresa = '" & codigo_empresa & "' AND cuenta = '" & cuenta _

& "' AND codigo = '" & codigo & "'" cn.Execute strCmd

Exit Sub DeleteError:

Err.Raise iDeleteError, , FormatError(cn, "Error en Delete.") End Sub

strCmd = "SELECT * FROM Acrdeu"

Set GetList = rs

(10)

End Function

Dim sAux As String

Next eError

(11)

3. Apuntes.cls

Attribute VB_Name = "Apuntes" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False

Option Explicit

Public Enum ErroresApuntes

Public Sub Insert(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, _

codigo_documento As String, numero_documento As String, numero_apunte As Integer, codigo_diario As String, _

fecha_apunte As String, fecha_documento As String, referencia As String, cuenta As String, _

concepto As String, debe As Double, haber As Double, tipo As String, tag As String, ultimo_usuario As String)

strCmd = "SELECT * FROM Apuntes WHERE 1 = 0" rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs.AddNew rs("codigo_empresa") = codigo_empresa rs("codigo_delegacion") = codigo_delegacion rs("codigo_documento") = codigo_documento rs("numero_documento") = numero_documento rs("numero_apunte") = numero_apunte rs("codigo_diario") = codigo_diario rs("fecha_apunte") = sDate(fecha_apunte) rs("fecha_documento") = sDate(fecha_documento) rs("referencia") = referencia rs("cuenta") = cuenta rs("concepto") = concepto rs("debe") = debe rs("haber") = haber rs("tipo") = tipo rs("tag") = tag rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing

(12)

Exit Sub InsertError:

Public Sub Update(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, _

strCmd = "SELECT * FROM Apuntes WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion _

& "' AND codigo_documento = '" & codigo_documento & "' AND numero_documento = '" & numero_documento _

& "' AND numero_apunte = " & numero_apunte rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs("codigo_diario") = codigo_diario rs("fecha_apunte") = sDate(fecha_apunte) rs("fecha_documento") = sDate(fecha_documento) rs("referencia") = referencia rs("cuenta") = cuenta rs("concepto") = concepto rs("debe") = debe rs("haber") = haber rs("tipo") = tipo rs("tag") = tag rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub UpdateError:

Public Sub Delete(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, _

codigo_documento As String, numero_documento As String, numero_apunte As Integer, transaccion_activa As Boolean)

If Not transaccion_activa Then cn.BeginTrans If numero_apunte = 0 Then

strCmd = "DELETE FROM Cartera WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" _

& codigo_delegacion & "' AND codigo_documento = '" & codigo_documento & "' AND numero_documento = '" _

& numero_documento & "'" cn.Execute strCmd

strCmd = "DELETE FROM RegistroIVA WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" _

strCmd = "DELETE FROM RegistroIRPF WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" _

(13)

strCmd = "DELETE FROM Apuntes WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" _

Else

& numero_documento & "' AND numero_apunte = " & numero_apunte cn.Execute strCmd

strCmd = "DELETE FROM RegistroIVA WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" _

strCmd = "DELETE FROM RegistroIrpf WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" _

End If

If Not transaccion_activa Then cn.CommitTrans

If Not transaccion_activa Then cn.RollbackTrans

strCmd = "SELECT * FROM Apuntes"

Set GetList = rs

(14)

Dim sAux As String

Next eError

(15)

4. Asientos.cls

Attribute VB_Name = "Asientos" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False

Option Explicit

Public Enum ErroresAsientos

Public Sub Insert(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, _

strCmd = "SELECT * FROM Apuntes WHERE 1 = 0" rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs.AddNew rs("codigo_empresa") = codigo_empresa rs("codigo_delegacion") = codigo_delegacion rs("codigo_documento") = codigo_documento rs("numero_documento") = numero_documento rs("numero_apunte") = numero_apunte rs("codigo_diario") = codigo_diario rs("fecha_apunte") = sDate(fecha_apunte) rs("fecha_documento") = sDate(fecha_documento) rs("referencia") = referencia rs("cuenta") = cuenta rs("concepto") = concepto rs("debe") = debe rs("haber") = haber rs("tipo") = tipo rs("tag") = tag rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing

(16)

Public Sub Update(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, _

strCmd = "SELECT * FROM Apuntes WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion _

& "' AND codigo_documento = '" & codigo_documento & "' AND numero_documento = '" & numero_documento _

& "' AND numero_apunte = " & numero_apunte rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs("codigo_diario") = codigo_diario rs("fecha_apunte") = fecha_apunte rs("fecha_documento") = fecha_documento rs("referencia") = referencia rs("cuenta") = cuenta rs("concepto") = concepto rs("debe") = debe rs("haber") = haber rs("tipo") = tipo rs("tag") = tag rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub UpdateError:

Public Sub Delete(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, _

codigo_documento As String, numero_documento As String, numero_apunte As Integer) Dim strCmd As String

If numero_apunte = 0 Then

Else

End If

(17)

strCmd = "SELECT * FROM Apuntes"

Set GetList = rs

Dim sAux As String

Next eError

(18)

5. Auxiliar.cls

Attribute VB_Name = "Auxiliar" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False Option Explicit

Public Function sDate(fecha As Variant) As String If IsDate(fecha) Then

sDate = Format(fecha, "yyyymmdd") Else

sDate = "" End If

End Function

Public Function dDate(yyyymmdd As Variant) As String If yyyymmdd <> "" Then

dDate = Mid$(yyyymmdd, 7, 2) & "/" & Mid$(yyyymmdd, 5, 2) & "/" & Mid$(yyyymmdd, 1, 4)

Else

dDate = "" End If

End Function

Public Function vBuscarCampo(tabla As String, campo As String, condicion As String, Optional default As Variant) As Variant

Dim strSQL As String Dim rs As ADODB.Recordset Dim cn As ADODB.Connection Dim pos As Integer

Set cn = New ADODB.Connection cn.Open "File name=Contabilidad.udl"

strSQL = "SELECT " & campo & " FROM " & tabla & " WHERE " & condicion

pos = InStr(1, UCase(campo), " AS ")

If pos > 0 Then campo = Mid(campo, pos + 4)

rs.Open strSQL, cn, adOpenStatic, adLockOptimistic

vBuscarCampo = IIf(rs.EOF, default, IIf(IsNull(rs(campo)), default, rs(campo)))

Set rs = Nothing Set cn = Nothing End Function

Public Function vBuscarCampoTemporal(tabla As String, campo As String, condicion As String, Optional default As Variant) As Variant

Dim strSQL As String Dim rs As ADODB.Recordset Dim cn As ADODB.Connection Dim pos As Integer

Set cnt = New ADODB.Connection cnt.Open "File name=Temporal.udl"

strSQL = "SELECT " & campo & " FROM " & tabla & " WHERE " & condicion

pos = InStr(1, UCase(campo), " AS ")

(19)

rs.Open strSQL, cnt, adOpenStatic, adLockOptimistic

vBuscarCampoTemporal = IIf(rs.EOF, default, IIf(IsNull(rs(campo)), default, rs(campo)))

Set rs = Nothing Set cnt = Nothing End Function

Public Function DeleteExt(cn As ADODB.Connection, strTable As String, Optional strWhere As String)

strCmd = "SELECT * FROM " & strTable

While Not rs.EOF rs.Delete rs.MoveNext Wend rs.Close Set rs = Nothing Exit Function DeleteError:

Err.Raise Err.Number, , FormatError(cn, "Error en DeleteExt.") End Function

Public Function GetListExt(cn As ADODB.Connection, strTable As String, strFields As String, Optional strWhere As String, _

Optional strGroup As String, Optional strHaving As String, Optional strOrder As String) As Object

strCmd = "SELECT " & strFields & " FROM " & strTable

If Len(strGroup) > 0 Then

strCmd = strCmd & " GROUP BY " & strGroup End If

If Len(strHaving) > 0 Then

strCmd = strCmd & " HAVING BY " & strHaving End If

Set GetListExt = rs

Err.Raise Err.Number, , FormatError(cn, "Error en GetListExt.") End Function

(20)

Dim sAux As String

Next eError

(21)

6. Cartera.cls

Attribute VB_Name = "Cartera" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False

Option Explicit

Public Enum ErroresCartera

iInsertError = (vbObjectError + 1) iUpdateError = (vbObjectError + 2) iDeleteError = (vbObjectError + 3) iListError = (vbObjectError + 4) iSetEstado = (vbObjectError + 5) End Enum

Public Sub Insert(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, codigo_documento As String, _

numero_documento As String, numero_apunte As Integer, fecha_vto As String, tipo_acrdeu As String, codigo_personal As String, _

concepto As String, tipo As String, estado As String, importe As Double, fecha_cierre As String, _

codigo_documento_cierre As String, numero_documento_cierre As String, ultimo_usuario As String)

strCmd = "SELECT * FROM Cartera WHERE 1 = 0" rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs.AddNew rs("codigo_empresa") = codigo_empresa rs("codigo_delegacion") = codigo_delegacion rs("codigo_documento") = codigo_documento rs("numero_documento") = numero_documento rs("numero_apunte") = numero_apunte rs("fecha_vto") = sDate(fecha_vto) rs("tipo_acrdeu") = tipo_acrdeu rs("codigo_personal") = codigo_personal rs("concepto") = concepto rs("tipo") = tipo rs("estado") = estado rs("importe") = importe rs("fecha_cierre") = sDate(fecha_cierre) rs("codigo_documento_cierre") = codigo_documento_cierre rs("numero_documento_cierre") = numero_documento_cierre rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing

(22)

Public Sub Update(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, codigo_documento As String, _

numero_documento As String, numero_apunte As Integer, fecha_vto As String, tipo_acrdeu As String, codigo_personal As String, _

concepto As String, tipo As String, estado As String, importe As Double, fecha_cierre As String, _

strCmd = "SELECT * FROM Cartera WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" _

& numero_documento & "' AND numero_apunte = " & numero_apunte & " AND fecha_vto = '" & sDate(fecha_vto) & "'"

rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs("tipo_acrdeu") = tipo_acrdeu rs("codigo_personal") = codigo_personal rs("concepto") = concepto rs("tipo") = tipo rs("estado") = estado rs("importe") = importe rs("fecha_cierre") = sDate(fecha_cierre) rs("codigo_documento_cierre") = codigo_documento_cierre rs("numero_documento_cierre") = numero_documento_cierre rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub UpdateError:

Public Sub Delete(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, codigo_documento As String, _

numero_documento As String, numero_apunte As Integer, fecha_vto As String) Dim strCmd As String

cn.Execute strCmd

strCmd = "SELECT * FROM Cartera"

(23)

Set GetList = rs

Public Sub SetEstado(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, codigo_documento As String, _

numero_documento As String, numero_apunte As Integer, fecha_vto As String, estado As String, fecha_cierre As String, _

strCmd = "SELECT * FROM Cartera WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" _

rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs("estado") = estado rs("fecha_cierre") = sDate(fecha_cierre) rs("codigo_documento_cierre") = codigo_documento_cierre rs("numero_documento_cierre") = numero_documento_cierre rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub UpdateError:

Dim sAux As String

Next eError

(24)

7. Cierre.cls

Attribute VB_Name = "Cierre"

Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False Option Explicit

Public Enum ErroresCierre

iInsertError = (vbObjectError + 1) iDeleteError = (vbObjectError + 3) iListError = (vbObjectError + 4) End Enum

Dim mAuxiliar As Auxiliar Dim mApuntes As Apuntes Dim mEjercicios As Ejercicios Dim mNumeracion As numeracion

Public Sub Insert(cn As ADODB.Connection, codigo_empresa As String, ejercicio As Integer, codigo_documento_cierre As String, _

codigo_documento_apertura As String, ultimo_usuario As String, transaccion_activa As Boolean)

Dim strCmd As String Dim debe As Double Dim haber As Double Dim apunte As Integer Dim rs As ADODB.Recordset Dim rsAux As ADODB.Recordset

apunte = 1

Set mEjercicios = New Ejercicios

Set rsAux = mEjercicios.GetList(cn, "codigo_empresa = '" & codigo_empresa & "' and ejercicio = " & ejercicio)

strCmd = "SELECT cuenta, SUM(debe-haber) AS saldo FROM Apuntes WHERE codigo_empresa = '" & codigo_empresa _

& "' AND fecha_apunte BETWEEN '" & rsAux("fecha_inicial") & "' AND '" & rsAux("fecha_final") & "' " _

& "AND cuenta >= '1' AND cuenta < '6' GROUP BY cuenta HAVING SUM(debe-haber) <> 0 ORDER BY cuenta"

rs.Open strCmd, cn, adOpenStatic, adLockOptimistic If Not rs.EOF Then

Set mApuntes = New Apuntes Set mAuxiliar = New Auxiliar

If Not transaccion_activa Then cn.BeginTrans

While Not rs.EOF

If rs("saldo") > 0 Then

mApuntes.Insert cn, codigo_empresa, codigo_empresa,

codigo_documento_cierre, String(10, "9"), Format(apunte, "00000"), "99999",

mAuxiliar.dDate(rsAux("fecha_final")), "", "", rs("cuenta"), "ASIENTO DE CIERRE", 0, str(rs("saldo")), "A", "", ultimo_usuario

codigo_documento_apertura, String(10, "0"), Format(apunte, "00000"), "00000", DateAdd("yyyy", 1, mAuxiliar.dDate(rsAux("fecha_inicial"))), "", "", rs("cuenta"), "ASIENTO DE APERTURA", str(rs("saldo")), 0, "A", "", ultimo_usuario

(25)

Else

codigo_documento_cierre, String(10, "9"), Format(apunte, "00000"), "99999",

mAuxiliar.dDate(rsAux("fecha_final")), "", "", rs("cuenta"), "ASIENTO DE CIERRE", str(-rs("saldo")), 0, "A", "", ultimo_usuario

codigo_documento_apertura, String(10, "0"), Format(apunte, "00000"), "00000", DateAdd("yyyy", 1, mAuxiliar.dDate(rsAux("fecha_inicial"))), "", "", rs("cuenta"), "ASIENTO DE APERTURA", 0, str(-rs("saldo")), "A", "", ultimo_usuario

End If apunte = apunte + 1 rs.MoveNext Wend

mEjercicios.Update cn, codigo_empresa, ejercicio,

mAuxiliar.dDate(rsAux("fecha_inicial")), mAuxiliar.dDate(rsAux("fecha_final")), mAuxiliar.dDate(rsAux("inicio_bloqueo")), mAuxiliar.dDate(rsAux("final_bloqueo")), rsAux("regularizado"), "S", ultimo_usuario

If mAuxiliar.vBuscarCampo("Ejercicios", "ejercicio", "codigo_empresa = '" & codigo_empresa & "' and ejercicio = " & ejercicio + 1, "#") = "#" Then

mEjercicios.Insert cn, codigo_empresa, ejercicio + 1, DateAdd("yyyy", 1, mAuxiliar.dDate(rsAux("fecha_inicial"))), DateAdd("yyyy", 1,

mAuxiliar.dDate(rsAux("fecha_final"))), "", "", "N", "N", ultimo_usuario, True End If

Set mAuxiliar = Nothing Set mApuntes = Nothing End If

Set mEjercicios = Nothing rs.Close

Set rs = Nothing

Public Sub Delete(cn As ADODB.Connection, codigo_empresa As String, ejercicio As Integer, codigo_documento_cierre As String, _

codigo_documento_apertura As String, ultimo_usuario As String, transaccion_activa As Boolean)

& codigo_empresa & "' AND codigo_documento = '" & codigo_documento_cierre & "' AND numero_documento = '" _

& String(10, "9") & "'" cn.Execute strCmd

& codigo_empresa & "' AND codigo_documento = '" & codigo_documento_apertura & "' AND numero_documento = '" _

& String(10, "0") & "'" cn.Execute strCmd

Set mAuxiliar = New Auxiliar Set mEjercicios = New Ejercicios

Set rs = mEjercicios.GetList(cn, "codigo_empresa = '" & codigo_empresa & "' and ejercicio = " & ejercicio)

mEjercicios.Update cn, sEmpresa, ejercicio, mAuxiliar.dDate(rs("fecha_inicial")), mAuxiliar.dDate(rs("fecha_final")), mAuxiliar.dDate(rs("inicio_bloqueo")),

mAuxiliar.dDate(rs("final_bloqueo")), rs("regularizado"), "N", ultimo_usuario Set mEjercicios = Nothing

(26)

Set mAuxiliar = Nothing

Set rs = Nothing

Dim rs As ADODB.Recordset

Set mApuntes = New Apuntes

Set rs = mApuntes.GetList(cn, strWhere, strOrder) Set mApuntes = Nothing

Set GetList = rs

Dim sAux As String

Next eError

(27)

8. Cuentas.cls

Attribute VB_Name = "Cuentas" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False

Option Explicit

Public Enum ErroresCuentas

Public Sub Insert(cn As ADODB.Connection, codigo_empresa As String, cuenta As String, descripcion As String, pases As String, _

nivel As Integer, ultimo_usuario As String) Dim strCmd As String

strCmd = "SELECT * FROM Cuentas WHERE 1 = 0" rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs.AddNew rs("codigo_empresa") = codigo_empresa rs("cuenta") = cuenta rs("descripcion") = descripcion rs("pases") = pases rs("nivel") = nivel rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub InsertError:

Public Sub Update(cn As ADODB.Connection, codigo_empresa As String, cuenta As String, descripcion As String, pases As String, _

nivel As Integer, ultimo_usuario As String) Dim strCmd As String

strCmd = "SELECT * FROM Cuentas WHERE codigo_empresa = '" & codigo_empresa & "' AND cuenta = '" & cuenta & "'"

(28)

rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs("descripcion") = descripcion rs("pases") = pases rs("nivel") = nivel rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub UpdateError:

Public Sub Delete(cn As ADODB.Connection, codigo_empresa As String, cuenta As String) Dim strCmd As String

strCmd = "DELETE FROM Cuentas WHERE codigo_empresa = '" & codigo_empresa & "' AND cuenta = '" & cuenta & "'"

cn.Execute strCmd

strCmd = "SELECT * FROM Cuentas"

Set GetList = rs

Dim sAux As String

Next eError

(29)

9. Delegaciones.cls

Attribute VB_Name = "Delegaciones" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False

Option Explicit

Public Enum ErroresDelegaciones

Public Sub Insert(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, nombre As String, _

direccion As String, cp As String, poblacion As String, provincia As String, pais As String, telefono As String, _

fax As String, correo As String, ultimo_usuario As String, transaccion_activa As Boolean)

Dim rs As New ADODB.Recordset Dim rsAux As New ADODB.Recordset

strCmd = "SELECT * FROM Delegaciones WHERE 1 = 0" rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs.AddNew rs("codigo_empresa") = codigo_empresa rs("codigo_delegacion") = codigo_delegacion rs("nombre") = nombre rs("direccion") = direccion rs("cp") = cp rs("poblacion") = poblacion rs("provincia") = provincia rs("pais") = pais rs("telefono") = telefono rs("fax") = fax rs("correo") = correo rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close

strCmd = "SELECT codigo_usuario FROM Usuarios GROUP BY codigo_usuario" rs.Open strCmd, cn, adOpenStatic, adLockOptimistic

(30)

If Not rs.EOF Then

strCmd = "SELECT * FROM UsuariosDelegaciones WHERE 1 = 0" rsAux.Open strCmd, cn, adOpenStatic, adLockOptimistic

rs.MoveFirst While Not rs.EOF rsAux.AddNew rsAux("codigo_usuario") = rs("codigo_usuario") rsAux("codigo_empresa") = codigo_empresa rsAux("codigo_delegacion") = codigo_delegacion rsAux("activo") = 0 rsAux("ultima_modificacion") = sDate(Date) rsAux("ultimo_usuario") = ultimo_usuario rsAux.Update rs.MoveNext Wend rsAux.Close End If rs.Close

Set rs = Nothing Set rsAux = Nothing

Public Sub Update(cn As ADODB.Connection, codigo_empresa As String, codigo_delegacion As String, nombre As String, _

direccion As String, cp As String, poblacion As String, provincia As String, pais As String, telefono As String, _

fax As String, correo As String, ultimo_usuario As String, transaccion_activa As Boolean)

strCmd = "SELECT * FROM Delegaciones WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" _

& codigo_delegacion & "'"

rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs("nombre") = nombre rs("direccion") = direccion rs("cp") = cp rs("poblacion") = poblacion rs("provincia") = provincia rs("pais") = pais rs("telefono") = telefono rs("fax") = fax rs("correo") = correo rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close

If codigo_empresa = codigo_delegacion Then

strCmd = "SELECT * FROM Empresas WHERE codigo_empresa = '" & codigo_empresa & "'"

rs("nombre") = nombre rs("direccion") = direccion rs("cp") = cp

(31)

rs("provincia") = provincia rs("pais") = pais rs("telefono") = telefono rs("fax") = fax rs("correo") = correo rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close End If

Set rs = Nothing

Exit Sub UpdateError:

Public Sub Delete(cn As ADODB.Connection, codigo_empresa As Variant, codigo_delegacion As Variant, transaccion_activa As Boolean)

strCmd = "DELETE FROM RegistroIRPF WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion & "'"

cn.Execute strCmd

strCmd = "DELETE FROM RegistroIVA WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion & "'"

cn.Execute strCmd

strCmd = "DELETE FROM Cartera WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion & "'"

cn.Execute strCmd

strCmd = "DELETE FROM Apuntes WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion & "'"

cn.Execute strCmd

strCmd = "DELETE FROM RestriccionesUsuariosMenus WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion & "'"

cn.Execute strCmd

strCmd = "DELETE FROM UsuariosDelegaciones WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion & "'" cn.Execute strCmd

strCmd = "DELETE FROM Delegaciones WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_delegacion = '" & codigo_delegacion & "'"

cn.Execute strCmd

strCmd = "SELECT * FROM Delegaciones"

(32)

Set GetList = rs

Dim sAux As String

Next eError

(33)

10. Diarios.cls

Attribute VB_Name = "Diarios" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = False Attribute VB_Exposed = False

Option Explicit

Public Enum ErroresDiarios

Public Sub Insert(cn As ADODB.Connection, codigo_empresa As String, codigo_diario As String, descripcion As String, ultimo_usuario As String)

strCmd = "SELECT * FROM Diarios WHERE 1 = 0" rs.Open strCmd, cn, adOpenStatic, adLockOptimistic rs.AddNew rs("codigo_empresa") = codigo_empresa rs("codigo_diario") = codigo_diario rs("descripcion") = descripcion rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub InsertError:

Public Sub Update(cn As ADODB.Connection, codigo_empresa As String, codigo_diario As String, descripcion As String, ultimo_usuario As String)

strCmd = "SELECT * FROM Diarios WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_diario = '" _

& codigo_diario & "'"

(34)

rs("ultima_modificacion") = sDate(Date) rs("ultimo_usuario") = ultimo_usuario rs.Update rs.Close Set rs = Nothing Exit Sub UpdateError:

Public Sub Delete(cn As ADODB.Connection, codigo_empresa As String, codigo_diario As String)

strCmd = "DELETE FROM Diarios WHERE codigo_empresa = '" & codigo_empresa & "' AND codigo_diario = '" _

& codigo_diario & "'" cn.Execute strCmd

strCmd = "SELECT * FROM Diarios"

Set GetList = rs

Dim sAux As String

Next eError