forked from Saul1023/SistemaFactura
-
Notifications
You must be signed in to change notification settings - Fork 0
/
CREACION_BASE_DE_DATOS.sql
98 lines (90 loc) · 7.06 KB
/
CREACION_BASE_DE_DATOS.sql
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
-- Script de creacion de base de datos y login correspondiente.
-- Se puede ejecutar todo el script sin intervencion.
-- BORRARA LA BASE DE DATOS LLAMADA 'sistema_factura'
USE master;
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'sistema_factura')
BEGIN
USE master;
ALTER DATABASE sistema_factura SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE sistema_factura;
END
GO
CREATE DATABASE sistema_factura;
GO
USE sistema_factura;
CREATE TABLE usuario(
nit_ci BIGINT PRIMARY KEY NOT NULL,
nombre VARCHAR(50),
);
GO
CREATE TABLE factura(
id_factura INT IDENTITY (1,1) PRIMARY KEY NOT NULL,
nit_usuario BIGINT NOT NULL,
nombre_razon VARCHAR(100), -- Probablemente no se llene
nit_emisor BIGINT NOT NULL,
numero_factura INT NOT NULL,
cod_autorizacion VARCHAR(70)NOT NULL,
fecha_emision DATE NOT NULL,
monto MONEY NOT NULL,
monto_imponible MONEY NOT NULL,
cod_control VARCHAR(30), -- Probablemente no tiene
tipo_especifico BIT NOT NULL,
tipo_general BIT NOT NULL,
FOREIGN KEY (nit_usuario) REFERENCES usuario(nit_ci),
);
GO
-- Creacion del usuario para login correspondiente a la cadena de conexion
USE master;
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'factura_admin')
BEGIN
DROP LOGIN factura_admin;
END
GO
CREATE LOGIN factura_admin WITH PASSWORD = 'admin123', CHECK_POLICY = OFF;
GO
USE sistema_factura;
DROP USER IF EXISTS factura_admin;
GO
CREATE USER factura_admin FOR LOGIN factura_admin;
GO
ALTER USER factura_admin WITH DEFAULT_SCHEMA = [dbo];
ALTER ROLE [db_owner] ADD MEMBER factura_admin;
GO
-- Insercion de usuario y valores de demostracion
INSERT INTO usuario VALUES (10101010, 'JEELM');
INSERT INTO factura VALUES (10101010, 'Empresa1', 395674228903, 2742, 'HJU98FGH289HF9H9F23890HD', '2023-12-20', 8941.82, 8941.82, 'F3-9C-1A-4E-2G-8D-6R-7P', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa2', 214421740047, 1525, '1YFN70YF7SDY0W9Y89023Y89', '2024-03-22', 3638.69, 3638.69, 'G9-2W-5E-3R-8T-1H-6S-4F', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa3', 860361920849, 3327, '178Y9D784Y89032Y789YD8903Y29203YNAZ', '2023-12-27', 3860.81, 3860.81, 'N8-3D-5F-2G-1R-9T-7Y-6U', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa4', 192245872095, 1254, '1C7FN0V74JG0VM8', '2024-04-04', 6674.39, 6674.39, 'D4-9A-6S-2D-5F-1G-3H-7J', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa5', 923066136724, 7785, '901UNN7893YUD89Y2389DH23NYD237890YD', '2023-11-17', 1048.15, 1048.15, 'R8-7F-3D-1G-9H-4J-2K-5L', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa6', 325318944745, 6230, '1289YND0Y1YD891Y20', '2023-09-29', 8584.96, 8584.96, 'D2-4F-6G-1H-3J-5K-9L-7Z', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa7', 115726434477, 2120, '89U3HNEY789YQW709Y', '2024-01-16', 6163.85, 6163.85, 'A9-4S-7D-1F-5G-6H-2J-8K', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa8', 311249078610, 7931, '1Y2D890Y0S9YD9Y990', '2023-06-29', 9826.42, 9826.42, 'C5-1D-3F-7G-9H-2J-4K-8L', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa9', 370070736490, 9902, '1Z9M28EY12378XYEN130129YNZE061R126B', '2023-09-27', 7510.61, 7510.61, 'R2-3W-7E-1R-9T-5Y-8U-6I', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa10', 933858310875, 7855, 'Z12TN67F4V2TI90M290JIE891G71290014', '2023-05-05', 2183.34, 2183.34, 'F7-3D-1F-5G-9H-6J-2K-8L', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa11', 248528036105, 1951, 'JH78N247NSD0FYNN934YNSD706FF07D700', '2024-02-22', 7293.55, 7293.55, 'W4-1E-3R-7T-9Y-5U-8I-2O', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa12', 708788263593, 4225, '9072893N74890723890757925Y7Y70VNNC', '2023-12-22', 9542.72, 9542.72, 'U3-4I-9O-7P-1A-6S-2D-8F', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa13', 335724994918, 3012, '69820XMUE98YVNY0NT534895YN89NY998W', '2023-08-26', 4425.49, 4425.49, 'H2-6A-4S-8D-5F-9G-1H-3J', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa14', 140253112344, 5677, 'Y8923C4B780Y37280Y4B7823BH47Y74Y4Y', '2023-12-10', 7997.41, 7997.41, 'N3-6A-9S-4D-8F-2G-7H-1J', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa15', 867273815022, 4113, 'Cod-2T-6Y-4U', '2024-01-28', 6601.53, 6601.53, 'F5-2D-7F-3G-9H-1J-8K-4L', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa16', 981917964647, 1918, 'Cod-6Y-1U-8I', '2024-02-26', 1230.69, 1230.69, 'C7-3F-8G-2H-9J-5K-1L-4Z', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa17', 297027860459, 2210, 'Cod-3U-7I-2O', '2023-10-29', 2049.74, 2049.74, 'U2-4A-8S-1D-5F-7G-3H-9J', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa18', 177693917500, 5472, 'Cod-7I-1O-9P-3A-5S-2D', '2023-09-11', 6075.01, 6075.01, 'L9-6U-2I-3O-7P-8A-1S-4D', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa19', 918707116398, 9754, 'Cod-1O-6P-2A-7S-4D-8F', '2023-06-29', 6010.12, 6010.12, 'O2-4P-6A-8S-3D-1F-7G-9H', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa20', 669565376741, 2943, 'Cod-9P-3A-5S', '2023-11-16', 9413.07, 9413.07, 'I5-7O-1P-3A-8S-4D-9F-6G', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa21', 442661043116, 6072, 'Cod-5A-2S-7D', '2023-08-11', 5059.36, 5059.36, 'T2-4Y-9U-5I-1O-7P-3A-8S', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa22', 359360815363, 3553, 'Cod-4S-9D-6F', '2023-05-27', 7396.56, 7396.56, 'E9-2R-6T-8Y-4U-7I-1O-3P', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa23', 426614785299, 6238, 'Cod-1D-6F-2G-9H-7J-5K', '2023-10-13', 6213.08, 6213.08, 'P2-4W-8E-7R-1T-5Y-6U-9I', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa24', 779953843999, 3324, 'Cod-2F-9G-4H', '2023-10-21', 2007.24, 2007.24, 'Z1-8D-4F-6G-2H-9J-5K-7L', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa25', 328963432596, 8916, 'Cod-7G-3H-1J-5K', '2023-07-27', 6386.94, 6386.94, 'A2-4W-6E-8R-3T-1Y-5U-7I', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa26', 791037337698, 7710, 'Cod-9H-4J-7K', '2023-07-06', 9508.17, 9508.17, 'X6-2Z-4X-7C-1V-3B-9N-5M', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa27', 986051065017, 4422, 'Cod-5J-9K-2L', '2023-07-18', 9386.19, 9386.19, 'H2-4G-6H-8J-3K-5L-9Z-1X', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa28', 404572950624, 2482, 'Cod-9K-4L-1Z-5X-2C-6V', '2023-09-15', 8950.26, 8950.26, 'G3-6H-1J-4K-9L-5Z-7X-2C', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa29', 303037409261, 8513, 'Cod-3L-8Z-1X', '2023-06-09', 5809.63, 5809.63, 'S2-4D-8F-1G-6H-3J-7K-5L', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa30', 324348047064, 4841, 'Cod-6Z-1X-4C', '2023-05-22', 4204.75, 4204.75, 'N3-8S-1D-4F-6G-9H-7J-5K', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa31', 771837894116, 8394, 'Cod-9X-5C-3V', '2023-06-21', 5108.68, 5108.68, 'O2-4P-6A-8S-3D-1F-7G-9H', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa32', 726561474298, 1890, 'Cod-1C-9V-4B', '2023-05-20', 8658.12, 8658.12, 'L9-6U-2I-3O-7P-8A-1S-4D', 1, 0);
INSERT INTO factura VALUES (10101010, 'Empresa33', 123456789012, 8962, 'Cod-9V-5B-1N-6M-2Z-7X', '2024-01-31', 6362.19, 6362.19, 'I5-7O-1P-3A-8S-4D-9F-6G', 0, 1);
INSERT INTO factura VALUES (10101010, 'Empresa34', 425180352891, 7347, 'Cod-2B-8N-4M', '2023-07-13', 9275.16, 9275.16, 'T2-4Y-9U-5I-1O-7P-3A-8S', 0, 1);
GO
--EFVF