--Create database

USE [master]
GO
IF NOT EXISTS (SELECT [name] FROM sys.databases WHERE name = N'payroll')
BEGIN
CREATE DATABASE [payroll] COLLATE SQL_Latin1_General_CP1_CI_AS
END
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'payroll', @new_cmptlevel=100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
	EXEC [payroll].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [payroll] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [payroll] SET ANSI_NULLS OFF
GO
ALTER DATABASE [payroll] SET ANSI_PADDING OFF
GO
ALTER DATABASE [payroll] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [payroll] SET ARITHABORT OFF
GO
ALTER DATABASE [payroll] SET AUTO_CLOSE ON
GO
ALTER DATABASE [payroll] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [payroll] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [payroll] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [payroll] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [payroll] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [payroll] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [payroll] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [payroll] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [payroll] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [payroll] SET DISABLE_BROKER
GO
ALTER DATABASE [payroll] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [payroll] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [payroll] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [payroll] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [payroll] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [payroll] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [payroll] SET READ_WRITE
GO
ALTER DATABASE [payroll] SET RECOVERY SIMPLE
GO
ALTER DATABASE [payroll] SET MULTI_USER
GO
ALTER DATABASE [payroll] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [payroll] SET DB_CHAINING OFF
GO

USE [payroll]
GO

--Table dbo.emp

USE [payroll]
GO

--Create table and its columns
CREATE TABLE [dbo].[emp] (
	[pkempslno] [int] NOT NULL IDENTITY (1, 1),
	[slno] [varchar](30) NULL,
	[emplidno] [varchar](20) NOT NULL,
	[emplfname] [varchar](50) NULL,
	[empllname] [varchar](50) NULL,
	[username] [varchar](50) NULL,
	[passwrd] [varchar](50) NULL,
	[emplemailid] [varchar](50) NULL,
	[status] [int] NULL,
	[deletedby] [varchar](30) NULL,
	[createddate] [datetime] NULL,
	[modifieddate] [datetime] NULL,
	[deleteddate] [datetime] NULL,
	[gender] [varchar](10) NULL,
	[address] [varchar](200) NULL,
	[landlineno] [varchar](20) NULL,
	[mobileno] [varchar](20) NULL,
	[bloodgroup] [varchar](10) NULL,
	[education] [varchar](20) NULL,
	[yearpass] [varchar](20) NULL,
	[dateofjoin] [datetime] NULL,
	[currsal] [varchar](20) NULL,
	[reasonfordelete] [varchar](200) NULL,
	[emplofficialid] [varchar](50) NULL,
	[userstatus] [varchar](20) NULL,
	[accountno] [varchar](50) NULL,
	[esi] [varchar](50) NULL,
	[pf] [varchar](50) NULL,
	[location] [varchar](50) NULL,
	[grade] [varchar](50) NULL,
	[esinumber] [varchar](50) NULL,
	[bankname] [varchar](50) NULL,
	[pfaccountno] [varchar](50) NULL,
	[pannumber] [varchar](50) NULL,
	[leftdate] [datetime] NULL,
	[annualgross] [varchar](50) NULL,
	[annualctc] [varchar](50) NULL,
	[fathername] [varchar](50) NULL,
	[designation] [varchar](50) NULL,
	[usergroup] [varchar](50) NULL,
	[per] [varchar](50) NULL);
GO

SET IDENTITY_INSERT [dbo].[emp] ON
GO
INSERT INTO [dbo].[emp] ([pkempslno], [slno], [emplidno], [emplfname], [empllname], [username], [passwrd], [emplemailid], [status], [deletedby], [createddate], [modifieddate], [deleteddate], [gender], [address], [landlineno], [mobileno], [bloodgroup], [education], [yearpass], [dateofjoin], [currsal], [reasonfordelete], [emplofficialid], [userstatus], [accountno], [esi], [pf], [location], [grade], [esinumber], [bankname], [pfaccountno], [pannumber], [leftdate], [annualgross], [annualctc], [fathername], [designation], [usergroup], [per])
	VALUES (1, N'0', N'Admin001', N'Devaraj', N'T N', N'admin@fuoco.in', N'admin', N'devabe2005@gmail.com', 1, NULL, CAST(0x0000a1bc006c7d70 AS datetime), CAST(0x0000a1d000685ec0 AS datetime), NULL, N'Male', N'fdsss', N'4355453ss', N'88888s', N'dsfsadfdss', N'dsfs', N'34434', CAST(0x0000a1cc00000000 AS datetime), N'40000', NULL, N'jeba', N'UnBlock', N'', N'0', N'1', N'', N'', N'', N'', N'', N'', NULL, N'288000', N'288000', N'', N'Admin', N'Admin', NULL)

