USE [master]
GO
/****** Object:  Database [SMSEngine]    Script Date: 23/06/2021 14:59:44 ******/
CREATE DATABASE [SMSEngine] ON  PRIMARY 
( NAME = N'SMSEngine', FILENAME = N'D:\z-BACKUP\MSSQL10 2008\SMSEngine.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SMSEngine_log', FILENAME = N'D:\z-BACKUP\MSSQL10 2008\SMSEngine_log.ldf' , SIZE = 5184KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [SMSEngine] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SMSEngine].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [SMSEngine] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [SMSEngine] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [SMSEngine] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [SMSEngine] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [SMSEngine] SET ARITHABORT OFF 
GO
ALTER DATABASE [SMSEngine] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [SMSEngine] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [SMSEngine] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [SMSEngine] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [SMSEngine] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [SMSEngine] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [SMSEngine] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [SMSEngine] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [SMSEngine] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [SMSEngine] SET  DISABLE_BROKER 
GO
ALTER DATABASE [SMSEngine] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [SMSEngine] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [SMSEngine] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [SMSEngine] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [SMSEngine] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [SMSEngine] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [SMSEngine] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [SMSEngine] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [SMSEngine] SET  MULTI_USER 
GO
ALTER DATABASE [SMSEngine] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [SMSEngine] SET DB_CHAINING OFF 
GO
EXEC sys.sp_db_vardecimal_storage_format N'SMSEngine', N'ON'
GO
USE [SMSEngine]
GO
/****** Object:  Table [dbo].[config]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[config](
	[id] [nchar](20) NOT NULL,
	[valor] [nvarchar](500) NULL,
 CONSTRAINT [PK_config] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[EnviosProgramados]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EnviosProgramados](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[descripcion] [nvarchar](150) NULL,
	[fecha] [datetime] NULL,
	[usuario] [nvarchar](50) NULL,
	[habilitado] [bit] NULL,
 CONSTRAINT [PK_EnviosProgramados] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[lista_numeros]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[lista_numeros](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[idlista] [int] NULL,
	[telefono] [nvarchar](50) NULL,
	[fecha] [datetime] NULL,
 CONSTRAINT [PK_lista_numeros] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[listas]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[listas](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nombre] [nvarchar](250) NULL,
	[idusuario] [int] NULL,
	[fecha] [date] NULL,
	[tipo] [nvarchar](50) NULL,
 CONSTRAINT [PK_listas] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[logCargadeDatos]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[logCargadeDatos](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[fecha] [datetime] NULL,
	[tipo] [nvarchar](50) NULL,
	[mensajes] [int] NULL,
	[usuario] [nvarchar](50) NULL,
	[comentarios] [nvarchar](500) NULL,
	[fechaCuotas] [datetime] NULL,
	[tiporecordatorio] [int] NULL,
	[cuotas] [int] NULL,
 CONSTRAINT [PK_logCargasAutomaticas] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[messages]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[messages](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[referenceid] [nvarchar](50) NULL,
	[status] [nvarchar](20) NULL,
	[channel] [nvarchar](20) NULL,
	[sent] [datetime] NULL,
	[received] [datetime] NULL,
	[scheduled] [datetime] NULL,
	[lastupdate] [datetime] NULL,
	[sender] [nvarchar](20) NULL,
	[recepient] [nvarchar](20) NULL,
	[body] [nvarchar](320) NULL,
	[isalert] [bit] NULL,
	[result] [nvarchar](250) NULL,
	[type] [nvarchar](50) NULL,
	[idcarga] [int] NULL,
	[idenvio] [int] NULL,
	[priority] [smallint] NULL,
	[codigo] [bigint] NULL,
	[messagekey] [nvarchar](50) NULL,
	[queued] [datetime] NULL,
	[delivered] [datetime] NULL,
	[usuario] [nvarchar](50) NULL,
 CONSTRAINT [PK_messages] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[messagesIn]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[messagesIn](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[channel] [nvarchar](20) NULL,
	[lastupdated] [datetime] NULL,
	[type] [nvarchar](20) NULL,
	[sender] [nvarchar](20) NULL,
	[body] [nvarchar](320) NULL,
 CONSTRAINT [PK_messagesIn] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[parametros]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[parametros](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[parametro] [nvarchar](50) NULL,
	[valor] [nvarchar](50) NULL,
	[orden] [smallint] NULL,
	[habilitado] [bit] NULL,
 CONSTRAINT [PK_parametros] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[plantillas]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[plantillas](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[texto] [nvarchar](160) NULL,
	[fecha] [datetime] NULL,
	[tipo] [nvarchar](50) NULL,
	[idusuario] [int] NULL,
 CONSTRAINT [PK_plantillas] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[SMS_limpio]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SMS_limpio](
	[telefono] [varchar](50) NULL,
	[mensaje] [varchar](50) NULL,
	[fechaenvio] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tarjetasCargadas]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tarjetasCargadas](
	[id] [int] NOT NULL,
	[fecha] [datetime] NULL,
	[credito] [decimal](18, 0) NULL,
	[costo] [decimal](18, 0) NULL,
	[idultimomensaje] [int] NULL,
 CONSTRAINT [PK_tarjetasCargadas] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[temp_messages]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[temp_messages](
	[referenceid] [nvarchar](50) NULL,
	[status] [nvarchar](20) NULL,
	[channel] [nvarchar](20) NULL,
	[sent] [datetime] NULL,
	[received] [datetime] NULL,
	[scheduled] [datetime] NULL,
	[lastupdate] [datetime] NULL,
	[sender] [nvarchar](20) NULL,
	[recepient] [nvarchar](20) NULL,
	[body] [nvarchar](320) NULL,
	[isalert] [bit] NULL,
	[result] [nvarchar](250) NULL,
	[type] [nvarchar](50) NULL,
	[idcarga] [int] NULL,
	[idenvio] [int] NULL,
	[priority] [smallint] NULL,
	[codigo] [bigint] NULL,
	[messagekey] [nvarchar](50) NULL,
	[queued] [datetime] NULL,
	[delivered] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[usuarios]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[lista_numeros] ADD  CONSTRAINT [DF_lista_numeros_fecha]  DEFAULT (getdate()) FOR [fecha]
GO
ALTER TABLE [dbo].[listas] ADD  CONSTRAINT [DF_listas_fecha]  DEFAULT (getdate()) FOR [fecha]
GO
ALTER TABLE [dbo].[logCargadeDatos] ADD  CONSTRAINT [DF_logCargasAutomaticas_fecha]  DEFAULT (getdate()) FOR [fecha]
GO
ALTER TABLE [dbo].[messages] ADD  CONSTRAINT [DF_messages_lastupdate]  DEFAULT (getdate()) FOR [lastupdate]
GO
ALTER TABLE [dbo].[messages] ADD  CONSTRAINT [DF_messages_priority]  DEFAULT ((2)) FOR [priority]
GO
ALTER TABLE [dbo].[messagesIn] ADD  CONSTRAINT [DF_messagesIn_lastupdated]  DEFAULT (getdate()) FOR [lastupdated]
GO
ALTER TABLE [dbo].[plantillas] ADD  CONSTRAINT [DF_plantillas_fecha]  DEFAULT (getdate()) FOR [fecha]
GO
ALTER TABLE [dbo].[plantillas] ADD  CONSTRAINT [DF_plantillas_tipo]  DEFAULT (N'CSV') FOR [tipo]
GO
ALTER TABLE [dbo].[tarjetasCargadas] ADD  CONSTRAINT [DF_tarjetasCargadas_fecha]  DEFAULT (getdate()) FOR [fecha]
GO
ALTER TABLE [dbo].[usuarios] ADD  CONSTRAINT [DF_usuarios_registrado]  DEFAULT (getdate()) FOR [registrado]
GO
ALTER TABLE [dbo].[usuarios] ADD  CONSTRAINT [DF_usuarios_mod_operador]  DEFAULT ((0)) FOR [modulos]
GO
/****** Object:  StoredProcedure [dbo].[dashboardContadores]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[dashboardContadores]
@totalFechas float out,
@totalProcesando float out,
@totalEnviados float out
as 
DECLARE @fechaini date
declare @fechafin date

set @fechaini = cast(getdate() as date)
set @fechafin= dateadd(DAY, 1,cast(getdate() as date))
set @totalFechas=(select count(*) as total from messages where scheduled between @fechaini and @fechafin)

set @totalProcesando= (select count(*) as TotalProcesando
from messages  where status in ('PENDIENTE','EN COLA','ENVIANDO') and scheduled between @fechaini and @fechafin)

set @totalEnviados= (select count(*) as totalEnviados
from messages  where status in ('ENVIADO','ERROR') and sent between @fechaini and @fechafin)
GO
/****** Object:  StoredProcedure [dbo].[dashboardGrafTotalesxFecha]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[dashboardGrafTotalesxFecha]
@fechaini date,
@fechafin date,
@totalFechas float out,
@totalProcesando float out,
@totalEnviados float out
as 
set @fechaini = cast(@fechaini as date)
set @fechafin= dateadd(DAY, 1,cast(@fechafin as date))

select top 10 cast(scheduled as date) as fecha, count(*) as totalFecha
from messages where scheduled between @fechaini and @fechafin group by cast(scheduled as date) order by cast(scheduled as date) desc
GO
/****** Object:  StoredProcedure [dbo].[spCancelarMensaje]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spCancelarMensaje] 
	-- Add the parameters for the stored procedure here
	@rowscount int out,
	@errorcode int out,
	@id as int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

UPDATE [messages] 
SET status='CANCELADO', lastupdate=GETDATE() WHERE id= @id;
select @rowscount=@@ROWCOUNT, @errorcode = @@ERROR
END
GO
/****** Object:  StoredProcedure [dbo].[spCargarMensajes]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spCargarMensajes] 
  @dia datetime
AS
BEGIN
	SET NOCOUNT ON;

-- CAMPOS Y ALIAS OBLIGATORIOS: FECHA, TELEFONO, MENSAJE
-- ejemplo: Campos de la tabla prestamos.cuotas
-- nombre, fechapago, numerodeuda,montocuota, moneda, numerocuota, 
	select fechaenvio as FECHA, telefono as TELEFONO, mensaje as MENSAJE
	from prestamos.dbo.cuotas 
	where fechaenvio = @dia
END
GO
/****** Object:  StoredProcedure [dbo].[spTraerCuotas]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spTraerCuotas] 
  @dia nvarchar(10)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
-- nombre, fechapago, numerodeuda,montocuota, moneda, numerocuota, telefono1, telefono2, telefono3
	select nombre as nombre, vencimiento as fechapago,0 as numerodeuda,monto as montocuota, nrocuota as numerocuota, moneda as moneda, telefono as telefono1, '' as telefono2, '' as telefono3
	from prestamos.dbo.cuotas 
	where vencimiento = @dia

END
GO
/****** Object:  StoredProcedure [dbo].[spWsEliminarMensaje]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spWsEliminarMensaje]
	-- Add the parameters for the stored procedure here
	@rowscount int out,
	@errorcode int out,
	@id as int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT off;
	  
DELETE FROM [messages]
      WHERE id= @id;
select @rowscount=@@ROWCOUNT, @errorcode = @@ERROR
END
GO
/****** Object:  StoredProcedure [dbo].[spWsEnviarMensaje]    Script Date: 23/06/2021 14:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spWsEnviarMensaje]
	-- Add the parameters for the stored procedure here
	@fecha_envio date,
	@telefono nvarchar(10),
	@mensaje nvarchar(250),
	@prioridad smallint,
	@rowscount int out,
	@errorcode int out,
	@codigo bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

 insert into messages(status,scheduled,recepient,body, type, priority,codigo) 
values ('PENDIENTE', @fecha_envio, @telefono ,@mensaje , 'WEBSERVICE', @prioridad,@codigo);
select @rowscount=@@ROWCOUNT, @errorcode = @@ERROR
END
GO
USE [master]
GO
ALTER DATABASE [SMSEngine] SET  READ_WRITE 
GO
