Vfrdtyky

create database BDSeguros
go

use database BDSeguros
go

create schema reg
go

create schema seg
go

create schema ope
go

create table reg.Tablas
(
cIdTabla char(4) not null primary key,
cDescripcion varchar(80) not null,
cAbrev varchar(15),
cValor varchar(10)
)
go

create table reg.Agencia
(
cIdAgencia char(3) not null primary key,
cAgencia varchar(50) not null unique,
cDireccion varchar(80),
dFechaReg date not null default getdate()
)
go

create table reg.Parametros
(
cIdParametro varchar(15) not null primary key,
cParametro varchar(50) not null unique,
cValor varchar(10)
)
go

--GalPue 11/11/2018 Tabla de Clientes, debería haber tabla de persona, persona natural y persona juridica, y enlazar
-- al cliente con la tabla de personas
create table reg.Cliente
(
cIdCliente char(7) not null primary key,
cIdTipoDocu char(4) not null foreign key references reg.Tablas(cIdTabla),
cNroDocu varchar(12) not null unique,
cApePat varchar(45) not null,
cApeMat varchar(45),
cNombres varchar(50) not null,
dFechaNac date not null,
cSexo char(1) not null,
cIdEstCivil char(4) not null foreign key references reg.Tablas(cIdTabla),
cDireccion varchar(80),
cTelefono varchar(15),
dFechaIngreso date not null default getdate(),
dFechaReg date not null default getdate(),
cHoraReg varchar(8) not null default convert(varchar(8),getdate(),108),
cIdAgencia char(3) not null foreign key references reg.Agencia(cIdAgencia),
cIdUser char(6) --Id del usuario que registra, debería haber una tabla de usuarios, pero por tiempo se obvia
)
go

create table seg.Aseguradora
(
cIdAseguradora char(3) not null primary key,
cRazonSoc varchar(50) not null,
cNroRUC char(11) not null unique,
cDireccion varchar(80),
cTelefono varchar(15),
cActivo char(1) not null default '1'
)
go

create table seg.TipoSeguro
(
iIdTipoSeg int identity(1,1) primary key,
cTipoSeguro varchar(20)
)
go

create table seg.Seguro
(
cIdSeguro char(3) not null primary key,
cIdAseguradora char(3) not null foreign key references seg.Aseguradora(cIdAseguradora),
iIdTipoSeg int not null foreign key references seg.TipoSeguro(iIdTipoSeg),
nFactorImp decimal(4,2) not null,
nComision decimal(4,2) not null default 0.00,
nPrima decimal(5,2) not null,
cTipMoneda char(1) not null default '0', --Soles(0) y Dólares(1)
iEdadMax int not null,
cEstado char(1) not null default '1',--Activo(1) e Inactivo(0)
cTipoImp char(1) not null,--Porcentaje(P) y Monetario(M)
nImpMen decimal(5,2) not null,
nCobertura decimal(9,2) not null,
dFechaVig date not null default getdate(),
dFechaReg date not null default getdate(),
cHoraReg varchar(8) not null default convert(varchar(8),getdate(),108),
cIdUser char(6) not null
)
go

create table seg.Afiliacion
(
cNroPoliza char(10) not null primary key,
cIdCliente char(7) not null foreign key references reg.Cliente(cIdCliente),
cIdSeguro char(3) not null foreign key references seg.Seguro(cIdSeguro),
dFechaReg date not null default getdate(),
cHoraReg varchar(8) not null default convert(varchar(8),getdate(),108),
cIdUser char(6) not null
)
go

create table seg.Cuota
(
cNroPoliza char(10) not null foreign key references seg.Afiliacion(cNroPoliza),
iNroCrono int not null, --Número de cronograma
iNroCuota int not null,
nMonto decimal(5,2) not null,
dFechaVen date not null,
cEstado char(1) not null default '0', --Pagado(1) y No Pagado(0)
cActivo char(1) not null default '1', --Activo(1) e Inactivo(0)
constraint PK_CUOTA primary key (cNroPoliza,iNroCrono,iNroCuota)
)
go

create table ope.Voucher
(
cNroVoucher char(10) not null primary key,
cEstado char(1) not null default '1', --Registrado(1) y Anulado(0)
dFechaEst date not null default getdate(),
dFechaReg date not null default getdate(),
cHoraReg varchar(8) not null default convert(varchar(8),getdate(),108),
cIdUser char(6) not null
)
go