GO
INSERT INTO [dbo].[emp] ([pkempslno], [slno], [emplidno], [emplfname], [empllname], [username], [passwrd], [emplemailid], [status], [deletedby], [createddate], [modifieddate], [deleteddate], [gender], [address], [landlineno], [mobileno], [bloodgroup], [education], [yearpass], [dateofjoin], [currsal], [reasonfordelete], [emplofficialid], [userstatus], [accountno], [esi], [pf], [location], [grade], [esinumber], [bankname], [pfaccountno], [pannumber], [leftdate], [annualgross], [annualctc], [fathername], [designation], [usergroup], [per])
	VALUES (2, N'1', N'Fuoco1', N'Devaraj', N'T N', N'devaraj@fuoco.in', N'devaraj', N'devabe2005@gmail.com', 1, NULL, CAST(0x0000a1bc006c7d70 AS datetime), CAST(0x0000a1de00c5b548 AS datetime), NULL, N'Male', N'perambur', N'4355453', N'88888', N'dsfsadfdss', N'B.E', N'34434', CAST(0x0000a1cc00000000 AS datetime), N'40000', NULL, N'jeba', N'UnBlock', N'dsfdf', N'0', N'1', N'India', N'D', N'df', N'dfd', N'df', N'dfd', CAST(0xffff2e4600000000 AS datetime), N'360000', N'360000', N'dsff', N'Senior Software Engineer', N'General', N'True')

GO
INSERT INTO [dbo].[emp] ([pkempslno], [slno], [emplidno], [emplfname], [empllname], [username], [passwrd], [emplemailid], [status], [deletedby], [createddate], [modifieddate], [deleteddate], [gender], [address], [landlineno], [mobileno], [bloodgroup], [education], [yearpass], [dateofjoin], [currsal], [reasonfordelete], [emplofficialid], [userstatus], [accountno], [esi], [pf], [location], [grade], [esinumber], [bankname], [pfaccountno], [pannumber], [leftdate], [annualgross], [annualctc], [fathername], [designation], [usergroup], [per])
	VALUES (5, N'2', N'Fuoco2', N'Jeba', N'singh', N'jeba@fuoco.in', N'jebas', N'devabe2005@gmail.com', 1, NULL, CAST(0x0000a1d700ae2220 AS datetime), NULL, NULL, N'', N'fdsss', N'4355453ss', N'88888s', N'dsfsadfdss', N'dsfs', N'34434', CAST(0x0000a1e200000000 AS datetime), N'15000', NULL, N'jeba', N'UnBlock', N'', N'1', N'1', N'', N'', N'', N'', N'', N'', NULL, N'180000', N'180000', N'', N'Software Engineer', N'General', NULL)

GO
INSERT INTO [dbo].[emp] ([pkempslno], [slno], [emplidno], [emplfname], [empllname], [username], [passwrd], [emplemailid], [status], [deletedby], [createddate], [modifieddate], [deleteddate], [gender], [address], [landlineno], [mobileno], [bloodgroup], [education], [yearpass], [dateofjoin], [currsal], [reasonfordelete], [emplofficialid], [userstatus], [accountno], [esi], [pf], [location], [grade], [esinumber], [bankname], [pfaccountno], [pannumber], [leftdate], [annualgross], [annualctc], [fathername], [designation], [usergroup], [per])
	VALUES (6, N'3', N'Fuoco3', N'Abdul', N'Azees', N'abdul@fuoco.in', N'abdul', N'devabe2005@gmail.com', 1, NULL, CAST(0x0000a1d800c22ef0 AS datetime), NULL, NULL, N'', N'fdsss', N'4355453ss', N'88888s', N'dsfsadfdss', N'dsfs', N'34434', CAST(0x0000a1da00000000 AS datetime), N'10000', NULL, N'Abdul', N'UnBlock', N'', N'1', N'1', N'', N'', N'', N'', N'', N'', NULL, N'120000', N'120000', N'', N'software engineer', N'General', NULL)

GO
INSERT INTO [dbo].[emp] ([pkempslno], [slno], [emplidno], [emplfname], [empllname], [username], [passwrd], [emplemailid], [status], [deletedby], [createddate], [modifieddate], [deleteddate], [gender], [address], [landlineno], [mobileno], [bloodgroup], [education], [yearpass], [dateofjoin], [currsal], [reasonfordelete], [emplofficialid], [userstatus], [accountno], [esi], [pf], [location], [grade], [esinumber], [bankname], [pfaccountno], [pannumber], [leftdate], [annualgross], [annualctc], [fathername], [designation], [usergroup], [per])
	VALUES (7, N'4', N'Fuoco4', N'Ela', N'chezhian', N'ela@fuoco.in', N'elas', N'devabe2005@gmail.com', 1, NULL, CAST(0x0000a1d800d07730 AS datetime), NULL, NULL, N'Male', N'fdsss', N'4355453ss', N'88888s', N'dsfsadfdss', N'dsfs', N'34434', CAST(0x0000a1db00000000 AS datetime), N'20000', NULL, N'ela', N'UnBlock', N'233433', N'0', N'1', N'Chennai', N'Male', N'234343', N'HDFC', N'33442', N'343343', NULL, N'240000', N'240000', N'DSDSFDS', N'Software Engineer', N'General', NULL)

