create table date(username varchar(50),password varchar(50), logindate datetime ,logoutdate datetime)
-----------loginpage.aspx.cs-----------
protected void Button1_Click(object sender, EventArgs e)
{
cls_PMPtables obj = new cls_PMPtables();
DataSet ds = new DataSet();
username = TextBox1.Text;
password = TextBox2.Text;
Session["username"] = username;
obj.savelogin(username,password);
if (ds != null)
{
Response.Redirect("Default2.aspx");
}
}
}
-------------------- cls_PMPtables.cs class file define globally connection----------------------------
public SqlConnection sqlconn = new SqlConnection(ConfigurationManager.AppSettings["statecity"]);
SqlCommand cmd = new SqlCommand();
-----------------------------------------------------------------------------------------------
------------------ cls_PMPtables.cs class file--------------------------------------------------
public void savelogin( string username, string password)
{
try
{
sqlconn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "PMP_Savelogin";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlconn;
cmd.Parameters.Add("@Username", SqlDbType.VarChar);
cmd.Parameters["@Username"].Value = username;
cmd.Parameters.Add("@Password", SqlDbType.VarChar);
cmd.Parameters["@Password"].Value = password;
cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
sqlconn.Close();
}
}
------savelogin procedure---------
create PROCEDURE [dbo].[PMP_Savelogin]
@Username AS varchar(50),
@Password AS varchar(50)
AS
SELECT logindate FROM DATE WHERE CONVERT(VARCHAR(25),logindate,101)= CONVERT(VARCHAR(25),getDate(),101)
IF (@@rowcount = 0)
BEGIN
INSERT INTO date (username,password,logindate,logoutdate)
VALUES
(@Username,@Password,getdate(),getdate())
insert into Attendence(username,hourlytime,AttendenceDate)
values
(@Username,'',getdate())
END
ELSE
BEGIN
update date set username = @Username , password = @Password where username = @Username
END
---------------Default2.aspx.cs---------------
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = Session["username"].ToString();
}
*****************************************************************
----------create table attendence fo storing hourlytime on particular date--------
create table attendence(username varchar(50), hourlytime varchar(10),AttendenceDate datetime)
-----------------------------------------------------------------------------------------------------
--------------create GetTime function for calculating login logout difference for particular day--------
Create FUNCTION [dbo].[GetTime](@username VARCHAR(50),@Date DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @hour AS VARCHAR(10),@minute AS VARCHAR(10)
SELECT @hour=datediff(hour,logindate,logoutdate) from date where username=@username AND CONVERT(VARCHAR(25),logindate,111) = CONVERT(VARCHAR(25),@Date,111)
SELECT @minute=datediff(minute,logindate,logoutdate) from date where username=@username AND CONVERT(VARCHAR(25),logindate,111) = CONVERT(VARCHAR(25),@Date,111)
RETURN (@hour + ':' + @minute)
END
--------------------------------------------------------------------------------------------------------------------------
--------------also Defaul2.aspx page take one hyperlink and set navigationurl property Logout.aspx--------
------------and write code on Logout.aspx.cs file page load-------------------------
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string user = Session["username"].ToString();
cls_PMPtables obj = new cls_PMPtables();
DataSet ds = new DataSet();
DateTime logoutdate = DateTime.Now;
obj.saveloginn(user,logoutdate);
}
}
-------------- cls_PMPtables.cs class file write saveloginn procedure---------
Create PROCEDURE [dbo].[PMP_Saveloginn]
@Username AS varchar(50),
@Logoutdate AS datetime
As
Declare @loginday as INT, @logoutday as INT
select @loginday = DATEPART(dy, logindate) from date where username = @Username
select @logoutday = DATEPART(dy, logoutdate) from date where username = @Username
if (@loginday = @logoutday )
BEGIN
update date set logoutdate = @Logoutdate where username=@Username and CONVERT(VARCHAR(25),logindate,101)= CONVERT(VARCHAR(25),getDate(),101)
update attendence set hourlytime = dbo.GetTime(@Username,getdate()),AttendenceDate=getdate()
where username = @Username and CONVERT(VARCHAR(25),AttendenceDate,101)= CONVERT(VARCHAR(25),getDate(),101)
END
else
Begin
Declare @password as varchar(50)
SELEct @password = password from date where username = @Username
insert into date (username,password,logindate,logoutdate) values(@Username,@password,getdate(),getdate())
end
-----------------------------------------------------------------------------
