public List <leaveapprove> getLeaveLimit() { leaveapprove lvapp; List <leaveapprove> LVlist = new List <leaveapprove>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = " select distinct a.LeaveID from LeaveOB a where a.Year=YEAR(GETDATE()) "; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lvapp = new leaveapprove(); lvapp.leaveid = reader.GetString(0); LVlist.Add(lvapp); } conn.Close(); } catch (Exception ex) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); } return(LVlist); }
//select LeaveID, OfficeID, MaxDays from LeaveOfficeMapping public List <leaveapprove> getLeaveRemainYearWise(string empid, string leaveid, int year) { leaveapprove lvapp; List <leaveapprove> LVlist = new List <leaveapprove>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select DATEDIFF(DAY,a.SanctionedFromDate ,a.SanctionedToDate ) as totaldays from LeaveRequest a, " + "(select b.UserID from ViewUserEmployeeList b where b.EmployeeID = '" + empid + "') c " + " where a.UserID = c.UserID and a.LeaveID = '" + leaveid + "' and a.Status = 1 and a.DocumentStatus = 99 and a.LeaveRequestStatus in (1,11,10,9,7) and year(a.SanctionedFromDate) <'" + year + "' "; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lvapp = new leaveapprove(); lvapp.leavepending = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); LVlist.Add(lvapp); } conn.Close(); } catch (Exception ex) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); } return(LVlist); }
public Boolean RejectLeave(leaveapprove lv) { Boolean status = true; string utString = ""; try { string updateSQL = "update LeaveRequest set " + "Remarks='" + lv.remarks + "'" + ", LeaveRequestStatus=99 " + ", ApproveTime=GetDate() " + " where RowID='" + lv.rowid + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "LeaveRequest", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); status = false; } return(status); }
public Boolean ApproveCheck(leaveapprove lv, double totaldays) { Boolean status = false; try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select a.MaxSanctionLimit from LeaveSanctionLimit a,ViewEmployeeDetails b where b.EmployeeID='" + Login.empLoggedIn + "' " + "and a.Designation = b.Designation and a.LeaveID = '" + lv.leaveid + "'"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { if (reader.GetInt32(0) >= totaldays) { status = true; } } } catch (Exception) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); status = false; } return(status); }
public Boolean ApproveLeaveRequestModified(leaveapprove lv) { Boolean status = true; string utString = ""; string updateSQL = ""; try { updateSQL = "update LeaveRequest set Remarks='" + lv.remarks + "', SanctionedToDate='" + lv.sanctionedTo.ToString("yyyy-MM-dd") + "', " + "documentstatus=99,LeaveRequestStatus=9 ,ApproveUser='******', " + "ApproveTime=GetDate() where RowID='" + lv.rowid + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("approve", "LeaveRequest", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); status = false; } return(status); }
public Boolean CancelCancelRequest(leaveapprove lv) { Boolean status = true; string utString = ""; try { string updateSQL = "update LeaveRequest set Remarks='" + lv.remarks + "', " + "LeaveRequestStatus=11 " + "where UserID in (select userid from ViewUserEmployeeList where EmployeeID = '" + lv.EmployeeID + "') and RowID='" + lv.rowid + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("approve", "LeaveRequest", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); status = false; } return(status); }
public Boolean forwardleave(leaveapprove lv) { Boolean status = true; string utString = ""; try { string updateSQL = "update LeaveRequest set DocumentStatus += 1 , forwardUser='******'" + ", Remarks='" + lv.remarks + "'" + ", ForwarderList='" + lv.ForwarderList + "'" + " where RowID='" + lv.rowid + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("forward", "LeaveRequest", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); status = false; } return(status); }
public leaveapprove forwarder(string empid) { leaveapprove lvapp = new leaveapprove(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select a.UserID,e.Name from ERPUser a,Employee e where " + "a.EmployeeID in (select emp.ReportingOfficerID from ViewEmployeeDetails" + " emp where emp.EmployeeID = '" + empid + "' ) and a.EmployeeID = e.EmployeeID"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lvapp.ForwardUser = reader.GetString(0); lvapp.username = reader.GetString(1); } conn.Close(); } catch (Exception ex) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); } return(lvapp); }
public List <leaveapprove> getLeaveLimit(string empid) { leaveapprove lvapp; List <leaveapprove> LVlist = new List <leaveapprove>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select a.LeaveCount,a.LeaveID from ViewLeaveCount a where EmployeeID='" + empid + "'"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lvapp = new leaveapprove(); lvapp.maxdays = reader.GetInt32(0); lvapp.leaveid = reader.GetString(1); LVlist.Add(lvapp); } conn.Close(); } catch (Exception ex) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); } return(LVlist); }
public List <leaveapprove> getApprovedLeave(DateTime frmdate, DateTime todate) { leaveapprove lvapp; List <leaveapprove> LVlist = new List <leaveapprove>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select b.EmployeeID,b.Name,a.LeaveID,CAST(a.CreateTime as date)ApplicationDate, " + "a.FromDate,a.ToDate,a.SanctionedFromDate, " + "a.SanctionedToDate,DATEDIFF(DAY, a.SanctionedFromDate, a.SanctionedToDate) + 1 as noofdays, " + "a.Status, a.DocumentStatus, a.LeaveRequestStatus,(select Name from ViewUserEmployeeList where UserID= a.ApproveUser) Approver " + "from LeaveRequest a, ViewUserEmployeeList b " + " where a.UserID = b.UserID and " + "((a.SanctionedFromDate between '" + frmdate.ToString("yyyy-MM-dd") + "' and '" + todate.ToString("yyyy-MM-dd") + "') or " + "(a.SanctionedToDate between '" + frmdate.ToString("yyyy-MM-dd") + "' and '" + todate.ToString("yyyy-MM-dd") + "') or " + "('" + frmdate.ToString("yyyy-MM-dd") + "' >= a.SanctionedFromDate and '" + frmdate.ToString("yyyy-MM-dd") + "' <= a.SanctionedToDate) or " + "('" + todate.ToString("yyyy-MM-dd") + "' >= a.SanctionedFromDate and '" + todate.ToString("yyyy-MM-dd") + "' <= a.SanctionedToDate) ) and " + "a.Status = 1 and a.DocumentStatus = 99 "; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lvapp = new leaveapprove(); lvapp.EmployeeID = reader.GetString(0); lvapp.EmployeeName = reader.GetString(1); lvapp.leaveid = reader.GetString(2); lvapp.CreateTime = reader.GetDateTime(3); lvapp.fromdate = reader.GetDateTime(4); lvapp.todate = reader.GetDateTime(5); lvapp.sanctionedFrom = reader.GetDateTime(6); lvapp.sanctionedTo = reader.GetDateTime(7); lvapp.noofdays = reader.GetInt32(8); lvapp.status = reader.IsDBNull(9) ? 0 : reader.GetInt32(9); lvapp.documentStatus = reader.GetInt32(10); lvapp.leavestatus = reader.GetInt32(11); lvapp.ApproveUser = reader.GetString(12); LVlist.Add(lvapp); } conn.Close(); } catch (Exception ex) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); } return(LVlist); }
public Boolean ApproveLeaveRequestCOmpoff(leaveapprove lv, int tp) { Boolean status = true; string utString = ""; string updateSQL = ""; try { updateSQL = "update LeaveRequest set Remarks='" + lv.remarks + "', " + " SanctionedFromDate = '" + lv.sanctionedFrom.ToString("yyyy-MM-dd") + "', SanctionedToDate ='" + lv.sanctionedTo.ToString("yyyy-MM-dd") + "'," + "documentstatus=99, ApproveUser='******', ApproveTime=GetDate() " + "where RowID='" + lv.rowid + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "LeaveRequest", "", updateSQL) + Main.QueryDelimiter; updateSQL = "update LeaveEarning set Status=2 where RowID in" + " (select top " + tp + " rowid from LeaveEarning where " + " EmployeeID='" + lv.EmployeeID + "' and LeaveID='CO' and Status=1)"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "LeaveEarning", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); status = false; } return(status); }
public Boolean ApproveCancelRequestCO(leaveapprove lv, int top) { Boolean status = true; string utString = ""; string updateSQL = ""; try { updateSQL = "update LeaveRequest set Remarks='" + lv.remarks + "', " + "documentstatus=99,LeaveRequestStatus=5, Status=98 ,ApproveUser='******' " + "where UserID in (select userid from ViewUserEmployeeList where EmployeeID = '" + lv.EmployeeID + "') and RowID='" + lv.rowid + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("approve", "LeaveRequest", "", updateSQL) + Main.QueryDelimiter; updateSQL = "update LeaveEarning set Status=1 where RowID in" + "(select top " + top + " rowid from LeaveEarning where EmployeeID = '" + lv.EmployeeID + "'" + " and LeaveID = 'CO' and Status = 2 order by date desc)"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("approve", "LeaveRequest", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); status = false; } return(status); }
public List <leaveapprove> getFilteredLeaveApproval(string userList, int opt) { leaveapprove lvpr; List <leaveapprove> LeaveApr = new List <leaveapprove>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query1 = "select b.EmployeeID,b.Name,c.Description, a.FromDate,a.ToDate,a.DocumentStatus," + "a.Status,a.LeaveRequestStatus,a.Remarks,a.ForwardUser,a.ForwarderList,a.ApproveUser,a.CreateTime,c.LeaveID,a.RowID,a.SanctionedFromDate,a.SanctionedToDate,c.SanctionType" + " from LeaveRequest a, Employee b,LeaveType c, ERPUser d" + " where b.EmployeeID = d.EmployeeID and a.LeaveID = c.LeaveID and a.UserID = d.UserID and a.Status = 1 and a.DocumentStatus between 2 and 99" + " and a.LeaveRequestStatus in (1,2,4,5,8) and a.ForwardUser = '******'"; string query2 = "select b.EmployeeID,b.Name,c.Description, a.FromDate,a.ToDate,a.DocumentStatus, " + " a.Status,a.LeaveRequestStatus,a.Remarks,a.ForwardUser,a.ForwarderList,a.ApproveUser,a.CreateTime,c.LeaveID,a.RowID,a.SanctionedFromDate,a.SanctionedToDate,c.SanctionType " + " from LeaveRequest a, Employee b,LeaveType c, ERPUser d " + " where b.EmployeeID = d.EmployeeID and a.LeaveID = c.LeaveID and a.UserID = d.UserID and a.Status = 1 and a.DocumentStatus between 2 and 99 " + " and a.LeaveRequestStatus in (1,2,4,5) and ForwarderList like '%" + userList + "%' and ForwardUser <> '" + Login.userLoggedIn + "'"; string query3 = "select b.EmployeeID,b.Name,c.Description, a.FromDate,a.ToDate,a.DocumentStatus, " + "a.Status,a.LeaveRequestStatus,a.Remarks,a.ForwardUser,a.ForwarderList,a.ApproveUser,a.CreateTime,c.LeaveID,a.RowID,a.SanctionedFromDate,a.SanctionedToDate,c.SanctionType " + " from LeaveRequest a, Employee b,LeaveType c, ERPUser d where b.EmployeeID = d.EmployeeID " + " and a.LeaveID = c.LeaveID and a.UserID = d.UserID and a.Status <> 2 and " + " a.LeaveRequestStatus not in (4,8) and " + " ForwarderList like '%" + userList + "%'"; 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 leaveapprove(); lvpr.EmployeeID = reader.GetString(0); lvpr.EmployeeName = reader.GetString(1); lvpr.Leavetype = reader.GetString(2); lvpr.fromdate = reader.GetDateTime(3); lvpr.todate = reader.GetDateTime(4); lvpr.documentStatus = reader.GetInt32(5); lvpr.status = reader.GetInt32(6); lvpr.leavestatus = reader.GetInt32(7); lvpr.remarks = reader.GetString(8); lvpr.ForwardUser = reader.GetString(9); lvpr.ForwarderList = reader.GetString(10); lvpr.ApproveUser = reader.IsDBNull(11) ? "" : reader.GetString(11); lvpr.CreateTime = reader.GetDateTime(12); lvpr.leaveid = reader.GetString(13); lvpr.rowid = reader.GetInt32(14); lvpr.sanctionedFrom = reader.IsDBNull(15) ? DateTime.Now : reader.GetDateTime(15); lvpr.sanctionedTo = reader.IsDBNull(16) ? DateTime.Now : reader.GetDateTime(16); lvpr.sanctiontype = reader.GetInt32(17); LeaveApr.Add(lvpr); } conn.Close(); } catch (Exception ex) { MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error"); } return(LeaveApr); }