GO
INSERT INTO [dbo].[emp] ([pkempslno], [slno], [emplidno], [emplfname], [empllname], [username], [passwrd], [emplemailid], [status], [deletedby], [createddate], [modifieddate], [deleteddate], [gender], [address], [landlineno], [mobileno], [bloodgroup], [education], [yearpass], [dateofjoin], [currsal], [reasonfordelete], [emplofficialid], [userstatus], [accountno], [esi], [pf], [location], [grade], [esinumber], [bankname], [pfaccountno], [pannumber], [leftdate], [annualgross], [annualctc], [fathername], [designation], [usergroup], [per])
	VALUES (11, N'5', N'Fuoco5', N'arfath', N'a', N'arfath@fuoco.in', N'arfath', N'', 2, NULL, CAST(0x0000a1de00bad164 AS datetime), CAST(0x0000a1de00baf810 AS datetime), NULL, N'', N'', N'', N'', N'', N'', N'', CAST(0x0000a1de00000000 AS datetime), N'10000', NULL, N'arfath', N'UnBlock', N'', N'1', N'1', N'', N'', N'', N'', N'', N'', CAST(0xffff2e4600000000 AS datetime), N'120000', N'120000', N'', N'Software Engineer', N'General', N'True')

GO
SET IDENTITY_INSERT [dbo].[emp] OFF
GO

--Table dbo.salary

USE [payroll]
GO

--Create table and its columns
CREATE TABLE [dbo].[salary] (
	[pksalaryslno] [int] NOT NULL IDENTITY (1, 1),
	[fkemplidno] [varchar](50) NULL,
	[totaldays] [varchar](50) NULL,
	[noofdayspresent] [varchar](50) NULL,
	[basicsalary] [varchar](50) NULL,
	[hra] [varchar](50) NULL,
	[lossofpay] [varchar](50) NULL,
	[gross_sal] [varchar](50) NULL,
	[esiec] [varchar](50) NULL,
	[esicc] [varchar](50) NULL,
	[pfec] [varchar](50) NULL,
	[pfcc] [varchar](50) NULL,
	[advance] [varchar](50) NULL,
	[otherded] [varchar](50) NULL,
	[netsal] [varchar](50) NULL,
	[month] [varchar](50) NULL,
	[year] [varchar](50) NULL,
	[conveyance] [varchar](50) NULL,
	[medreimburse] [varchar](50) NULL,
	[empcontrib] [varchar](50) NULL,
	[lta] [varchar](50) NULL,
	[foodreimburse] [varchar](50) NULL,
	[spallowance] [varchar](50) NULL,
	[tax] [varchar](50) NULL,
	[incentive] [varchar](50) NULL,
	[bonus] [varchar](50) NULL,
	[variablepay] [varchar](50) NULL,
	[foodcoupon] [varchar](50) NULL,
	[refreshment] [varchar](50) NULL,
	[penalty] [varchar](50) NULL,
	[esiempcontrib] [varchar](50) NULL,
	[gross_sala] [varchar](50) NULL,
	[otherdedreason] [varchar](50) NULL,
	[incentivereason] [varchar](50) NULL,
	[penaltyreason] [varchar](50) NULL);
GO

