﻿-----------------------------------------------------------

-- 1) - CREACIÓN DE TABLAS: [VE_PEDIDOS_CAB] Y [VE_PEDIDOS_DET] y sus relaciones.
-- 2) - CREAR EL INICIO DE SESIÓN AnubisWeb, el usuario y establecer los permisos al mismo.

-----------------------------------------------------------

CREATE TABLE [dbo].[VE_PEDIDOS_CAB](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FecComp] [datetime] NOT NULL,
	[Id_Cliente] [int] NOT NULL,
	[Nombre] [nvarchar](50) NOT NULL,
	[Id_TipoIva] [smallint] NOT NULL,
	[SiglaIVA] [nvarchar](4) NULL,
	[DescriIVA] [nvarchar](40) NULL,
	[Cuit] [nvarchar](11) NULL,
	[Direccion] [nvarchar](30) NULL,
	[Localidad] [nvarchar](20) NULL,
	[Telefonos] [nvarchar](50) NULL,
	[ListaPrecios] [nvarchar](1) NULL,
	[ImpNeto1] [money] NOT NULL,
	[ImpNeto2] [money] NOT NULL,
	[ImpNeto3] [money] NOT NULL,
	[ImpNeto4] [money] NULL,
	[PctIVA2] [money] NOT NULL,
	[PctIVA3] [money] NOT NULL,
	[PctIVA4] [money] NOT NULL,
	[ImpIVA2] [money] NOT NULL,
	[ImpIVA3] [money] NOT NULL,
	[ImpIVA4] [money] NULL,
	[PctDescGral1] [money] NOT NULL,
	[ImpDescGral1] [money] NOT NULL,
	[PctDescGral2] [money] NOT NULL,
	[ImpDescGral2] [money] NOT NULL,
	[PctDescGral3] [money] NOT NULL,
	[ImpDescGral3] [money] NOT NULL,
	[ImpTotal] [money] NOT NULL,
	[Observaciones] [nvarchar](255) NULL,
	[Id_Personal] [smallint] NULL,
	[NombrePersonal] [nvarchar](20) NULL,
	[Tipo] [nvarchar](1) NULL,
	[Estado] [nvarchar](1) NULL,
	[Id_Usuario] [smallint] NULL,
	[Usuario] [nvarchar](10) NULL,
	[FecReg] [datetime] NULL,
	[Id_UsuarioUM] [smallint] NULL,
	[UsuarioUM] [nvarchar](10) NULL,
	[FecRegUM] [datetime] NULL,
 CONSTRAINT [VE_PEDIDOS_CAB_PK] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [Id_Cliente]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [Id_TipoIva]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((1)) FOR [ListaPrecios]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpNeto1]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpNeto2]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpNeto3]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpNeto4]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [PctIVA2]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [PctIVA3]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [PctIVA4]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpIVA2]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpIVA3]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpIVA4]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [PctDescGral1]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpDescGral1]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [PctDescGral2]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpDescGral2]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [PctDescGral3]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpDescGral3]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [ImpTotal]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT (N'P') FOR [Tipo]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT (N'A') FOR [Estado]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [Id_Personal]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [Id_Usuario]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_CAB] ADD  DEFAULT ((0)) FOR [Id_UsuarioUM]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'P = Pedido | D = Devolución' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'VE_PEDIDOS_CAB', @level2type=N'COLUMN',@level2name=N'Tipo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A = Pedido o devolución abierta | C = Pedido o devolución cerrada | X = Pedido o devolución exportada' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'VE_PEDIDOS_CAB', @level2type=N'COLUMN',@level2name=N'Estado'
GO

-----------------------------------------------------------

CREATE TABLE [dbo].[VE_PEDIDOS_DET](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Id_Pedido] [int] NULL,
	[Id_Producto] [int] NULL,
	[CodProducto] [nvarchar](20) NULL,
	[ValorSA] [nvarchar](10) NULL,
	[ValorSB] [nvarchar](10) NULL,
	[DescriProd] [nvarchar](255) NULL,
	[ObsItem] [nvarchar](20) NULL,
	[Servicio] [bit] NOT NULL,
	[Id_Marca] [smallint] NULL,
	[Id_Rubro] [smallint] NULL,
	[Id_SubRubro] [smallint] NULL,
	[Id_Grupo] [smallint] NULL,
	[Id_Unidad] [smallint] NULL,
	[Unidad] [nvarchar](6) NULL,
	[UniRep] [real] NULL,
	[Cantidad] [real] NOT NULL,
	[ImpPreUniCompra] [money] NULL,
	[ImpPrecioUni] [money] NOT NULL,
	[PctDesc1] [money] NULL,
	[ImpDesc1] [money] NULL,
	[PctDesc2] [money] NULL,
	[ImpDesc2] [money] NULL,
	[PctDesc3] [money] NULL,
	[ImpDesc3] [money] NULL,
	[ImpTotal] [money] NOT NULL,
	[PctIVA] [money] NULL,
	[CtaContable] [nvarchar](11) NULL,
	[OrdenCarga] [int] NULL,
 CONSTRAINT [VE_PEDIDOS_DET_PK] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [Id_Pedido]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [Id_Producto]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [Servicio]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [Id_Marca]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [Id_Rubro]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [Id_SubRubro]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [Id_Grupo]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [Id_Unidad]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [UniRep]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [Cantidad]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [ImpPreUniCompra]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [ImpPrecioUni]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [PctDesc1]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [ImpDesc1]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [PctDesc2]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [ImpDesc2]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [PctDesc3]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [ImpDesc3]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [ImpTotal]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [PctIVA]
