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); }