SET IDENTITY_INSERT [dbo].[salary] ON
GO
INSERT INTO [dbo].[salary] ([pksalaryslno], [fkemplidno], [totaldays], [noofdayspresent], [basicsalary], [hra], [lossofpay], [gross_sal], [esiec], [esicc], [pfec], [pfcc], [advance], [otherded], [netsal], [month], [year], [conveyance], [medreimburse], [empcontrib], [lta], [foodreimburse], [spallowance], [tax], [incentive], [bonus], [variablepay], [foodcoupon], [refreshment], [penalty], [esiempcontrib], [gross_sala], [otherdedreason], [incentivereason], [penaltyreason])
	VALUES (1, N'Fuoco2', N'30', N'30', N'10500.00', N'1650.00', N'0.00', N'15000.00', N'262.50', N'712.50', N'113.75', N'308.75', N'0.00', N'0.00', N'13419.00', N'06', N'2013', N'0.00', N'0.00', N'884.65', N'525.00', N'0.00', N'727.85', N'183.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'712.50', N'15000.00', N'', N'', N'')

GO
INSERT INTO [dbo].[salary] ([pksalaryslno], [fkemplidno], [totaldays], [noofdayspresent], [basicsalary], [hra], [lossofpay], [gross_sal], [esiec], [esicc], [pfec], [pfcc], [advance], [otherded], [netsal], [month], [year], [conveyance], [medreimburse], [empcontrib], [lta], [foodreimburse], [spallowance], [tax], [incentive], [bonus], [variablepay], [foodcoupon], [refreshment], [penalty], [esiempcontrib], [gross_sala], [otherdedreason], [incentivereason], [penaltyreason])
	VALUES (11, N'Fuoco1', N'30', N'25', N'23333.33', N'3666.67', N'6666.67', N'34334.00', N'0.00', N'0.00', N'113.75', N'308.75', N'100.00', N'0.00', N'33628.00', N'06', N'2013', N'800.00', N'1250.00', N'884.65', N'1166.67', N'1250.00', N'982.01', N'183.00', N'1000.76', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'41001.00', N'', N'dffddf', N'')

GO
INSERT INTO [dbo].[salary] ([pksalaryslno], [fkemplidno], [totaldays], [noofdayspresent], [basicsalary], [hra], [lossofpay], [gross_sal], [esiec], [esicc], [pfec], [pfcc], [advance], [otherded], [netsal], [month], [year], [conveyance], [medreimburse], [empcontrib], [lta], [foodreimburse], [spallowance], [tax], [incentive], [bonus], [variablepay], [foodcoupon], [refreshment], [penalty], [esiempcontrib], [gross_sala], [otherdedreason], [incentivereason], [penaltyreason])
	VALUES (13, N'Fuoco3', N'30', N'25', N'5833.33', N'916.67', N'1666.67', N'8333.00', N'145.83', N'395.83', N'102.08', N'277.08', N'0.00', N'0.00', N'7327.00', N'06', N'2013', N'0.00', N'0.00', N'793.92', N'291.67', N'0.00', N'101.91', N'85.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'395.83', N'10000.00', N'', N'', N'')

GO
INSERT INTO [dbo].[salary] ([pksalaryslno], [fkemplidno], [totaldays], [noofdayspresent], [basicsalary], [hra], [lossofpay], [gross_sal], [esiec], [esicc], [pfec], [pfcc], [advance], [otherded], [netsal], [month], [year], [conveyance], [medreimburse], [empcontrib], [lta], [foodreimburse], [spallowance], [tax], [incentive], [bonus], [variablepay], [foodcoupon], [refreshment], [penalty], [esiempcontrib], [gross_sala], [otherdedreason], [incentivereason], [penaltyreason])
	VALUES (14, N'Fuoco3', N'31', N'0', N'0.00', N'0.00', N'0', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'07', N'2013', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', NULL, NULL, NULL)

GO
INSERT INTO [dbo].[salary] ([pksalaryslno], [fkemplidno], [totaldays], [noofdayspresent], [basicsalary], [hra], [lossofpay], [gross_sal], [esiec], [esicc], [pfec], [pfcc], [advance], [otherded], [netsal], [month], [year], [conveyance], [medreimburse], [empcontrib], [lta], [foodreimburse], [spallowance], [tax], [incentive], [bonus], [variablepay], [foodcoupon], [refreshment], [penalty], [esiempcontrib], [gross_sala], [otherdedreason], [incentivereason], [penaltyreason])
	VALUES (15, N'Fuoco3', N'31', N'0', N'0.00', N'0.00', N'0', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'08', N'2013', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', NULL, NULL, NULL)

GO
INSERT INTO [dbo].[salary] ([pksalaryslno], [fkemplidno], [totaldays], [noofdayspresent], [basicsalary], [hra], [lossofpay], [gross_sal], [esiec], [esicc], [pfec], [pfcc], [advance], [otherded], [netsal], [month], [year], [conveyance], [medreimburse], [empcontrib], [lta], [foodreimburse], [spallowance], [tax], [incentive], [bonus], [variablepay], [foodcoupon], [refreshment], [penalty], [esiempcontrib], [gross_sala], [otherdedreason], [incentivereason], [penaltyreason])
	VALUES (16, N'Fuoco4', N'30', N'25', N'11666.67', N'1833.33', N'3333.33', N'17667.00', N'0.00', N'0.00', N'113.75', N'308.75', N'0.00', N'0.00', N'17061.00', N'06', N'2013', N'800.00', N'0.00', N'884.65', N'583.33', N'0.00', N'898.69', N'183.00', N'1000.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'21000.00', N'', N'pd', N'')

GO
INSERT INTO [dbo].[salary] ([pksalaryslno], [fkemplidno], [totaldays], [noofdayspresent], [basicsalary], [hra], [lossofpay], [gross_sal], [esiec], [esicc], [pfec], [pfcc], [advance], [otherded], [netsal], [month], [year], [conveyance], [medreimburse], [empcontrib], [lta], [foodreimburse], [spallowance], [tax], [incentive], [bonus], [variablepay], [foodcoupon], [refreshment], [penalty], [esiempcontrib], [gross_sala], [otherdedreason], [incentivereason], [penaltyreason])
	VALUES (18, N'Fuoco3', N'31', N'0', N'0.00', N'0.00', N'0', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'05', N'2013', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', NULL, NULL, NULL)

GO
INSERT INTO [dbo].[salary] ([pksalaryslno], [fkemplidno], [totaldays], [noofdayspresent], [basicsalary], [hra], [lossofpay], [gross_sal], [esiec], [esicc], [pfec], [pfcc], [advance], [otherded], [netsal], [month], [year], [conveyance], [medreimburse], [empcontrib], [lta], [foodreimburse], [spallowance], [tax], [incentive], [bonus], [variablepay], [foodcoupon], [refreshment], [penalty], [esiempcontrib], [gross_sala], [otherdedreason], [incentivereason], [penaltyreason])
	VALUES (19, N'Fuoco4', N'30', N'0', N'0.00', N'0.00', N'0', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'04', N'2013', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', NULL, NULL, NULL)

GO
INSERT INTO [dbo].[salary] ([pksalaryslno], [fkemplidno], [totaldays], [noofdayspresent], [basicsalary], [hra], [lossofpay], [gross_sal], [esiec], [esicc], [pfec], [pfcc], [advance], [otherded], [netsal], [month], [year], [conveyance], [medreimburse], [empcontrib], [lta], [foodreimburse], [spallowance], [tax], [incentive], [bonus], [variablepay], [foodcoupon], [refreshment], [penalty], [esiempcontrib], [gross_sala], [otherdedreason], [incentivereason], [penaltyreason])
	VALUES (20, N'Fuoco4', N'30', N'0', N'0.00', N'0.00', N'0', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'04', N'2012', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', N'0.00', NULL, NULL, NULL)

GO
SET IDENTITY_INSERT [dbo].[salary] OFF
GO

--Table dbo.salset

USE [payroll]
GO

--Create table and its columns
CREATE TABLE [dbo].[salset] (
	[pksalsetslno] [int] NOT NULL IDENTITY (1, 1),
	[basic] [varchar](50) NULL,
	[hra] [varchar](50) NULL,
	[conveyance] [varchar](50) NULL,
	[empcontribgreaterthanorequal] [varchar](50) NULL,
	[empcontribgreaterthanvalue] [varchar](50) NULL,
	[empcontriblessthanvaluebasic] [varchar](50) NULL,
	[medicalreimbursements] [varchar](50) NULL,
	[foodreimbursements] [varchar](50) NULL,
	[spall] [varchar](50) NULL,
	[esiempeecon] [varchar](50) NULL,
	[esiemprcon] [varchar](50) NULL,
	[pfempeecon] [varchar](50) NULL,
	[pfemprcon] [varchar](50) NULL,
	[lta] [varchar](50) NULL);
GO

SET IDENTITY_INSERT [dbo].[salset] ON
GO
INSERT INTO [dbo].[salset] ([pksalsetslno], [basic], [hra], [conveyance], [empcontribgreaterthanorequal], [empcontribgreaterthanvalue], [empcontriblessthanvaluebasic], [medicalreimbursements], [foodreimbursements], [spall], [esiempeecon], [esiemprcon], [pfempeecon], [pfemprcon], [lta])
	VALUES (3, N'70', N'11', N'800', N'6500', N'13.61', N'13.61', N'1250', N'1250', N'250', N'1.75', N'4.75', N'1.75', N'4.75', N'20')

GO
SET IDENTITY_INSERT [dbo].[salset] OFF
GO

--Table dbo.taxset

USE [payroll]
GO

--Create table and its columns
CREATE TABLE [dbo].[taxset] (
	[pktaxsetslno] [int] NOT NULL IDENTITY (1, 1),
	[tfrom] [varchar](50) NULL,
	[tto] [varchar](50) NULL,
	[tax] [varchar](50) NULL,
	[status] [int] NULL,
	[createdby] [varchar](50) NULL,
	[modifiedby] [varchar](50) NULL,
	[deletedby] [varchar](50) NULL,
	[reasonfordelete] [varchar](50) NULL,
	[createddate] [datetime] NULL,
	[modifieddate] [datetime] NULL,
	[deleteddate] [datetime] NULL);
GO

SET IDENTITY_INSERT [dbo].[taxset] ON
GO
INSERT INTO [dbo].[taxset] ([pktaxsetslno], [tfrom], [tto], [tax], [status], [createdby], [modifiedby], [deletedby], [reasonfordelete], [createddate], [modifieddate], [deleteddate])
	VALUES (1, N'0', N'3500', N'0', 1, N'devaraj@fuoco.in', NULL, NULL, NULL, CAST(0x0000a1bc017fbe48 AS datetime), NULL, NULL)

GO
INSERT INTO [dbo].[taxset] ([pktaxsetslno], [tfrom], [tto], [tax], [status], [createdby], [modifiedby], [deletedby], [reasonfordelete], [createddate], [modifieddate], [deleteddate])
	VALUES (2, N'3500', N'5000', N'17', 1, N'devaraj@fuoco.in', NULL, NULL, NULL, CAST(0x0000a1bc017fbe48 AS datetime), NULL, NULL)

GO
INSERT INTO [dbo].[taxset] ([pktaxsetslno], [tfrom], [tto], [tax], [status], [createdby], [modifiedby], [deletedby], [reasonfordelete], [createddate], [modifieddate], [deleteddate])
	VALUES (3, N'5000', N'7000', N'39', 1, N'devaraj@fuoco.in', NULL, NULL, NULL, CAST(0x0000a1bc017fbe48 AS datetime), NULL, NULL)

GO
INSERT INTO [dbo].[taxset] ([pktaxsetslno], [tfrom], [tto], [tax], [status], [createdby], [modifiedby], [deletedby], [reasonfordelete], [createddate], [modifieddate], [deleteddate])
	VALUES (4, N'7000', N'10000', N'85', 1, N'devaraj@fuoco.in', NULL, NULL, NULL, CAST(0x0000a1bc017fbe48 AS datetime), NULL, NULL)

GO
INSERT INTO [dbo].[taxset] ([pktaxsetslno], [tfrom], [tto], [tax], [status], [createdby], [modifiedby], [deletedby], [reasonfordelete], [createddate], [modifieddate], [deleteddate])
	VALUES (5, N'10000', N'12500', N'127', 1, N'devaraj@fuoco.in', NULL, N'admin@fuoco.in', N'not', CAST(0x0000a1bc017fbe48 AS datetime), NULL, CAST(0x0000a1d1008d34d4 AS datetime))

GO
INSERT INTO [dbo].[taxset] ([pktaxsetslno], [tfrom], [tto], [tax], [status], [createdby], [modifiedby], [deletedby], [reasonfordelete], [createddate], [modifieddate], [deleteddate])
	VALUES (6, N'12500', N'above', N'183', 1, N'devaraj@fuoco.in', N'admin@fuoco.in', N'admin@fuoco.in', N'not', CAST(0x0000a1bc017fbe48 AS datetime), CAST(0x0000a1d1008d2214 AS datetime), CAST(0x0000a1d1008d2b74 AS datetime))

GO
INSERT INTO [dbo].[taxset] ([pktaxsetslno], [tfrom], [tto], [tax], [status], [createdby], [modifiedby], [deletedby], [reasonfordelete], [createddate], [modifieddate], [deleteddate])
	VALUES (12, N'34434345', N'3243345', N'2343345', 2, N'admin@fuoco.in', N'admin@fuoco.in', N'admin@fuoco.in', N'not required', CAST(0x0000a1d800d192c8 AS datetime), CAST(0x0000a1d800d1a588 AS datetime), CAST(0x0000a1d800d1b26c AS datetime))

GO
SET IDENTITY_INSERT [dbo].[taxset] OFF
GO

--Table dbo.tblentery

USE [payroll]
GO

--Create table and its columns
CREATE TABLE [dbo].[tblentery] (
	[sno] [int] NOT NULL IDENTITY (1, 1),
	[taskid] [varchar](50) NULL,
	[softwareengineer] [varchar](50) NULL,
	[bug] [varchar](50) NULL,
	[priority] [varchar](50) NULL,
	[projectname] [varchar](500) NULL,
	[department] [varchar](50) NULL,
	[status] [varchar](50) NULL,
	[projecttype] [varchar](50) NULL,
	[projectdescription] [varchar](5000) NULL);
GO

SET IDENTITY_INSERT [dbo].[tblentery] ON
GO
INSERT INTO [dbo].[tblentery] ([sno], [taskid], [softwareengineer], [bug], [priority], [projectname], [department], [status], [projecttype], [projectdescription])
	VALUES (2, N'T2', N'Ela', N'Medium', N'Normal', N'payroll1', N'Software Development', N'Complete', N'Manuel', N'slipt')

GO
SET IDENTITY_INSERT [dbo].[tblentery] OFF
GO

--Executing Entities
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[verifypassword]
	(
	@username varchar(50),
	@password varchar(50)
	)
	
AS
	Select * From emp WHERE ( username =@username) AND (passwrd =@password) AND (status = '1') AND (userstatus ='UnBlock')
	RETURN

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[checkusername]
	(
	@username varchar(100)
	)
	
AS
	Select * From emp WHERE ( username = @username) AND (status = '1')
	RETURN

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[adduser]
	(
	    @slno varchar(100),
        @emplidno varchar(100),
        @emplfname varchar(100),
        @empllname varchar(100),
        @username varchar(100),
        @passwrd varchar(100),
        @emplemailid varchar(100),
        @status varchar(100),
        @createddate varchar(100),
        @gender varchar(100),
        @address varchar(100),
        @landlineno varchar(100),
        @mobileno varchar(100),
        @bloodgroup varchar(100),
        @education varchar(100),
        @yearpass varchar(100),
        @dateofjoin varchar(100),
        @currsal varchar(100),
        @emplofficialid varchar(100),
        @userstatus varchar(100),
        @accountno varchar(100),
        @esi varchar(100),
        @pf varchar(100),
        @location varchar(100),
        @grade varchar(100),
        @esinumber varchar(100),
        @bankname varchar(100),
        @pfaccountno varchar(100),
        @pannumber varchar(100),
        @annualgross varchar(100),
        @annualctc varchar(100),
        @fathername varchar(100),
        @designation varchar(100),
        @usergroup varchar(100)

	)
	
AS
	insert into emp (slno,emplidno,emplfname,empllname,username,passwrd,emplemailid,status,createddate,gender,address,landlineno,mobileno,bloodgroup,education,yearpass,dateofjoin,currsal,emplofficialid,userstatus,accountno,esi,pf,location,grade,esinumber,bankname,pfaccountno,pannumber,annualgross,annualctc,fathername, designation,usergroup)values(@slno,@emplidno,@emplfname,@empllname,@username,@passwrd,@emplemailid,@status,@createddate,@gender,@address,@landlineno,@mobileno,@bloodgroup,@education,@yearpass,@dateofjoin,@currsal,@emplofficialid,@userstatus,@accountno,@esi,@pf,@location,@grade,@esinumber,@bankname,@pfaccountno,@pannumber,@annualgross,@annualctc,@fathername, @designation,@usergroup)
	RETURN

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[retrieveemplist]
AS
select * from emp where (status = '1') AND (userstatus = 'UnBlock') and (usergroup = 'General')
RETURN
GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
                CREATE PROCEDURE [dbo].[retrieveemplidno]
                (
                @emplidno varchar(100)
                )

            AS
                Select * From emp WHERE ( emplidno = @emplidno) AND (status = '1') 
                RETURN
GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
            CREATE PROCEDURE [dbo].[retrievesalaryfromfkemplidno_month_year]
            (
            @fkemplidno varchar(100),
            @month varchar(100),
            @year varchar(100)
            )
            AS
            Select * from salary where fkemplidno=@fkemplidno and month=@month and year=@year
            RETURN
GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
           CREATE PROCEDURE [dbo].[addsalary]
           (
            @fkemplidno varchar(50),
            @totaldays varchar(50),
            @noofdayspresent varchar(50),
            @basicsalary  varchar(50),
            @hra varchar(50),
            @lossofpay varchar(50),
            @gross_sal varchar(50),
            @esiec varchar(50),
            @esicc varchar(50),
            @pfec varchar(50),
            @pfcc varchar(50),
            @advance varchar(50),
            @otherded varchar(50),
            @netsal varchar(50),
            @month varchar(50),
            @year varchar(50),
            @conveyance varchar(50),
            @medreimburse varchar(50),
            @empcontrib varchar(50),
            @lta varchar(50),
            @foodreimburse varchar(50),
            @spallowance varchar(50),
            @tax varchar(50),
            @incentive varchar(50),
            @bonus varchar(50),
            @variablepay varchar(50),
            @foodcoupon varchar(50),
            @refreshment varchar(50),
            @penalty varchar(50),
            @esiempcontrib varchar(50),
            @gross_sala varchar(50)
        )
	
    AS
        insert into salary (fkemplidno,totaldays,noofdayspresent,basicsalary,hra,lossofpay,gross_sal,esiec,esicc,pfec,pfcc,advance,otherded,netsal,
        month,year,conveyance,medreimburse, empcontrib,lta, foodreimburse,spallowance,tax,incentive,bonus,variablepay,foodcoupon,refreshment,penalty,esiempcontrib,
        gross_sala) values (@fkemplidno,@totaldays,@noofdayspresent,@basicsalary,@hra,@lossofpay,@gross_sal,@esiec,@esicc,@pfec,@pfcc,@advance,@otherded,@netsal,
        @month,@year,@conveyance,@medreimburse,@empcontrib,@lta,@foodreimburse,@spallowance,@tax,@incentive,@bonus,@variablepay,@foodcoupon,@refreshment,@penalty,
        @esiempcontrib,@gross_sala)
        RETURN
GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
        CREATE PROCEDURE [dbo].[deleteuser]
        (
        @emplidno varchar(100)
        )
        AS
        update emp set status='2' where emplidno=@emplidno
        RETURN
GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
                CREATE PROCEDURE [dbo].[editdisplay]
            (
            @emplidno varchar(100)
            )

        AS
            Select * From emp WHERE ( emplidno = @emplidno) AND (status = '1') 
            RETURN

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
                CREATE PROCEDURE [dbo].[updateuser]
                (
                    
                    @emplidno varchar(100),
                    @emplfname varchar(100),
                    @empllname varchar(100),
                    @username varchar(100),
                    @passwrd varchar(100),
                    @emplemailid varchar(100),
                    @status varchar(100),
                    @modifieddate varchar(100),
                    @gender varchar(100),
                    @address varchar(100),
                    @landlineno varchar(100),
                    @mobileno varchar(100),
                    @bloodgroup varchar(100),
                    @education varchar(100),
                    @yearpass varchar(100),
                    @dateofjoin varchar(100),
                    @currsal varchar(100),
                    @emplofficialid varchar(100),
                    @userstatus varchar(100),
                    @accountno varchar(100),
                    @esi varchar(100),
                    @pf varchar(100),
                    @location varchar(100),
                    @grade varchar(100),
                    @esinumber varchar(100),
                    @bankname varchar(100),
                    @pfaccountno varchar(100),
                    @pannumber varchar(100),
                    @annualgross varchar(100),
                    @annualctc varchar(100),
                    @fathername varchar(100),
                    @designation varchar(100),
                    @usergroup varchar(100),
					@leftdate varchar(100)
                )

            AS
                update emp set emplfname=@emplfname, empllname=@empllname, emplemailid=@emplemailid, gender=@gender, address=@address, landlineno=@landlineno,mobileno=@mobileno,   bloodgroup=@bloodgroup, education=@education, yearpass=@yearpass, dateofjoin=@dateofjoin,currsal=@currsal,emplofficialid=@emplofficialid, accountno=@accountno,esi=@esi, pf=@pf, location=@location, grade=@grade, esinumber=@esinumber, bankname=@bankname, pfaccountno=@pfaccountno, pannumber=@pannumber,annualgross=@annualgross, annualctc=@annualctc,fathername=@fathername,designation=@designation,modifieddate=@modifieddate,leftdate=@leftdate,userstatus=@userstatus  where emplidno=@emplidno            RETURN
GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[retrieveTableListTaxSettings]
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
AS
	select * from taxset
	RETURN

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[retrieveFrompktaxsetslno]
	(
	@pktaxsetslno varchar(100)
	)
AS
	select * from taxset where pktaxsetslno=@pktaxsetslno
	RETURN

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
            CREATE PROCEDURE [dbo].[updatesalary]
            (
                @fkemplidno varchar(50),
                @totaldays varchar(50),
                @noofdayspresent varchar(50),
                @basicsalary  varchar(50),
                @hra varchar(50),
                @lossofpay varchar(50),
                @gross_sal varchar(50),
                @esiec varchar(50),
                @esicc varchar(50),
                @pfec varchar(50),
                @pfcc varchar(50),
                @advance varchar(50),
                @otherded varchar(50),
                @netsal varchar(50),
                @month varchar(50),
                @year varchar(50),
                @conveyance varchar(50),
                @medreimburse varchar(50),
                @empcontrib varchar(50),
                @lta varchar(50),
                @foodreimburse varchar(50),
                @tax varchar(50),
                @incentive varchar(50),
                @bonus varchar(50),
                @variablepay varchar(50),
                @foodcoupon varchar(50),
                @refreshment varchar(50),
                @penalty varchar(50),
                @esiempcontrib varchar(50),
                @gross_sala varchar(50),
                @otherdedreason varchar(800),
                @incentivereason varchar(800),
                @penaltyreason varchar(800)
            )
	
        AS
            update salary set totaldays=@totaldays,noofdayspresent=@noofdayspresent,basicsalary=@basicsalary,hra=@hra,lossofpay=@lossofpay,
            gross_sal=@gross_sal,esiec=@esiec,esicc=@esicc,pfec=@pfec,pfcc=@pfcc,advance=@advance,otherded=@otherded,netsal=@netsal,
            month=@month,year=@year,conveyance=@conveyance,medreimburse=@medreimburse,empcontrib=@empcontrib,lta=@lta,foodreimburse=@foodreimburse,tax=@tax,
            incentive=@incentive,bonus=@bonus,variablepay=@variablepay,foodcoupon=@foodcoupon,refreshment=@refreshment,penalty=@penalty,esiempcontrib=@esiempcontrib,
                gross_sala=@gross_sala, otherdedreason=@otherdedreason, incentivereason=@incentivereason,penaltyreason=@penaltyreason where fkemplidno=@fkemplidno
GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 create PROCEDURE [dbo].[updatepassword]
                (
                    @emplidno varchar(100),
                    @passwrd varchar(100)
                )
                AS
                update emp set passwrd=@passwrd where emplidno=@emplidno
                RETURN
GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
                create PROCEDURE [dbo].[updategeneral]
                (
            
                    @emplfname varchar(100),
                    @empllname varchar(100),
                    @address varchar(100),
                    @landlineno varchar(100),
                    @mobileno varchar(100),
                    @bloodgroup varchar(100),
                    @education varchar(100),
                    @yearpass varchar(100),
                    @emplemailid varchar(100)
                )

            AS
            
            update emp set emplfname=@emplfname,empllname=@empllname,address=@address,landlineno=@landlineno,mobileno=@mobileno,bloodgroup=@bloodgroup,education=@education,yearpass=@yearpass,emplemailid=@emplemailid
GO

GO

--Indexes of table dbo.emp
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[emp] ADD CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED ([emplidno]) 
GO

--Indexes of table dbo.salary
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[salary] ADD CONSTRAINT [PK__salary__397C45AE108B795B] PRIMARY KEY CLUSTERED ([pksalaryslno]) 
GO

--Indexes of table dbo.salset
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[salset] ADD CONSTRAINT [PK__salset__F07FC9381ED998B2] PRIMARY KEY CLUSTERED ([pksalsetslno]) 
GO

--Indexes of table dbo.taxset
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[taxset] ADD CONSTRAINT [PK__taxset__9680AD3922AA2996] PRIMARY KEY CLUSTERED ([pktaxsetslno]) 
GO

--Indexes of table dbo.tblentery
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[tblentery] ADD CONSTRAINT [PK_tblentery] PRIMARY KEY CLUSTERED ([sno]) 
GO