GO

ALTER TABLE [dbo].[VE_PEDIDOS_DET] ADD  DEFAULT ((0)) FOR [OrdenCarga]
GO

-----------------------------------------------------------

ALTER TABLE dbo.VE_PEDIDOS_DET 
ADD CONSTRAINT FK_VE_PEDIDOS_DET_VE_PEDIDOS_CAB	
FOREIGN KEY (Id_Pedido) REFERENCES dbo.VE_PEDIDOS_CAB (Id) ON UPDATE NO ACTION ON DELETE NO ACTION 
GO

ALTER TABLE dbo.VE_PEDIDOS_CAB 
ADD CONSTRAINT FK_VE_PEDIDOS_CAB_VE_CLIENTES 
FOREIGN KEY (Id_Cliente) REFERENCES dbo.VE_CLIENTES (Id_Cliente) ON UPDATE NO ACTION ON DELETE NO ACTION 
GO

-----------------------------------------------------------

CREATE TABLE [dbo].[GEO_REGISTRO](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Latitud] [decimal](9, 6) NOT NULL,
	[Longitud] [decimal](9, 6) NOT NULL,
	[Id_Cliente] [int] NOT NULL,
	[Nombre] [nvarchar](50) NOT NULL,
	[Id_Personal] [smallint] NOT NULL,
	[NombrePersonal] [nvarchar](20) NOT NULL,
	[Id_Usuario] [smallint] NOT NULL,
	[Usuario] [nvarchar](10) NOT NULL,
	[FecReg] [datetime] NOT NULL,
	[Id_Motivo] [int] NOT NULL,
	[Notas] [nvarchar](255) NULL,
 CONSTRAINT [PK_GEO_REGISTRO] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GEO_REGISTRO] ADD  CONSTRAINT [DF_GEO_REGISTRO_Id_Cliente]  DEFAULT ((0)) FOR [Id_Cliente]
GO

ALTER TABLE [dbo].[GEO_REGISTRO] ADD  CONSTRAINT [DF_GEO_REGISTRO_Id_Personal]  DEFAULT ((0)) FOR [Id_Personal]
GO

ALTER TABLE [dbo].[GEO_REGISTRO] ADD  CONSTRAINT [DF_GEO_REGISTRO_Id_Usuario]  DEFAULT ((0)) FOR [Id_Usuario]
GO

ALTER TABLE [dbo].[GEO_REGISTRO] ADD  CONSTRAINT [DF_GEO_REGISTRO_Id_Motivo]  DEFAULT ((0)) FOR [Id_Motivo]
GO

-----------------------------------------------------------

CREATE TABLE [dbo].[GEO_MOTIVOS](
	[Id] [int] IDENTITY(0,1) NOT NULL,
	[Nombre] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_GEO_MOTIVOS] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

-----------------------------------------------------------

CREATE LOGIN AnubisWeb WITH PASSWORD = 'Anubis550BV'
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'AnubisWeb')
BEGIN
    CREATE USER [AnubisWeb] FOR LOGIN [AnubisWeb]
    EXEC sp_addrolemember N'db_datareader', N'AnubisWeb'
END;
GO

GRANT INSERT, UPDATE, DELETE ON VE_PEDIDOS_CAB to AnubisWeb
GRANT INSERT, UPDATE, DELETE ON VE_PEDIDOS_DET to AnubisWeb
GRANT INSERT, UPDATE, DELETE ON GEO_REGISTRO to AnubisWeb
GRANT INSERT, UPDATE, DELETE ON GEO_MOTIVOS to AnubisWeb

ALTER TABLE VE_PEDIDOS_CAB
ADD Latitud  DECIMAL(9,6) NULL,
    Longitud DECIMAL(9,6) NULL,
    GeoFecha DATETIME NULL;

-----------------------------------------------------------

ALTER TABLE dbo.AR_USUARIOS ADD	TableroControl bit NOT NULL DEFAULT 0

-----------------------------------------------------------