/****** Object:  Table [dbo].[GeoFence] ******/
/****** For mainting Fence records ******/

CREATE TABLE [dbo].[GeoFence](
	[FenceId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Lat] [varchar](50) NOT NULL,
	[Lng] [varchar](50) NOT NULL
) ON [PRIMARY]

--Changing autoincrement for Geofence since the id can clash with Reader Table
DBCC CHECKIDENT ('GeoFence', RESEED, 1000)


/****** Object:  Table [dbo].[GeoTag] ******/
/****** Manual Location updates ******/

CREATE TABLE [dbo].[GeoTag](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Empid] [int] NOT NULL,
	[LocationName] [varchar](300) NULL,
	[Lat] [varchar](50) NOT NULL,
	[Lng] [varchar](50) NOT NULL,
	[INOUT] [char](1) NULL,
	[CreatedDate] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]



/****** Object:  StoredProcedure [dbo].[InsertGeoTag]******/
/****** Inserting New Records in GeoTag Table******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[InsertGeoTag] (@CARD INT,@LOCATIONNAME VARCHAR(300),@LAT VARCHAR(50),@LNG VARCHAR(50),@INOUT CHAR(1))

AS
BEGIN
	INSERT INTO [dbo].[GeoTag]([Empid],[LocationName],[Lat],[Lng],[INOUT],[CreatedDate])VALUES(@CARD,@LOCATIONNAME,@LAT,@LNG,@INOUT,getdate())
		RETURN 
	END
GO


/****** New Property to enable GeoFencing and GeoTagging under Employee Master******/
ALTER TABLE employee ADD EnableGeoFence bit NULL DEFAULT ((0))
ALTER TABLE employee ADD EnableGeoTag bit NULL DEFAULT ((0))