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