create table ope.Pago
(
cNroPago char(10) not null primary key,
cNroVoucher char(10) not null foreign key references ope.Voucher(cNroVoucher),
cTipMoneda char(1) not null default '0', --Soles(0) y Dólares(1)
nMonto decimal(10,2) not null,
cGlosa varchar(200),
cIdAgencia char(3) not null foreign key references reg.Agencia(cIdAgencia),
dFechaReg date not null default getdate(),
cHoraReg varchar(8) not null default convert(varchar(8),getdate(),108),
cIdUser char(6) not null
)
go

create table seg.CtrlCuotas
(
cNroPoliza char(10) not null,
iNroCrono int not null, --Número de cronograma
iNroCuota int not null,
cNroPago char(10) not null foreign key references ope.Pago(cNroPago),
dFecha date not null default getdate(),
constraint FK_CUOTA_CTRLCUOTAS foreign key (cNroPoliza,iNroCrono,iNroCuota) references seg.Cuota(cNroPoliza,iNroCrono,iNroCuota),
constraint PK_CTRLCUOTAS primary key (cNroPoliza,iNroCrono,iNroCuota,cNroPago)
)
go

insert into reg.Tablas (cIdTabla,cDescripcion,cAbrev,cValor) values
('0100','TIPOS DE DOCUMENTO DE INDENTIFICACION','TipoDoc',''),
('0101','DNI','DNI',''),
('0102','PARTIDA DE NACIMIENTO','P. NAC',''),
('0103','PASAPORTE','PASS','')
go

insert into reg.Tablas (cIdTabla,cDescripcion,cAbrev,cValor) values
('0200','ESTADOS CIVILES','EstCiv',''),
('0201','SOLTERO(A)','SOLT',''),
('0202','CASADO(A)','CAS',''),
('0203','VIUDO(A)','VIU',''),
('0204','DIVORCIADO(A)','DIVOR','')
go

insert into reg.Parametros(cIdParametro,cParametro,cValor) values
('IdCliente','Identificador de Clientes','0000000')
go

insert into reg.Agencia(cIdAgencia,cAgencia,cDireccion,dFechaReg) values
('001','OFICINA PRINCIPAL','Plaza de Armas',GETDATE())
go

create procedure reg.SpReg_ListarTabla (@cIdTabla char(4))
as
begin
set nocount on

select cIdTabla, cDescripcion from reg.Tablas where cIdTabla like concat(left(@cIdTabla,2),'%') and cIdTabla <> @cIdTabla

end
go

create procedure reg.SpReg_DevSecuenciaParametro (@cIdParametro varchar(15), @iTam int)
as
begin
set nocount on

select 'Valor' = right(concat('0000000000',cast(cValor as int) + 1), @iTam) from reg.Parametros where cIdParametro = @cIdParametro

end
go

create procedure reg.SpReg_GuardarSecuenciaParametro (@cIdParametro varchar(15), @cValor varchar(10))
as
begin
update reg.Parametros set cValor=@cValor where cIdParametro = @cIdParametro
end
go

create procedure reg.SpReg_RegistrarCliente
(
@cIdCliente char(7)
,@cIdTipoDocu char(4)
,@cNroDocu varchar(12)
,@cApePat varchar(45)
,@cApeMat varchar(45)
,@cNombres varchar(50)
,@dFechaNac date
,@cSexo char(1)
,@cIdEstCivil char(4)
,@cDireccion varchar(80)
,@cTelefono varchar(15)
,@cIdAgencia char(3)
,@cIdUser char(6)
)
as
begin
insert into reg.Cliente
(cIdCliente
,cIdTipoDocu

,cNroDocu

,cApePat

,cApeMat

,cNombres

,dFechaNac

,cSexo

,cIdEstCivil

,cDireccion
,cTelefono

,dFechaIngreso

,dFechaReg

,cHoraReg

,cIdAgencia
,cIdUser)
values
(@cIdCliente

,@cIdTipoDocu

,@cNroDocu

,@cApePat

,@cApeMat

,@cNombres

,@dFechaNac
,@cSexo
,@cIdEstCivil

,@cDireccion

,@cTelefono
,getdate()

,getdate()
,CONVERT(varchar(8),getdate(),108)

,@cIdAgencia

,@cIdUser)

end
go