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