CREATE TABLE [dbo].[City](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	CONSTRAINT [PK_City] 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]
) 
GO


CREATE TABLE [dbo].[Area](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](500) NOT NULL,
	[City] [int] NOT NULL
 CONSTRAINT [PK_Area] 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]
) 
GO

ALTER TABLE [dbo].[Area]  WITH CHECK ADD  CONSTRAINT [FK_Area] FOREIGN KEY([City])
REFERENCES [dbo].[City] ([ID])
GO

ALTER TABLE [dbo].[Area] CHECK CONSTRAINT [FK_Area]
GO


CREATE TABLE [dbo].[EVENT](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](500) NOT NULL,
	[Description] [varchar](max) NOT NULL,
	[EventImage] [varchar](100) NOT NULL,
	[Area] [int] NOT NULL,
	[Created_Date] [datetime] NOT NULL,
	[Modified_Date] [datetime] NOT NULL,
	[Deleted_Date] [datetime] NULL,
	[IsDeleted] [bit] NULL
	
 CONSTRAINT [PK_Event] 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]
) 
GO

ALTER TABLE [dbo].[EVENT]  WITH CHECK ADD  CONSTRAINT [FK_Event] FOREIGN KEY([Area])
REFERENCES [dbo].[Area] ([ID])
GO

ALTER TABLE [dbo].[EVENT] CHECK CONSTRAINT [FK_Event]
GO




/****** Object:  StoredProcedure [dbo].[EVENT_CRUD]    Script Date: 10/3/2020 2:16:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:		<AUTHOR,,NAME>
-- CREATE DATE: <CREATE DATE,,>
-- DESCRIPTION:	<DESCRIPTION,,>
-- =============================================
CREATE PROCEDURE [dbo].[EVENT_CRUD] 
	-- ADD THE PARAMETERS FOR THE STORED PROCEDURE HERE
	@ID INT = 0, 
	@TYPE INT = 0, --0 SELECT 1 ADD 2 EDIT 3 DELETE 
	@NAME VARCHAR(500) = '',
	@DESCRIPTION VARCHAR(MAX) = '',
	@EventImage VARCHAR(100) = '',
	@AREA INT = 0
AS
BEGIN
	-- SET NOCOUNT ON ADDED TO PREVENT EXTRA RESULT SETS FROM
	-- INTERFERING WITH SELECT STATEMENTS.
	SET NOCOUNT ON;

    IF (@TYPE = 0 AND @ID=0)
	SELECT EVENT.*,AREA.Name AS AREANAME,CITY.Name AS CITYNAME FROM
	EVENT INNER JOIN AREA ON AREA.ID=EVENT.AREA 
	INNER JOIN CITY ON CITY.ID=AREA.CITY
	WHERE EVENT.ISDELETED = 0 ORDER BY EVENT.MODIFIED_DATE DESC;

	IF (@TYPE = 0 AND @ID  > 0)
	SELECT EVENT.*,AREA.Name AS AREANAME,CITY.Name AS CITYNAME,CITY.ID AS CITYID FROM
	EVENT INNER JOIN AREA ON AREA.ID=EVENT.AREA 
	INNER JOIN CITY ON CITY.ID=AREA.CITY WHERE EVENT.ID = @ID;


	IF (@TYPE = 1 AND @NAME != '' AND @DESCRIPTION != '' AND @EventImage != '' AND @AREA > 0)
	INSERT INTO EVENT VALUES(@NAME, @DESCRIPTION,@EventImage,@AREA,GETDATE(),GETDATE(),GETDATE(),0);

	IF(@TYPE = 2 AND  @ID > 0 AND @NAME != '' AND @DESCRIPTION != '' AND @EventImage != '' AND @AREA > 0 )
	UPDATE EVENT SET NAME=@NAME, DESCRIPTION=@DESCRIPTION ,EventImage=@EventImage,AREA =@AREA, MODIFIED_DATE=GETDATE() WHERE ID=@ID;

	IF(@TYPE = 3 AND  @ID > 0)
	UPDATE EVENT SET DELETED_DATE=GETDATE(), ISDELETED=1 WHERE ID=@ID;
	
END


INSERT INTO CITY values ('Mumbai');
INSERT INTO CITY values ('Pune');
INSERT INTO CITY values ('Nagpur');

INSERT INTO Area values ('Bandra',1);
INSERT INTO Area values ('Byculla',1);
INSERT INTO Area values ('Ambegaon',2);
INSERT INTO Area values ('Baner',2);
INSERT INTO Area values ('Mahal',3);