20190220100315_v0.7.4.js
8.14 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
exports.up = function(knex) {
return knex.schema.raw(
"if not exists (SELECT * FROM dbo.sysobjects where id = object_id("+
"N'dbo.[ACOBYPAG_VENCIMIENTOS]') and OBJECTPROPERTY(id, N'IsTable') = 1) begin "+
"CREATE TABLE ACOBYPAG_VENCIMIENTOS(" +
"[CYV] [char](1) NOT NULL," +
"[COD] [int] NOT NULL," +
"[FEP] [smalldatetime] NOT NULL," +
"[TIP] [char](1) NOT NULL," +
"[TCO] [char](2) NOT NULL," +
"[SUC] [int] NOT NULL," +
"[NCO] [bigint] NOT NULL," +
"[NCU] [bigint] NOT NULL," +
"[IPA] [money] NOT NULL," +
"[SAL] [money] NOT NULL," +
"[TCA] [money] NOT NULL," +
"[ZONA] [int] NOT NULL," +
"[FPA] [char](1) NOT NULL," +
"[REC] [bigint] NOT NULL," +
"[REP] [bigint] NOT NULL," +
"[FER] [smalldatetime] NULL," +
"[REM] [bigint] NOT NULL," +
"[FRE] [smalldatetime] NULL," +
"[PRO] [char](1) NOT NULL," +
"[FEV] [datetime] NULL," +
"[ANU] [char](1) NOT NULL," +
"[CCU] [int] NOT NULL," +
"[UCU] [int] NOT NULL," +
"[PLA] [int] NOT NULL," +
"[LUG] [int] NOT NULL," +
"[PFA] [char](1) NOT NULL," +
"[RES] [int] NOT NULL," +
"[LEG] [varchar](15) NOT NULL," +
"[CTA] [int] NOT NULL," +
"[FAR] [bit] NOT NULL," +
"[HOS] [varchar](1) NOT NULL," +
"[E_HD] [varchar](8) NOT NULL," +
"[C_HD] [varchar](1) NOT NULL," +
"[DIC_SEL] [int] NOT NULL," +
"[SALDO_CALC] [money] NOT NULL," +
"[IDLP] [varchar](1) NOT NULL," +
"[ES_CANJE] [bit] NOT NULL," +
"[CAMBIO_APLICADO] [varchar](50) NOT NULL," +
"[TIPO_CANJE] [bit] NOT NULL," +
"[ORD_COM] [int] NOT NULL," +
"[NATHB] [varchar](1) NOT NULL," +
"CONSTRAINT [PK_ACOBYPAG_VENCIMIENTOS] PRIMARY KEY CLUSTERED" +
"(" +
"[CYV] ASC," +
"[COD] ASC," +
"[FEP] ASC," +
"[TIP] ASC," +
"[TCO] ASC," +
"[SUC] ASC," +
"[NCO] ASC," +
"[NCU] ASC" +
")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]" +
") ON [PRIMARY]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_IPA] DEFAULT ((0)) FOR [IPA]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_SAL] DEFAULT ((0)) FOR [SAL]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_TCA] DEFAULT ((1)) FOR [TCA]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_ZONA] DEFAULT (' ') FOR [ZONA]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_FPA] DEFAULT ((1)) FOR [FPA]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_REC] DEFAULT ((0)) FOR [REC]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_REP] DEFAULT ((0)) FOR [REP]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_REM] DEFAULT ((0)) FOR [REM]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_PRO] DEFAULT ('N') FOR [PRO]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_ANU] DEFAULT (' ') FOR [ANU]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_CCU] DEFAULT ((0)) FOR [CCU]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_UCU] DEFAULT ((0)) FOR [UCU]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_PLA] DEFAULT ((0)) FOR [PLA]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_LUG] DEFAULT ((0)) FOR [LUG]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_PFA] DEFAULT ('N') FOR [PFA]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_RES] DEFAULT ((0)) FOR [RES]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_LEG] DEFAULT ('') FOR [LEG]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_CTA] DEFAULT ((0)) FOR [CTA]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_FAR] DEFAULT ((0)) FOR [FAR]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF__ACOBYPAG_VENCIMIENTOS__HOS__383BDA07] DEFAULT ('') FOR [HOS]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF__ACOBYPAG_VENCIMIENTOS__E_HD__50D27DA7] DEFAULT ('') FOR [E_HD]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF__ACOBYPAG_VENCIMIENTOS__C_HD__51C6A1E0] DEFAULT ('') FOR [C_HD]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [DIC_SEL]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [SALDO_CALC]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ('') FOR [IDLP]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [ES_CANJE]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ('') FOR [CAMBIO_APLICADO]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [TIPO_CANJE]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [ORD_COM]" +
"ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ('') FOR [NATHB]" +
" end"
).raw(
"if OBJECT_ID('[dbo].[VI_ACOBYPAG_AMAEFACT]') is not null "+
"begin " +
"drop view VI_ACOBYPAG_AMAEFACT "+
"end " +
"execute(' " +
"CREATE VIEW [VI_ACOBYPAG_AMAEFACT]" +
"AS " +
"SELECT A.*, A.FEP as FECHA_COMPROBANTE, C.ID_MONEDA AS MONEDA,C.COTIZACION AS COTIZACION," +
"B.COD AS CODIGO_CLIENTE,B.NOM AS NOMBRE_CLIENTE,B.DOM AS DOMICILIO_CLIENTE,B.LOC AS LOCALIDAD,B.PCI AS PROVINCIA,B.CPO AS CODIGO_POSTAL," +
"C.NET AS NETO," +
"C.NEE AS NETO_EXENTO," +
"C.IRI + C.IRS AS IVA," +
"C.IMI + C.IMI2 + C.IMI3 AS IMPUESTOS_INTERNOS," +
"C.PER AS PERCEPCION_IIBB," +
"C.PER_IVA AS PERCECPION_IVA," +
"C.TOT AS TOTAL " +
"FROM ACOBYPAG A " +
"FULL OUTER JOIN AMAEFACT C ON A.CYV = C.CYV AND A.TIP=C.TIP AND A.TCO=C.TCO AND A.SUC=C.SUC AND A.NCO=C.NCO " +
"INNER JOIN CLIENTES B ON A.COD=B.COD"+
" ')"
).raw(
"if OBJECT_ID('[dbo].[VI_ACOBYPAG_VENCIMIENTOS]') is not null "+
"begin " +
"drop view VI_ACOBYPAG_VENCIMIENTOS "+
"end " +
"execute(' " +
"CREATE VIEW [VI_ACOBYPAG_VENCIMIENTOS]" +
"AS " +
"SELECT A.*,B.FEP AS FECHA_COMPROBANTE,C.ID_MONEDA AS MONEDA,C.COTIZACION AS COTIZACION," +
"C.NET AS NETO," +
"C.NEE AS NETO_EXENTO," +
"C.IRI + C.IRS AS IVA," +
"C.IMI + C.IMI2 + C.IMI3 AS IMPUESTOS_INTERNOS," +
"C.PER AS PERCEPCION_IIBB," +
"C.PER_IVA AS PERCECPION_IVA," +
"C.TOT AS TOTAL " +
"FROM ACOBYPAG_VENCIMIENTOS A " +
"INNER JOIN ACOBYPAG B ON A.CYV = B.CYV AND A.TIP=B.TIP AND A.TCO=B.TCO AND A.SUC=B.SUC AND A.NCO=B.NCO " +
"INNER JOIN AMAEFACT C ON A.CYV = C.CYV AND A.TIP=C.TIP AND A.TCO=C.TCO AND A.SUC=C.SUC AND A.NCO=C.NCO" +
" ')"
);
};
exports.down = function(knex) {
return knex.schema
.dropTable('ACOBYPAG_VENCIMIENTOS')
.raw("DROP VIEW [VI_ACOBYPAG_AMAEFACT]")
.raw("DROP VIEW [VI_ACOBYPAG_VENCIMIENTOS]");
};