public bool isEmployeePresent(int EmpID, string Email, int id, string action) { using (DBService dbservice = new BLL.DBService()) { string SelectQueryEmpID = ""; if (action == "create") { SelectQueryEmpID = "select * from Employee where EmployeeID=" + EmpID + " or Email='" + Email + "';"; } else if (action == "update") { SelectQueryEmpID = "select * from Employee where (EmployeeID=" + EmpID + " or Email='" + Email + "') and ID not in(" + id + ");"; } DataTable ds = new DataTable(); using (OleDbDataAdapter adapter = new OleDbDataAdapter(SelectQueryEmpID, dbservice.conn)) { adapter.Fill(ds); } if (ds.Rows.Count == 0) { return(false); } else { return(true); } } }
public bool isEMployeeIDChanged(int ID, int pEmpID) { using (DBService dbservice = new BLL.DBService()) { string SelectQueryEmpID = "select EmployeeID from Employee where ID=" + ID + ";"; DataTable ds = new DataTable(); using (OleDbDataAdapter adapter = new OleDbDataAdapter(SelectQueryEmpID, dbservice.conn)) { adapter.Fill(ds); } if (ds.Rows.Count > 0) { int empID = ds.Rows[0]["EmployeeID"] != null?Convert.ToInt32(ds.Rows[0]["EmployeeID"]) : -1; if (empID != pEmpID) { return(true); } else { return(false); } } else { return(false); } } }
public Employee GetEmployeebyId(int id) { using (DBService dbservice = new BLL.DBService()) { using (DataTable dt = new DataTable()) { string selectQuery = "SELECT * FROM Employee where ID=" + id.ToString() + ";"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, dbservice.conn)) { adapter.Fill(dt); } Employee usr = null; if (dt != null) { usr = new Employee(); foreach (DataRow dr in dt.Rows) { usr.DepartmentId = dr["DepartmentID"] != null?Convert.ToInt32(dr["DepartmentID"]) : -1; usr.EmployeeName = dr["EmployeeName"].ToString(); usr.EmployeeID = dr["EmployeeID"] != null?Convert.ToInt32(dr["EmployeeID"]) : -1; usr.Id = dr["ID"] != null?Convert.ToInt32(dr["ID"]) : -1; usr.Email = dr["Email"].ToString(); usr.Birthdate = dr["Birthdate"] != null?Convert.ToDateTime(dr["Birthdate"]) : (DateTime?)null; usr.PhotoLocation = dr["PhotoLocation"].ToString(); } } return(usr); } } }
public DistributionList GetDistributionListByDepartmentId(int DepartmentId) { using (DBService dbservice = new BLL.DBService()) { using (DataTable dt = new DataTable()) { string selectQuery = "SELECT DS.*, d.DepartmentName FROM DistributionList DS inner join Department d on DS.DepartmentId=d.ID where DepartmentId=" + DepartmentId.ToString() + ";"; //SELECT L.* , d.DepartmentName FROM LoginUser L inner join Department d on L.DepartmentID=d.ID where L.ID=" + id.ToString() + ";"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, dbservice.conn)) { adapter.Fill(dt); } DistributionList usr = null; if (dt.Rows.Count > 0) { usr = new DistributionList(); foreach (DataRow dr in dt.Rows) { usr.DepartmentId = dr["DepartmentID"] != null?Convert.ToInt32(dr["DepartmentID"]) : -1; usr.Recepients = dr["Recepients"].ToString(); usr.Department.DepartmentName = dr["DepartmentName"].ToString(); usr.IsIncludeUsers = Convert.ToBoolean(dr["IsIncludeUsers"]); usr.ID = dr["ID"] != null?Convert.ToInt32(dr["ID"]) : -1; } } return(usr); } } }
public int EditDistributionList(DistributionList dsList) { using (DBService dbservice = new BLL.DBService()) { DistributionList ds = GetDistributionListByDepartmentId(dsList.DepartmentId); if (ds != null) { string updateQuery = "UPDATE DistributionList SET [Recepients]='" + dsList.Recepients + "', [IsIncludeUsers]=" + dsList.IsIncludeUsers + " WHERE Departmentid=" + dsList.DepartmentId + ";"; using (OleDbCommand cmd = new OleDbCommand(updateQuery, dbservice.conn)) { return(cmd.ExecuteNonQuery()); } } else { string insertQuery = "INSERT INTO DistributionList (Recepients,IsIncludeUsers,DepartmentID) VALUES ('" + dsList.Recepients + "'," + dsList.IsIncludeUsers + "," + dsList.DepartmentId + ");"; using (OleDbCommand cmd = new OleDbCommand(insertQuery, dbservice.conn)) { return(cmd.ExecuteNonQuery()); } } } }
public List <Department> GetAllDepartments() { using (DBService dbservice = new BLL.DBService()) { using (DataTable dt = new DataTable()) { string selectQuery = "SELECT * FROM Department;"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, dbservice.conn)) { adapter.Fill(dt); } List <Department> lstDept = null; if (dt != null) { lstDept = new List <Department>(); foreach (DataRow dr in dt.Rows) { Department d = new Department(); d.DeptName = dr["DeptName"].ToString(); d.ShortName = dr["ShortName"].ToString(); d.Id = dr["ID"] != null?Convert.ToInt32(dr["ID"]) : -1; lstDept.Add(d); } } return(lstDept); } } }
public List <UserProfile> GetAllProfiles() { using (DBService dbservice = new BLL.DBService()) { using (DataTable dt = new DataTable()) { string selectQuery = "SELECT * FROM UserProfile;"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, dbservice.conn)) { adapter.Fill(dt); } List <UserProfile> lstprofiles = null; if (dt != null) { lstprofiles = new List <UserProfile>(); foreach (DataRow dr in dt.Rows) { UserProfile d = new UserProfile(); d.Code = dr["Code"].ToString(); d.ProfileName = dr["ProfileName"].ToString(); d.Id = dr["ID"] != null?Convert.ToInt32(dr["ID"]) : -1; lstprofiles.Add(d); } } return(lstprofiles); } } }
public LoginUser GetLoginUserbyId(int id) { using (DBService dbservice = new BLL.DBService()) { using (DataTable dt = new DataTable()) { string selectQuery = "SELECT L.* , d.DeptName FROM LoginUser L inner join Department d on L.DeptID=d.ID where L.ID=" + id.ToString() + ";"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, dbservice.conn)) { adapter.Fill(dt); } LoginUser usr = null; if (dt != null) { usr = new LoginUser(); foreach (DataRow dr in dt.Rows) { usr.DeptId = dr["DeptID"] != null?Convert.ToInt32(dr["DeptID"]) : -1; usr.DeptName = dr["DeptName"].ToString(); usr.UserCode = dr["UserCode"].ToString(); usr.UserProfileId = dr["UserProfileId"] != null?Convert.ToInt32(dr["UserProfileId"]) : -1; usr.Id = dr["ID"] != null?Convert.ToInt32(dr["ID"]) : -1; usr.Password = dr["Password"].ToString(); } } return(usr); } } }
public List <Employee> GetAllEmployeeByDepartmentID(int DepartmentID) { using (DBService dbservice = new BLL.DBService()) { using (DataTable dt = new DataTable()) { string selectQuery = "SELECT * FROM Employee where DepartmentID=" + DepartmentID + ";"; return(FillEmployeeList(dbservice, dt, selectQuery)); } } }
public int UpdateEmployeePhotoLocation(string path, int id) { using (DBService dbservice = new BLL.DBService()) { string updateQuery = "UPDATE Employee SET [PhotoLocation]='" + path + "'WHERE id=" + id + ";"; using (OleDbCommand cmd = new OleDbCommand(updateQuery, dbservice.conn)) { return(cmd.ExecuteNonQuery()); } } }
public int DeleteLognUserbyId(int ID) { using (DBService dbservice = new BLL.DBService()) { string DeleteQuery = "Delete from LoginUser where ID=" + ID; using (OleDbCommand cmd = new OleDbCommand(DeleteQuery, dbservice.conn)) { return(cmd.ExecuteNonQuery()); } } }
public List <Employee> GetAllEmployee() { using (DBService dbservice = new BLL.DBService()) { using (DataTable dt = new DataTable()) { string selectQuery = "SELECT * FROM Employee;"; return(FillEmployeeList(dbservice, dt, selectQuery)); } } }
public bool isAuthenticate(string login, string pwd) { using (DBService dbservice = new BLL.DBService()) { using (DataTable dt = new DataTable()) { string selectQuery = @"SELECT L.* , d.DeptName,p.ProfileName FROM ((LoginUser L inner join Department d on L.DeptID=d.ID) inner join UserProfile p on L.UserProfileId=p.ID) where L.UserCode ='" + login + "' and L.Password='******' ;"; // // // selects all content from table and adds it to datatable binded to datagridview using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, dbservice.conn)) { adapter.Fill(dt); } List <LoginUser> lstLoginUsers = null; if (dt != null) { lstLoginUsers = new List <LoginUser>(); foreach (DataRow dr in dt.Rows) { LoginUser usr = new LoginUser(); usr.DeptId = dr["DeptID"] != null?Convert.ToInt32(dr["DeptID"]) : -1; usr.DeptName = dr["DeptName"].ToString(); usr.UserCode = dr["UserCode"].ToString(); usr.UserProfileId = dr["UserProfileId"] != null?Convert.ToInt32(dr["UserProfileId"]) : -1; usr.ProfileName = dr["ProfileName"].ToString(); usr.Id = dr["ID"] != null?Convert.ToInt32(dr["ID"]) : -1; usr.Password = dr["Password"].ToString(); lstLoginUsers.Add(usr); } } var loginuser = lstLoginUsers.FirstOrDefault(); if (loginuser != null) { using (SessionService ss = new SessionService()) { ss.CurrentUser = loginuser; } return(true); } else { return(false); } } } }
public List <LoginUser> GetAllLoginUsers() { using (DBService dbservice = new BLL.DBService()) { using (DataTable dt = new DataTable()) { SessionService ss = new BLL.SessionService(); string selectQuery = ""; if (ss.CurrentUser.ProfileName == "IT administrator") { selectQuery = @"SELECT L.* , d.DeptName,p.ProfileName FROM ((LoginUser L inner join Department d on L.DeptID=d.ID) inner join UserProfile p on L.UserProfileId=p.ID);"; } else { selectQuery = @"SELECT L.* , d.DeptName,p.ProfileName FROM ((LoginUser L inner join Department d on L.DeptID=d.ID) inner join UserProfile p on L.UserProfileId=p.ID ) where p.ProfileName <> 'IT administrator';"; } // // // selects all content from table and adds it to datatable binded to datagridview using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, dbservice.conn)) { adapter.Fill(dt); } List <LoginUser> lstLoginUsers = null; if (dt != null) { lstLoginUsers = new List <LoginUser>(); foreach (DataRow dr in dt.Rows) { LoginUser usr = new LoginUser(); usr.DeptId = dr["DeptID"] != null?Convert.ToInt32(dr["DeptID"]) : -1; usr.DeptName = dr["DeptName"].ToString(); usr.UserCode = dr["UserCode"].ToString(); usr.UserProfileId = dr["UserProfileId"] != null?Convert.ToInt32(dr["UserProfileId"]) : -1; usr.ProfileName = dr["ProfileName"].ToString(); usr.Id = dr["ID"] != null?Convert.ToInt32(dr["ID"]) : -1; usr.Password = dr["Password"].ToString(); lstLoginUsers.Add(usr); } } return(lstLoginUsers); } } }
public int EditLoginUser(LoginUser LgUser) { using (DBService dbservice = new BLL.DBService()) { string updateQuery = "UPDATE LoginUser SET [UserCode]='" + LgUser.UserCode + "', [Password]='" + LgUser.Password + "',[DeptID]=" + LgUser.DeptId + " WHERE id=" + LgUser.Id + ";"; if (isUserPresent(LgUser.UserCode, LgUser.DeptId)) { return(-1); } else { using (OleDbCommand cmd = new OleDbCommand(updateQuery, dbservice.conn)) { return(cmd.ExecuteNonQuery()); } } } }
public int AddLoginUser(LoginUser loginUser) { using (DBService dbservice = new BLL.DBService()) { string insertQuery = "INSERT INTO LoginUser ([UserCode],[Password],[UserProfileID],[DeptID]) VALUES ('" + loginUser.UserCode + "','" + loginUser.Password + "','" + loginUser.UserProfileId + "','" + loginUser.DeptId + "');"; if (isUserPresent(loginUser.UserCode, loginUser.DeptId)) { return(-1); } else { using (OleDbCommand cmd = new OleDbCommand(insertQuery, dbservice.conn)) { return(cmd.ExecuteNonQuery()); } } } }
public int EditEmployee(Employee emp) { using (DBService dbservice = new BLL.DBService()) { string updateQuery = "UPDATE Employee SET [EmployeeName]='" + emp.EmployeeName + "', [EmployeeID]=" + emp.EmployeeID + ",[Email]='" + emp.Email + "',[Birthdate]='" + emp.Birthdate + "' WHERE id=" + emp.Id + ";"; if (isEmployeePresent(emp.EmployeeID, emp.Email, emp.Id, "update")) { return(-1); } else { using (OleDbCommand cmd = new OleDbCommand(updateQuery, dbservice.conn)) { return(cmd.ExecuteNonQuery()); } } } }
public int AddDepartment(Department objDept) { using (DBService dbservice = new BLL.DBService()) { string insertQuery = "INSERT INTO Department (DeptName,ShortName) VALUES ('" + objDept.DeptName + "','" + objDept.ShortName + "'); "; if (isDepartmentPresent(objDept.DeptName, objDept.ShortName)) { return(-1); } else { using (OleDbCommand cmd = new OleDbCommand(insertQuery, dbservice.conn)) { return(cmd.ExecuteNonQuery()); } //Add distribution list while creating department } } }
public bool isUserPresent(string userCode, int deptid) { using (DBService dbservice = new BLL.DBService()) { string SelectQueryEmpID = "select * from LoginUser where DeptID=" + deptid + " and UserCode='" + userCode + "';"; DataTable ds = new DataTable(); using (OleDbDataAdapter adapter = new OleDbDataAdapter(SelectQueryEmpID, dbservice.conn)) { adapter.Fill(ds); } if (ds.Rows.Count == 0) { return(false); } else { return(true); } } }
public int DeleteEmployee(int EmpID) { using (DBService dbservice = new BLL.DBService()) { string DeleteQuery = "Delete from Employee where ID=" + EmpID; using (OleDbCommand cmd = new OleDbCommand(DeleteQuery, dbservice.conn)) { int retVal = cmd.ExecuteNonQuery(); if (retVal > 0) { string photoPath = Path.Combine(System.Configuration.ConfigurationManager.AppSettings["PhotoLocation"].ToString(), EmpID.ToString() + ".jpg"); if (!File.Exists(photoPath)) { photoPath = Path.Combine(System.Configuration.ConfigurationManager.AppSettings["PhotoLocation"].ToString(), EmpID.ToString() + ".png"); if (!File.Exists(photoPath)) { photoPath = Path.Combine(System.Configuration.ConfigurationManager.AppSettings["PhotoLocation"].ToString(), EmpID.ToString() + ".jpeg"); if (File.Exists(photoPath)) { File.Delete(photoPath); } } else { File.Delete(photoPath); } } else { File.Delete(photoPath); } } return(retVal); } } }
public int AddEmployee(Employee emp) { using (DBService dbservice = new BLL.DBService()) { string insertQuery = "INSERT INTO Employee (EmployeeName,EmployeeID,Email,Birthdate,DepartmentID) VALUES ('" + emp.EmployeeName + "','" + emp.EmployeeID + "','" + emp.Email + "','" + emp.Birthdate + "','" + emp.DepartmentId + "');"; string selectQuery = "Select @@Identity"; int id = -1; if (isEmployeePresent(emp.EmployeeID, emp.Email, 0, "create")) { return(id); } else { using (OleDbCommand cmd = new OleDbCommand(insertQuery, dbservice.conn)) { cmd.ExecuteNonQuery(); cmd.CommandText = selectQuery; id = (int)cmd.ExecuteScalar(); return(id); } } } }
public bool isDepartmentPresent(string deptName, string ShortName) { using (DBService dbservice = new BLL.DBService()) { string SelectQueryEmpID = ""; SelectQueryEmpID = "select * from Department where DeptName='" + deptName + "' and ShortName='" + ShortName + "';"; DataTable ds = new DataTable(); using (OleDbDataAdapter adapter = new OleDbDataAdapter(SelectQueryEmpID, dbservice.conn)) { adapter.Fill(ds); } if (ds.Rows.Count == 0) { return(false); } else { return(true); } } }