public List <reportattendence> getEmployeeList() { reportattendence lob; List <reportattendence> lobList = new List <reportattendence>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select EmployeeID,Name from ViewEmployeeDetails where OfficeID='NVP' and status=1 order by Name "; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lob = new reportattendence(); lob.EmployeeID = reader.GetString(0); lob.EmployeeName = reader.GetString(1); lobList.Add(lob); } conn.Close(); } catch (Exception) { MessageBox.Show("Error querying Employee List"); } return(lobList); }
public List <reportattendence> getcompanydata() { reportattendence lst; List <reportattendence> lobList = new List <reportattendence>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = " select a.DataID,a.DataValue from CompanyData a where a.DataID in ('OfficeOpenTime','OfficeCloseTime')"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lst = new reportattendence(); lst.DataID = reader.GetString(0); lst.DataValue = reader.GetString(1); lobList.Add(lst); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying company Data List"); } return(lobList); }
//public Boolean getEmployeeEarlyList(string date, string empid) //{ // bool status = false; // try // { // SqlConnection conn = new SqlConnection(Login.connString); // string query = " select distinct e.EmployeeID,a.DataValue,CAST(b.Edatetime as time)time " + // "from BiometricAttendance e, (select a.DataValue from CompanyData a " + // "where a.DataID = 'OfficeEndTime') a, (select top 1 c.Edatetime, c.EmployeeID " + // "from BiometricAttendance c where cast(c.Edatetime as date) = '" + date + "' and " + // "c.EmployeeID = '" + empid + "' order by cast(c.Edatetime as time) Desc)b where " + // " a.DataValue > CAST(b.Edatetime as time) and e.EmployeeID = b.EmployeeID "; // SqlCommand cmd = new SqlCommand(query, conn); // conn.Open(); // SqlDataReader reader = cmd.ExecuteReader(); // if (reader.Read()) // { // status = true; // } // conn.Close(); // } // catch (Exception ex) // { // MessageBox.Show("Error querying Employee Early List"); // } // return status; //} //public Boolean getEmployeeLateList(string date, string empid) //{ // bool status = false; // try // { // SqlConnection conn = new SqlConnection(Login.connString); // string query = "select distinct e.EmployeeID,a.DataValue,CAST(b.Edatetime as time)time " + // "from BiometricAttendance e, (select a.DataValue from CompanyData a " + // "where a.DataID = 'OfficeStartTime') a, (select top 1 c.Edatetime, c.EmployeeID from " + // "BiometricAttendance c where cast(c.Edatetime as date) = '" + date + "' and " + // "c.EmployeeID = '" + empid + "' order by cast(c.Edatetime as time) Asc)b where " + // "a.DataValue < CAST(b.Edatetime as time) and e.EmployeeID = b.EmployeeID "; // SqlCommand cmd = new SqlCommand(query, conn); // conn.Open(); // SqlDataReader reader = cmd.ExecuteReader(); // if (reader.Read()) // { // status = true; // } // conn.Close(); // } // catch (Exception ex) // { // MessageBox.Show("Error querying Employee Late List"); // } // return status; //} public reportattendence getEmployeeLatenEarlyList(string date, string empid) { reportattendence lobList = new reportattendence(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = " select distinct d.EmployeeID,a.Edatetime,b.Edatetime from BiometricAttendance d, " + " (select top 1 c.Edatetime, c.EmployeeID from BiometricAttendance c where " + " cast(c.Edatetime as date) = '" + date + "' and c.EmployeeID = '" + empid + "' order by cast(c.Edatetime as time) Asc) a, " + " (select top 1 c.Edatetime,c.EmployeeID from BiometricAttendance c where " + " cast(c.Edatetime as date) = '" + date + "' and c.EmployeeID = '" + empid + "' order by cast(c.Edatetime as time) desc ) b " + " where d.EmployeeID = a.EmployeeID and d.EmployeeID = b.EmployeeID"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { lobList.EmployeeID = reader.GetString(0); lobList.EntryTime = reader.GetDateTime(1); lobList.ExitTime = reader.GetDateTime(2); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Employee Late and Early List"); } return(lobList); }
public List <reportattendence> getMrList(string mon, string yr) { reportattendence lv; List <reportattendence> leave = new List <reportattendence>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select mr.EmployeeID,mr.OutTime,mr.InTime,mr.ExitTimePlanned,mr.ReturnTimePlanned from MovementRegister mr where mr.Status = 1 " + " and mr.DocumentStatus in (99,3,4) and datepart(yy, mr.OutTime)= '" + yr + "' and datepart(MM, mr.OutTime) = '" + mon + "'"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lv = new reportattendence(); lv.EmployeeID = reader.GetString(0); lv.outime = reader.GetDateTime(1); lv.Intime = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2); lv.PlannedoutTime = reader.GetDateTime(3); lv.PlannedIntime = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4); leave.Add(lv); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Employee Leave List"); } return(leave); }
public List <reportattendence> getEmployeeLeaveList(string mon, string yr) { reportattendence lv; List <reportattendence> leave = new List <reportattendence>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select e.EmployeeID,Lr.SanctionedFromDate,Lr.SanctionedToDate from LeaveRequest Lr ,ERPUser e " + "where Lr.Status = 1 and Lr.DocumentStatus = 99 and Lr.LeaveRequestStatus in (1,9,11,4,6) and " + " lr.UserID = e.UserID and datepart(yy, Lr.SanctionedToDate)= '" + yr + "' and " + " (DATEPART(MM, Lr.SanctionedFromDate) = '" + mon + "' or DATEPART(MM, Lr.SanctionedToDate) = '" + mon + "')"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lv = new reportattendence(); lv.EmployeeID = reader.GetString(0); lv.fromdate = reader.GetDateTime(1); lv.todate = reader.GetDateTime(2); leave.Add(lv); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Employee Leave List"); } return(leave); }
//public Boolean getEmployeePresentList(string date, string empid) //{ // bool status = false; // try // { // SqlConnection conn = new SqlConnection(Login.connString); // string query = "select distinct a.EmployeeID from BiometricAttendance a where cast(a.Edatetime as date)='" + date + "' and a.EmployeeID='" + empid + "' "; // SqlCommand cmd = new SqlCommand(query, conn); // conn.Open(); // SqlDataReader reader = cmd.ExecuteReader(); // if (reader.Read()) // { // status = true; // } // conn.Close(); // } // catch (Exception ex) // { // MessageBox.Show("Error querying Employee Present List"); // } // return status; //} public List <reportattendence> getEmployeePresentListnew(string mon, string yr) { reportattendence lv; List <reportattendence> prsnt = new List <reportattendence>(); try { SqlConnection conn = new SqlConnection(Login.connString); string query = "select a.EmployeeID,a.Edatetime from BiometricAttendance a where datepart(yy,a.Edatetime)='" + yr + "' and datepart(MM,a.Edatetime)='" + mon + "' "; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { lv = new reportattendence(); lv.EmployeeID = reader.GetString(0); lv.Etime = reader.GetDateTime(1); prsnt.Add(lv); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Employee Present List"); } return(prsnt); }