HRM DB

hrm.sql

use hrm

create table mtblAccYear(
intAccYear numeric(6,0) not null primary key identity(1,1),
varAccYear varchar(15),
dtBeginDate datetime,
dtEndDate datetime,
)

create table mtblUserRole (
intUserRoleId numeric(6,0) identity (1,1) not null primary key,
varUserRole varchar (20),
varMasterFile varchar(30)
)

create table mtblUser (
intUserId  numeric(6, 0) IDENTITY(1,1) NOT NULL primary key,
varUserName varchar(30) not null,
varPwd varchar(30) not null,
intUserRoleId numeric(6,0),
dtDate datetime
) 

CREATE TABLE ctblLoginDetail (
	intLoginDetailsId numeric(6, 0) IDENTITY(1,1) NOT NULL primary key,
	dtDate datetime,
	varIPNo varchar(17),
	intId numeric(6, 0),
	varType varchar(2)
)  

 create table mtblCategory (
intCategoryId  numeric(6, 0) IDENTITY(1,1) NOT NULL primary key,
varCategory varchar(30) not null, 
)

 create table mtblDepartment (
intDepartmentId  numeric(6, 0) IDENTITY(1,1) NOT NULL primary key,
varDepartment varchar(30) not null, 
)

 create table mtblDesignation (
intDesignationId  numeric(6, 0) IDENTITY(1,1) NOT NULL primary key,
varDesignation varchar(30) not null, 
)
   
create table ctblDisability(
intDisability numeric(6,0) identity(1,1) primary key,
varDisablrType varchar(30)
) 

create table mtblCountry(
intCountryId numeric(6,0) identity (1,1) not null primary key,
varCountryName varchar(20) unique
)   
 
create table ctblState(
intStateId numeric(6,0) identity (1,1) not null primary key,
intCountryId numeric(6,0) not null constraint fk_intCountryId foreign key references mtblCountry(intCountryId),
varStateName varchar(20) unique
)

create table ctblDistrict(
intDistrictId numeric(6,0) identity (1,1) not null primary key,
intStateId numeric(6,0) not null constraint fk_intStateId foreign key references ctblState(intStateId),
varDistrict varchar(20) unique 
)

create table ctblCity(
intCityId numeric(6,0) identity (1,1) not null primary key,
varCity varchar(20) unique
)   

insert into mtblCountry values ('india')


select * from mtblCountry
select * from ctblState
select * from ctblDistrict

create table mtblEmployee (
intEmpId numeric(6,0) identity(1,1) not null primary key,
varEmpId varchar(20),
varPwd varchar(20),
intCompanyId numeric(6,0),
varCompanyESINo varchar(20),
intEmpType numeric(6,0),
varTitle varchar(5),
varInitial varchar(5),
varEmpName varchar(30),
varSex  varchar(10),
varBloodGroup varchar(5),
varFHName varchar(30),
varGuardian varchar(30),
varEducation varchar(30),
varReligion varchar(15),
varCommunity varchar(15),
varMarital varchar(15), 
varImage varchar(30),
varPdf varchar(30),
intCountryId numeric(6,0),
intStateId numeric(6,0),
intDistrictId numeric(6,0),
varCity varchar(15),
varAdd varchar(30), 
intPincode numeric(10),
varMobile varchar(20),
varEmail varchar(30),
dtDOB datetime,
dtDOJ datetime,
varDisability varchar(20), 
varPFNo varchar(20),
varESINo varchar(20),
varDispensaryName varchar(20),
varDispensaryCity varchar(20), 
intCategoryId numeric(6,0),
intDepartmentId numeric(6,0),
intDesignationId numeric(6,0),
varBankAccNo varchar(30),
varBankCode varchar(10),
varBankName varchar(30),
varBankCity varchar(20),
varSalaryMode varchar(10),
varSalaryType varchar(10),
intSalary numeric(20,0),
intHra numeric(6,0),
intDa numeric(6,0),
intWa numeric(6,0),
intTa numeric(6,0),
intOa numeric(6,0),
intOtPerHour numeric(6,0),
intGrossPay numeric(10,0), 
dtResignDate datetime,
varResignReason varchar(10), 
dtRejoinDate datetime,
intUserId  numeric(6, 0),
dtDate datetime, 
bitApprove bit,
)
 
create table mtblCompany (
intCompanyId numeric(6,0) identity (1,1) not null primary key,
varCompTitle varchar (20),
varCompanyName varchar(30),
intCountryId numeric(6,0),
intStateId numeric(6,0),
intDistrictId numeric(6,0),
varCity varchar(15),
varAdd varchar(30), 
intPincode numeric(10),
varMobile varchar(20),
varPhone varchar(20),
varEmail varchar(30),
varBankAccNo varchar(30),
varBankCode varchar(10),
varBankName varchar(30),
varBankCity varchar(20),
varPFNo varchar(20),
varESINo varchar(20),
intCompEsiPer numeric(20,0),
intEmpEsiPer numeric(20,0),
intPfLimit numeric(20,0),
intEsiLimit numeric(20,0),
intEsiDailyWage numeric(20,0),
) 
 
create table ctblCompEsiDetail(
intEsiDetailId numeric(6,0) identity (1,1) not null primary key,
intCompanyId numeric(6,0) not null constraint fk_intCompanyId foreign key references mtblCompany(intCompanyId),
varCompanyESINo varchar(20) unique
) 
 
select * from mtblcompany
select * from ctblstate
/* Stored Procedures */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[GetIdentity]
@TableName as varchar(50)
as
begin	
	select IDENT_CURRENT(@TableName) as IdentValue
end
GO 

/* --------------- Insert Some Data -------------- */

insert into mtbluserrole values('admin',1)
insert into mtbluserrole values('emp',2)   
insert into mtblUser values ('admin','admin',1, 9-11-2016)
insert into mtblUser values ('emp','emp',2, 2016-10-10)
insert into ctblDisability values ('Hearing Impaired')
insert into ctblDisability values ('Visually Challenged')

insert into ctblCompEsiDetail values (1,'66000404150001001')  
insert into ctblCompEsiDetail values (1,'47660404150011001')
insert into ctblCompEsiDetail values (1,'48660404150011001')
insert into ctblCompEsiDetail values (1,'56660404150011001')
insert into ctblCompEsiDetail values (2,'66660404150011001')


select * from ctbldistrict

/* Samples */
 SELECT [t0].[intUserRoleId], [t0].[varUserRole], [t1].[intUserId], [t1].[varUserName], [t1].[varPwd], [t1].[dtDate]
FROM [dbo].[mtblUserRole] AS [t0]
INNER JOIN [dbo].[mtblUser] AS [t1] ON ([t0].[intUserRoleId]) = [t1].[intUserRoleId]
ORDER BY [t1].[varUserName]


insert into ctbldistrict values (1,'Coimbatore')

truncate table ctbldistrict

select * from ctblLoginDetail