public List <List <string> > GetSingleUserAttendanceDetails(clsBE BE_In, DateTime pLoginDate_In)
//============================================================================================
{
List <List <string> > pSingleUserAttendanceDetails = new List <List <string> >();
con.Open();
//String pUserName = BE_In.UserName;
int pUserId = BE_In.UserId;
SqlCommand cmd = new SqlCommand("select fldUserName, fldLoginDate, fldLoginTime, fldLoginRemarks, fldLogoutTime, fldLogoutRemarks from tblUserAttendanceDetails where fldUserId='" + pUserId + "' and fldLoginDate='" + pLoginDate_In + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
List <string> pCol = new List <string>();
for (int j = 0; j < dt.Columns.Count; j++)
{
pCol.Add(dt.Rows[i][j].ToString());
}
pSingleUserAttendanceDetails.Add(pCol);
}
con.Close();
return(pSingleUserAttendanceDetails);
}
public int IsUserLoggedIn(clsBE BE_In)
//====================================
{
DateTime pDate = Convert.ToDateTime(DateTime.Now.ToShortDateString());
return(mDA.IsUserLoggedIn(BE_In, pDate));
}
public int AddUserPersonalDetails(clsBE BE_In)
//===============================================
{
con.Open();
string pUserRole = BE_In.UserRole;
bool pUserActive = true;
String pUserFullName = BE_In.UserFullName;
string pUserAddress = BE_In.UserAddress;
int pUserPincode = BE_In.UserPincode;
string pUserGender = BE_In.UserGender;
DateTime pUserDOB = BE_In.UserDOB;
Int64 pUserContactNo = BE_In.UserContactNo;
string pUserEmail = BE_In.UserEmail;
string pUserQualification = BE_In.UserQualification;
SqlCommand cmd = new SqlCommand("insert into tblUserPersonalDetails (fldFullName, fldAddress, fldPincode, fldGender, fldDOB, fldContactNo, fldEmail, fldQualification) values ('" + pUserFullName + "','" + pUserAddress + "','" + pUserPincode + "','" + pUserGender + "','" + pUserDOB + "','" + pUserContactNo + "','" + pUserEmail + "','" + pUserQualification + "')", con);
int i = 0;
i = cmd.ExecuteNonQuery();
if (i > 0)
{
cmd = new SqlCommand("insert into tblUserLogin (fldUserRole, fldUserActive) values('" + pUserRole + "','" + pUserActive + "')", con);
i = cmd.ExecuteNonQuery();
}
if (i > 0)
{
cmd = new SqlCommand("select MAX(fldUserId) from tblUserLogin", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
i = Convert.ToInt32(dt.Rows[0][0].ToString());
}
con.Close();
return(i);
}
public int IsUserLoggedOut(clsBE BE_In)
//====================================
{
DateTime pDate = Convert.ToDateTime(DateTime.Now.ToShortDateString());
DateTime pTime = Convert.ToDateTime(mDA.GetMaxLogInTime(BE_In, pDate));
return(mDA.IsUserLoggedOut(BE_In, pDate, pTime));
}
public int TimeInReInsert(clsBE BE_In, string pRemarks_In, ref DateTime pTime_Out)
//===============================================================================
{
DateTime pDate = Convert.ToDateTime(DateTime.Now.ToShortDateString());
DateTime pTime = Convert.ToDateTime(DateTime.Now.ToShortTimeString());
pTime_Out = pTime;
return(mDA.TimeInReInsert(BE_In, pDate, pTime, pRemarks_In));
}
public int DeleteUser(clsBE BE_In)
//=================================
{
con.Open();
int pUserId = BE_In.UserId;
SqlCommand cmd = new SqlCommand("update tblUserLogin set fldUserActive='" + false + "' where fldUserId='" + pUserId + "'", con);
int i = cmd.ExecuteNonQuery();
con.Close();
return(i);
}
public int TimeOutInsert(clsBE BE_In, string pRemarks_In, ref DateTime pTime_Out)
//===============================================================================
{
DateTime pDate = Convert.ToDateTime(DateTime.Now.ToShortDateString());
DateTime pTime = Convert.ToDateTime(DateTime.Now.ToShortTimeString());
pTime_Out = pTime;
DateTime pLastLoginTime = Convert.ToDateTime(mDA.GetMaxLogInTime(BE_In, pDate));
return(mDA.TimeOutInsert(BE_In, pDate, pTime, pLastLoginTime, pRemarks_In));
}
public string GetUserId(clsBE BE_In)
//=======================================
{
con.Open();
string pUserName = BE_In.UserName;
SqlCommand cmd = new SqlCommand("select fldUserId from tblUserLogin where fldUserName='******'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
return(dt.Rows[0][0].ToString());
}
public int TimeInReInsert(clsBE BE_In, DateTime pDate_In, DateTime pTime_In, string pRemarks_In)
//=================================================================================================
{
con.Open();
int i = 0;
String pUserName = BE_In.UserName;
int pUserId = BE_In.UserId;
SqlCommand cmd = new SqlCommand("INSERT INTO tblUserAttendanceDetails (fldUserId, fldUserName, fldLoginDate, fldLoginTime, fldLoginRemarks) values ('" + pUserId + "','" + pUserName + "',' " + pDate_In + "', '" + pTime_In + "', '" + pRemarks_In + "')", con);
i = cmd.ExecuteNonQuery();
con.Close();
return(i);
}
public string IsDeleteAdmin(clsBE BE_In)
//==============================
{
con.Open();
int pUserId = BE_In.UserId;
SqlCommand cmd = new SqlCommand("select fldUserRole from tblUserLogin where fldUserId='" + pUserId + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
return(dt.Rows[0][0].ToString());
}
public string SelectUserName(clsBE BE_In)
//====================================
{
con.Open();
string pUserName = BE_In.UserName;
string pPassword = BE_In.Password;
SqlCommand cmd = new SqlCommand("select fldFullName from tblUserPersonalDetails where fldId in (select fldUserId from tblUserLogin where fldUserName='******')", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
return(dt.Rows[0][0].ToString());
}
public string IsAdmin(clsBE BE_In)
//==============================
{
con.Open();
string pUserName = BE_In.UserName;
string pPassword = BE_In.Password;
SqlCommand cmd = new SqlCommand("select fldUserRole from tblUserLogin where fldUserName='******'and fldUserPassword='******'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
return(dt.Rows[0][0].ToString());
}
public string GetMaxLogInTime(clsBE BE_In, DateTime pDate_In)
//===========================================================
{
con.Open();
string pUserName = BE_In.UserName;
DateTime pLoginDate = pDate_In;
SqlCommand cmd = new SqlCommand("select MAX(fldLoginTime) from tblUserAttendanceDetails where fldUserName='******' and fldLoginDate='" + pLoginDate + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
return(dt.Rows[0][0].ToString());
}
public int SetUserNamePassword(clsBE BE_In)
//==========================================
{
con.Open();
int pUserId = BE_In.UserId;
string pUserName = BE_In.UserName;
string pUserPassword = BE_In.Password;
DateTime pUserRegistrationDate = Convert.ToDateTime(DateTime.Now.ToShortTimeString());
SqlCommand cmd = new SqlCommand("update tblUserLogin set fldUserName='******', fldUserPassword='******', fldUserRegistrationDate='" + pUserRegistrationDate + "' where fldUserId='" + pUserId + "'", con);
int i = cmd.ExecuteNonQuery();
con.Close();
return(i);
}
public int IsUserLoggedIn(clsBE BE_In, DateTime pDate_In)
//==========================================================
{
con.Open();
string pUserName = BE_In.UserName;
DateTime pLoginDate = pDate_In;
string pSQL = "select fldUserName from tblUserAttendanceSummary where fldLoginDate='" + pLoginDate + "' and fldUserId in (select fldUserId from tblUserLogin where fldUserName='******')";
SqlCommand cmd = new SqlCommand(pSQL, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
return(dt.Rows.Count);
}
public int IsDuplicateUserName(clsBE BE_In)
//==========================================
{
con.Open();
string pUserName = BE_In.UserName;
SqlCommand cmd = new SqlCommand("select * from tblUserLogin where fldUserName='******'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
int i = dt.Rows.Count;
con.Close();
return(i);
}
public int TimeOutInsert(clsBE BE_In, DateTime pDate_In, DateTime pTime_In, DateTime pLastLoginTime_In, string pRemarks_In)
//========================================================================================================================
{
con.Open();
int i = 0;
String pUserName = BE_In.UserName;
SqlCommand cmd = new SqlCommand("update tblUserAttendanceSummary set fldLogoutTime='" + pTime_In + "' where fldUserName='******' and fldLoginDate='" + pDate_In + "'", con);
i = cmd.ExecuteNonQuery();
if (i > 0)
{
cmd = new SqlCommand("update tblUserAttendanceDetails set fldLogoutTime='" + pTime_In + "' , fldLogoutRemarks='" + pRemarks_In + "' where fldUserName='******' and fldLoginDate='" + pDate_In + "' and fldLoginTime='" + pLastLoginTime_In + "'", con);
i = cmd.ExecuteNonQuery();
}
con.Close();
return(i);
}
public int EditUserPersonalDetails(clsBE BE_In)
//===============================================
{
con.Open();
int pUserId = BE_In.UserId;
string pUserAddress = BE_In.UserAddress;
int pUserPincode = BE_In.UserPincode;
string pUserGender = BE_In.UserGender;
DateTime pUserDOB = BE_In.UserDOB;
Int64 pUserContactNo = BE_In.UserContactNo;
string pUserEmail = BE_In.UserEmail;
string pUserQualification = BE_In.UserQualification;
SqlCommand cmd = new SqlCommand("update tblUserPersonalDetails set fldAddress='" + pUserAddress + "', fldPincode='" + pUserPincode + "', fldGender='" + pUserGender + "', fldDOB='" + pUserDOB + "', fldContactNo='" + pUserContactNo + "', fldEmail='" + pUserEmail + "', fldQualification='" + pUserQualification + "' where fldId='" + pUserId + "'", con);
int i = cmd.ExecuteNonQuery();
con.Close();
return(i);
}
public int IsUserLoggedOut(clsBE BE_In, DateTime pDate_In, DateTime pTime_In)
//============================================================================
{
con.Open();
string pUserName = BE_In.UserName;
DateTime pLoginDate = pDate_In;
DateTime pLoginTime = pTime_In;
string pSQL = "select fldLogoutTime from tblUserAttendanceDetails where fldLoginDate='" + pLoginDate + "' and fldLoginTime='" + pLoginTime + "' and fldUserId in (select fldUserId from tblUserLogin where fldUserName='******')";
SqlCommand cmd = new SqlCommand(pSQL, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
if (!Convert.IsDBNull(dt.Rows[0][0]))
{
return(1);
}
return(0);
}
public List <List <string> > GetSingleUserPersonalDetails(clsBE BE_In)
//==============================================================
{
List <List <string> > pSingleUserDetails = new List <List <string> >();
con.Open();
int pUserId = BE_In.UserId;
SqlCommand cmd = new SqlCommand("select * from tblUserPersonalDetails where fldId='" + pUserId + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
List <string> pCol = new List <string>();
for (int j = 0; j < dt.Columns.Count; j++)
{
pCol.Add(dt.Rows[i][j].ToString());
}
pSingleUserDetails.Add(pCol);
}
con.Close();
return(pSingleUserDetails);
}
public int IsDuplicateUserName(clsBE BE_In)
//==========================================
{
return(mDA.IsDuplicateUserName(BE_In));
}
public int AddUserPersonalDetails(clsBE BE_In)
//===============================================
{
return(mDA.AddUserPersonalDetails(BE_In));
}
public int UserLogin(clsBE BE_In)
//=============================
{
return(mDA.UserLogin(BE_In));
}
public int SetUserNamePassword(clsBE BE_In)
//==========================================
{
return(mDA.SetUserNamePassword(BE_In));
}
public string IsUserActive(clsBE BE_In)
//===================================
{
return(mDA.IsUserActive(BE_In));
}
public int SingleUserLeaveGenerate(clsBE BE_In)
//==============================================
{
con.Open();
int z = 0;
int pUserId = BE_In.UserId;
SqlCommand cmd = new SqlCommand("select fldUserName from tblUserLogin where fldUserId='" + pUserId + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
string pUserName = dt.Rows[0][0].ToString();
DateTime pCurrentDate = Convert.ToDateTime(DateTime.Now.ToShortDateString());
int pYear = pCurrentDate.Year;
int pMonthNo = pCurrentDate.Month;
if (pMonthNo < 7)
{
int pSession = 1;
int pMonth = 7 - pMonthNo;
double pAvailableCL = 5.0 * pMonth / 6;
double pTakenCL = 0.0;
double pRemainCL = pAvailableCL;
double pAvailableEL = 7.5 * pMonth / 6;
double pTakenEL = 0.0;
double pRemainEL = pAvailableEL;
double pAvailableML = pAvailableCL;
double pTakenML = 0.0;
double pRemainML = pAvailableCL;
cmd = new SqlCommand("insert into tblUserLeaveDetails (fldUserId, fldUserName, fldYear, fldSession, fldAvailableCL, fldTakenCL, fldRemainCL, fldAvailableEL, fldTakenEL, fldRemainEL, fldAvailableML, fldTakenML, fldRemainML) values ('" + pUserId + "','" + pUserName + "','" + pYear + "','" + pSession + "','" + pAvailableCL + "','" + pTakenCL + "','" + pRemainCL + "','" + pAvailableEL + "','" + pTakenEL + "','" + pRemainEL + "','" + pAvailableML + "','" + pTakenML + "','" + pRemainML + "')", con);
z = cmd.ExecuteNonQuery();
if (z > 0)
{
pSession = 2;
pAvailableCL = 5.0;
pTakenCL = 0.0;
pRemainCL = 5.0;
pAvailableEL = 7.5;
pTakenEL = 0.0;
pRemainEL = 7.5;
pAvailableML = 5.0;
pTakenML = 0.0;
pRemainML = 5.0;
cmd = new SqlCommand("insert into tblUserLeaveDetails (fldUserId, fldUserName, fldYear, fldSession, fldAvailableCL, fldTakenCL, fldRemainCL, fldAvailableEL, fldTakenEL, fldRemainEL, fldAvailableML, fldTakenML, fldRemainML) values ('" + pUserId + "','" + pUserName + "','" + pYear + "','" + pSession + "','" + pAvailableCL + "','" + pTakenCL + "','" + pRemainCL + "','" + pAvailableEL + "','" + pTakenEL + "','" + pRemainEL + "','" + pAvailableML + "','" + pTakenML + "','" + pRemainML + "')", con);
z = cmd.ExecuteNonQuery();
}
}
else
{
int pSession = 2;
int pMonth = 13 - pMonthNo;
double pAvailableCL = 5.0 * pMonth / 6;
double pTakenCL = 0.0;
double pRemainCL = pAvailableCL;
double pAvailableEL = 7.5 * pMonth / 6;
double pTakenEL = 0.0;
double pRemainEL = pAvailableEL;
double pAvailableML = pAvailableCL;
double pTakenML = 0.0;
double pRemainML = pAvailableCL;
cmd = new SqlCommand("insert into tblUserLeaveDetails (fldUserId, fldUserName, fldYear, fldSession, fldAvailableCL, fldTakenCL, fldRemainCL, fldAvailableEL, fldTakenEL, fldRemainEL, fldAvailableML, fldTakenML, fldRemainML) values ('" + pUserId + "','" + pUserName + "','" + pYear + "','" + pSession + "','" + pAvailableCL + "','" + pTakenCL + "','" + pRemainCL + "','" + pAvailableEL + "','" + pTakenEL + "','" + pRemainEL + "','" + pAvailableML + "','" + pTakenML + "','" + pRemainML + "')", con);
z = cmd.ExecuteNonQuery();
}
con.Close();
return(z);
}
public string SelectUserName(clsBE BE_In)
//=======================================
{
return(mDA.SelectUserName(BE_In));
}
public List <List <string> > GetSingleUserPersonalDetails(clsBE BE_In)
//=========================================================
{
return(mDA.GetSingleUserPersonalDetails(BE_In));
}
public string IsAdmin(clsBE BE_In)
//===============================
{
return(mDA.IsAdmin(BE_In));
}
public string GetUserId(clsBE BE_In)
//=================================
{
return(mDA.GetUserId(BE_In));
}
