public List <ApplyLeave> viewUserInfo(string MSID)
{
List <ApplyLeave> userInfo = new List <ApplyLeave>();
string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(con))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"select a.Emp_ID,b.Email_ID,a.Sub_Area,a.SupervisorName,a.Cust_Mana_Name,a.Project,a.Location
from LTLogin as a inner join LTEmailID as b
on a.MSID=b.MSID where a.MSID='" + MSID + "'", conn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.FieldCount != 0)
{
while (dr.Read())
{
ApplyLeave userInfoObj = new ApplyLeave();
userInfoObj.emp_ID = Convert.ToInt32(dr["Emp_ID"].ToString());
userInfoObj.add = dr["Email_ID"].ToString();
userInfoObj.subArea = dr["Sub_Area"].ToString();
userInfoObj.supervisorName = dr["SupervisorName"].ToString();
userInfoObj.custManaName = dr["Cust_Mana_Name"].ToString();
userInfoObj.ProjectName = dr["Project"].ToString();
userInfoObj.Location = dr["Location"].ToString();
userInfo.Add(userInfoObj);
}
}
}
return(userInfo);
}
public static List <ApplyLeave> editScreenSHotForEditing(int id)
{
ApplyLeave obj = new ApplyLeave();
List <ApplyLeave> listForLeaveEditing = new List <ApplyLeave>();
string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(con))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select * from LTLeaveDetails where SequenceNo=" + id + "", conn);
SqlDataReader sr = cmd.ExecuteReader();
while (sr.Read())
{
obj.MSID = sr["MSID"].ToString();
obj.imageSrc11 = sr["screenShot"] as byte[] ?? null;
obj.StartDate = Convert.ToDateTime(sr["StartDate"]);
obj.EndDate = Convert.ToDateTime(sr["EndDate"]);
obj.TypeOfLeaves = sr["TOL"].ToString();
obj.TotalLeaves = Convert.ToInt32(sr["TotalDays"]);
obj.Status = sr["_Status"].ToString();
listForLeaveEditing.Add(obj);
}
}
return(listForLeaveEditing);
}
public List <ApplyLeave> getLeaveList(string MSID)
{
List <ApplyLeave> list = new List <ApplyLeave>();
string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(con))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from LTLeaveDetails where MSID='" + MSID + "' order by StartDate desc ", conn);
SqlDataReader sr = cmd.ExecuteReader();
while (sr.Read())
{
ApplyLeave obj = new ApplyLeave();
obj.StartDate = Convert.ToDateTime(sr["StartDate"]);
obj.EndDate = Convert.ToDateTime(sr["EndDate"]);
obj.TypeOfLeaves = sr["TOL"].ToString();
obj.TotalLeaves = Convert.ToInt16(sr["TotalDays"]);
obj.Reason = sr["Reason"].ToString();
obj.sNo = Convert.ToInt32(sr["SequenceNo"]);
obj.imageSrc11 = sr["screenShot"] as byte[] ?? null;
obj.Status = sr["_Status"].ToString();
list.Add(obj);
}
}
return(list);
}
public List <ApplyLeave> getLeave(string Branch)
{
List <ApplyLeave> list = new List <ApplyLeave>();
string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(con))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"select t2.SequenceNo,t1.FirstName, t1.LastName, t2.StartDate,
t2.EndDate, t2.Reason, t2.TOL, t2.TotalDays,t2.screenShot
from LTLogin as t1 inner join LTLeaveDetails as t2
on t1.MSID = t2.MSID where t2._Status = 'Approval Pending' and t1.Sub_Area='" + Branch + "' order by t2.StartDate desc", conn);
SqlDataReader sr = cmd.ExecuteReader();
while (sr.Read())
{
ApplyLeave obj = new ApplyLeave();
obj.sNo = Convert.ToInt32(sr["SequenceNo"]);
obj.FirstName = sr["FirstName"].ToString();
obj.LastName = sr["LastName"].ToString();
obj.StartDate = Convert.ToDateTime(sr["StartDate"]);
obj.EndDate = Convert.ToDateTime(sr["EndDate"]);
obj.TypeOfLeaves = sr["TOL"].ToString();
obj.TotalLeaves = Convert.ToInt16(sr["TotalDays"]);
obj.Reason = sr["Reason"].ToString();
obj.imageSrc11 = sr["screenShot"] as byte[] ?? null;
list.Add(obj);
}
}
return(list);
}
public List <ApplyLeave> ExportToExcelForTL()
{
List <ApplyLeave> ExportToExcelForTLlist = new List <ApplyLeave>();
string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(con))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"select t1.Emp_ID,t1.MSID,t1.FirstName,t1.LastName,t2.StartDate,t2.EndDate,t1.Branch,
t1.Sub_Area,t1.Location,t1.Project,t2.TOL,t2.Reason,
t2.TotalDays,t1.SupervisorName,t2._Status,t1.Cust_Mana_Name,t1.Exp_Level
from LTLogin as t1 Join LTLeaveDetails as t2 on t1.MSID=t2.MSID", conn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.FieldCount != 0)
{
while (dr.Read())
{
ApplyLeave ExportToExcelfortl = new ApplyLeave();
ExportToExcelfortl.emp_ID = Convert.ToInt32(dr["Emp_ID"]);
ExportToExcelfortl.MSID = dr["MSID"].ToString();
ExportToExcelfortl.FirstName = dr["FirstName"].ToString();
ExportToExcelfortl.LastName = (dr["LastName"].ToString());
ExportToExcelfortl.StartDate = Convert.ToDateTime(dr["StartDate"].ToString());
ExportToExcelfortl.EndDate = Convert.ToDateTime(dr["EndDate"].ToString());
ExportToExcelfortl.Branch = (dr["Branch"].ToString());
ExportToExcelfortl.subArea = dr["Sub_Area"].ToString();
ExportToExcelfortl.Location = (dr["Location"].ToString());
ExportToExcelfortl.ProjectName = (dr["Project"].ToString());
ExportToExcelfortl.TypeOfLeaves = (dr["TOL"].ToString());
ExportToExcelfortl.Reason = (dr["Reason"].ToString());
ExportToExcelfortl.TotalLeaves = Convert.ToInt32(dr["TotalDays"]);
ExportToExcelfortl.supervisorName = (dr["SupervisorName"].ToString());
ExportToExcelfortl._Status = (dr["_Status"].ToString());
ExportToExcelfortl.custManaName = dr["Cust_Mana_Name"].ToString();
ExportToExcelfortl.expLevel = dr["Exp_Level"].ToString();
ExportToExcelForTLlist.Add(ExportToExcelfortl);
}
}
}
return(ExportToExcelForTLlist);
}
public static ApplyLeave getLeaveInfo(int id)
{
ApplyLeave obj = new ApplyLeave();
string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(con))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select * from LTLeaveDetails where SequenceNo=" + id + "", conn);
SqlDataReader sr = cmd.ExecuteReader();
while (sr.Read())
{
obj.imageSrc11 = sr["screenShot"] as byte[] ?? null;
obj.StartDate = Convert.ToDateTime(sr["StartDate"]);
obj.EndDate = Convert.ToDateTime(sr["EndDate"]);
obj.Status = sr["_Status"].ToString();
}
}
return(obj);
}
public List <ApplyLeave> ExportToExcelForUser()
{
List <ApplyLeave> ExportToExcelForUserList = new List <ApplyLeave>();
string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(con))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"select MSID,FirstName,LastName from LTLogin", conn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.FieldCount != 0)
{
while (dr.Read())
{
ApplyLeave ExportToExcelforuser = new ApplyLeave();
ExportToExcelforuser.MSID = dr["MSID"].ToString();
ExportToExcelforuser.FirstName = dr["FirstName"].ToString();
ExportToExcelforuser.LastName = (dr["LastName"].ToString());
ExportToExcelForUserList.Add(ExportToExcelforuser);
}
}
}
return(ExportToExcelForUserList);
}
public List <ApplyLeave> SearchMSIDByName(string Name)
{
List <ApplyLeave> MSIDlist = new List <ApplyLeave>();
string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(con))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"select MSID,FirstName,LastName from LTLogin where FirstName like '%" + Name + "%'", conn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.FieldCount != 0)
{
while (dr.Read())
{
ApplyLeave appLeaveObj = new ApplyLeave();
appLeaveObj.MSID = dr["MSID"].ToString();
appLeaveObj.FirstName = dr["FirstName"].ToString();
appLeaveObj.LastName = dr["LastName"].ToString();
MSIDlist.Add(appLeaveObj);
}
}
return(MSIDlist);
}
}
