public List <leavereport> getLeaveRemain(string empid, string leaveid)
{
leavereport lvapp;
List <leavereport> LVlist = new List <leavereport>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select DATEDIFF(DAY,a.SanctionedFromDate ,a.SanctionedToDate ) as totaldays,d.EmployeeID,d.Name,d.OfficeID,d.OfficeName,a.LeaveID " +
"from LeaveRequest a, (select b.UserID,b.Name from ViewUserEmployeeList b where b.EmployeeID = '" + empid + "') c, ViewEmployeeDetails d, ERPUser e " +
"where a.UserID = c.UserID and a.LeaveID in ('" + leaveid + "') and a.UserID=e.UserID and d.EmployeeID=e.EmployeeID and a.Status = 1 " +
" and a.DocumentStatus = 99 and a.LeaveRequestStatus in (1,11,10,9,7) and year(a.SanctionedFromDate)=year(GetDate()) ";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
lvapp = new leavereport();
lvapp.leavepending = reader.IsDBNull(0)? '0' : reader.GetInt32(0);
lvapp.EmployeeID = reader.GetString(1);
lvapp.EmployeeName = reader.GetString(2);
lvapp.officeid = reader.GetString(3);
lvapp.officename = reader.GetString(4);
lvapp.leaveid = reader.GetString(5);
LVlist.Add(lvapp);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(LVlist);
}
public List <leavereport> getleavetype()
{
leavereport lvpr;
List <leavereport> LeaveApr = new List <leavereport>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select distinct LeaveID from LeaveRequest where datepart(YEAR,SanctionedFromDate)=YEAR(GETDATE())";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
lvpr = new leavereport();
lvpr.leaveid = reader.GetString(0);
LeaveApr.Add(lvpr);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(LeaveApr);
}
public List <leavereport> getLeaveRemain2(int Opt, string Region, string Officeid)
{
leavereport lvapp;
List <leavereport> LVlist = new List <leavereport>();
try
{
int inttemp = 0;
DateTime dttemp = DateTime.Now;
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
if (Opt == 2)
{
query = "select DATEDIFF(DAY,a.SanctionedFromDate ,a.SanctionedToDate ) as totaldays,d.EmployeeID,d.Name,a.LeaveID,d.OfficeName,d.OfficeID,a.SanctionedFromDate, a.SanctionedToDate ,a.Status,a.DocumentStatus from ViewEmployeeDetails d FULL OUTER JOIN ERPUser e ON e.EmployeeID = d.EmployeeID FULL OUTER JOIN LeaveRequest a ON a.UserID = e.UserID where d.Status = 1 and d.OfficeID = '" + Officeid + "'";
}
else if (Opt == 3)
{
query = "select DATEDIFF(DAY,a.SanctionedFromDate ,a.SanctionedToDate ) as totaldays,d.EmployeeID,d.Name,a.LeaveID,d.OfficeName,d.OfficeID,a.SanctionedFromDate, a.SanctionedToDate ,a.Status,a.DocumentStatus from ViewEmployeeDetails d FULL OUTER JOIN ERPUser e ON e.EmployeeID = d.EmployeeID FULL OUTER JOIN LeaveRequest a ON a.UserID = e.UserID where d.Status = 1 and d.RegionID = '" + Region + "'";
}
else
{
query = "select DATEDIFF(DAY,a.SanctionedFromDate ,a.SanctionedToDate ) as totaldays,d.EmployeeID,d.Name,a.LeaveID,d.OfficeName,d.OfficeID,a.SanctionedFromDate, a.SanctionedToDate ,a.Status,a.DocumentStatus from ViewEmployeeDetails d FULL OUTER JOIN ERPUser e ON e.EmployeeID = d.EmployeeID FULL OUTER JOIN LeaveRequest a ON a.UserID = e.UserID where d.Status = 1";
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
lvapp = new leavereport();
lvapp.leavepending = reader.IsDBNull(0) ? inttemp : reader.GetInt32(0);
lvapp.EmployeeID = reader.GetString(1);
lvapp.EmployeeName = reader.GetString(2);
lvapp.officeid = reader.IsDBNull(5) ? " " : reader.GetString(5);
lvapp.officename = reader.IsDBNull(4) ? "" : reader.GetString(4);
lvapp.leaveid = reader.IsDBNull(3) ? " ": reader.GetString(3);
lvapp.sanctionedFrom = reader.IsDBNull(6) ? dttemp : reader.GetDateTime(6);
lvapp.sanctionedTo = reader.IsDBNull(7) ? dttemp : reader.GetDateTime(7);
lvapp.status = reader.IsDBNull(8) ? inttemp : reader.GetInt32(8);
lvapp.documentStatus = reader.IsDBNull(9) ? inttemp : reader.GetInt32(9);
LVlist.Add(lvapp);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(LVlist);
}
public List <leavereport> getFilteredLeaveApproval(string officeid, int opt, string region)
{
leavereport lvpr;
List <leavereport> LeaveApr = new List <leavereport>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query1 = "select a.EmployeeID,a.Name,a.OfficeID,a.OfficeName,b.PostingDate " +
"from ViewEmployeePosting a ,(select c.EmployeeID,MAX(c.PostingDate) as PostingDate from ViewEmployeePosting c group by c.EmployeeID) b " +
"where a.EmployeeID = b.EmployeeID and b.PostingDate = a.PostingDate ";
string query2 = "select s1.EmployeeID, s1.Name,s1.OfficeID,s1.OfficeName," +
" s1.PostingDate from ViewEmployeePosting s1 inner join" +
"(select max(PostingDate) PostingDate,EmployeeID from ViewEmployeePosting " +
"group by EmployeeID) s2 on s1.EmployeeID = s2.EmployeeID and " +
"s1.PostingDate = s2.PostingDate where s1.OfficeID = '" + officeid + "' order by s1.EmployeeID";//part
string query3 = "select s1.EmployeeID, s1.Name,s1.OfficeID,s1.OfficeName," +
" s1.PostingDate from ViewEmployeePosting s1 inner join" +
"(select max(PostingDate) PostingDate,EmployeeID from ViewEmployeePosting " +
"group by EmployeeID) s2 on s1.EmployeeID = s2.EmployeeID and " +
"s1.PostingDate = s2.PostingDate,office b where b.RegionID = '" + region + "'and b.OfficeID=s1.OfficeID order by s1.EmployeeID";//region-part and office all
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 3:
query = query3;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
lvpr = new leavereport();
lvpr.EmployeeID = reader.GetString(0);
lvpr.EmployeeName = reader.GetString(1);
lvpr.officeid = reader.GetString(2);
lvpr.officename = reader.GetString(3);
LeaveApr.Add(lvpr);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(LeaveApr);
}