public static string downloadOhListForHRMSUpload() { string ohList = ""; int ohYear = Int32.Parse((ConfigurationManager.AppSettings["ohYear"])); try { var employeesOHQuery = "SELECT CONVERT(VARCHAR(10), cast(Split.a.value('.', 'NVARCHAR(MAX)')AS DATETIME), 121) [Date], 'Optional Holiday' Holiday" + " , MONTH(Split.a.value('.', 'NVARCHAR(MAX)')) Month, YEAR(Split.a.value('.', 'NVARCHAR(MAX)')) Year, RTRIM(LTRIM(A.emp_Code)) EmpCode, a.NAME AS EmployeeName" + " FROM(SELECT OL.emp_Code, CAST('<X>' + REPLACE([Holidays], ',', '</X><X>') + '</X>' AS XML) AS String, U.FirstName + ' ' + ISNULL(U.MiddleName, '') + ' ' + ISNULL(U.LastName, '') AS NAME" + " FROM[dbo].[Ohlog] OL" + " join [dbo].[User] U on U.EmployeeCode = OL.emp_Code where OL.Year = " + ohYear + " and U.IsActive = 1 " + " ) AS A" + " CROSS APPLY String.nodes('/X') AS Split(a)" + " order by A.NAME, Date"; DataTable dtEmployeesOH = BaseDAL.ExecuteDataTable(employeesOHQuery); ohList = DataTableToCSV(dtEmployeesOH, ','); } catch (Exception ex) { Logger.Error(ex.Message, ex); } return(ohList); }
// Method to active or inactive the user... private void MakeActiveorInactive(int userDeleteId) { SqlParameter[] parameters = new SqlParameter[15]; parameters[0] = new SqlParameter("@FirstName", ""); parameters[1] = new SqlParameter("@LastName", ""); parameters[2] = new SqlParameter("@UserName", ""); parameters[3] = new SqlParameter("@Password", ""); parameters[4] = new SqlParameter("@EmailId", ""); parameters[5] = new SqlParameter("@Status", 'D'); parameters[6] = new SqlParameter("@UpdatedBy", Session["UserID"]); parameters[7] = new SqlParameter("@UpdatedDate", DateTime.Now); parameters[8] = new SqlParameter("@CreatedBy", Session["UserID"]); parameters[9] = new SqlParameter("@Createddate", DateTime.Now); parameters[10] = new SqlParameter("@middleName", ""); parameters[11] = new SqlParameter("@userID", userDeleteId); parameters[12] = new SqlParameter("@IsActive", 1); parameters[13] = new SqlParameter("@UserRoleID", 1); parameters[14] = new SqlParameter("@EmployeeCode", ""); parameters[5].Direction = ParameterDirection.InputOutput; BaseDAL.ExecuteSPNonQuery("[UserOperations]", parameters); DisplayUserDetailsList(); // Method to Display User Details List... lbMessage.Text = PMOscar.Core.Constants.AddRole.STATUS; }
public static string downloadEmployeeHolidaysDetail() { string ohList = ""; int ohYear = Int32.Parse((ConfigurationManager.AppSettings["ohYear"])); try { var employeesQuery = "SELECT U.FirstName+' '+ISNULL(U.MiddleName,'')+' '+ISNULL(U.LastName,'') AS NAME, OH.Holidays, len(OH.Holidays) -len(replace(OH.Holidays,',',''))+ case when Holidays like '%NULL%' then 0 else 1 end as LeaveTotal" + " FROM [dbo].[User] U" + " LEFT JOIN [dbo].[OhLog] OH ON U.EmployeeCode = OH.emp_Code and OH.Year = " + ohYear + " where U.IsActive = 1 ORDER BY U.FirstName"; DataTable dtEmployees = BaseDAL.ExecuteDataTable(employeesQuery); var leavesPerDayQuery = "SELECT [Holiday], COUNT(*) AS [Count] FROM(SELECT Split.a.value('.', 'NVARCHAR(MAX)')[Holiday]" + " FROM(SELECT CAST('<X>' + REPLACE([Holidays], ',', '</X><X>') + '</X>' AS XML) AS String FROM Ohlog) AS A" + " CROSS APPLY String.nodes('/X') AS Split(a)) AS O" + " GROUP BY[Holiday]"; DataTable dtLeavesPerDay = BaseDAL.ExecuteDataTable(leavesPerDayQuery); ohList = DetailedDataTableToCSV(dtEmployees, dtLeavesPerDay); } catch (Exception ex) { Logger.Error(ex.Message, ex); } return(ohList); }
public static string downloadEmployeeHolidays() { string ohList = ""; try { int noOfOptionalHolidays = Int32.Parse((ConfigurationManager.AppSettings["noOfOptionalHolidays"])); int ohYear = Int32.Parse((ConfigurationManager.AppSettings["ohYear"])); var query = "select row_number() over (order by len(o.Holidays) - len(replace(o.Holidays, ',', '')) + 1 desc, u.UserName) as SerialNo,UPPER(u.FirstName) as FirstName,UPPER(u.LastName) as LastName, u.UserName as Email,u.EmployeeCode, len(o.Holidays) - len(replace(o.Holidays, ',', '')) + 1 as AppliedHolidayCount"; for (var i = 1; i <= noOfOptionalHolidays; i++) { query = query + "," + "dbo.CSVParser(o.Holidays," + i + ") as OH" + i; } query = query + " from[User] u left join OhLog o on u.EmployeeCode = o.emp_Code and o.Year = " + ohYear + " where u.IsActive = 1 order by AppliedHolidayCount desc, u.UserName"; DataTable dt1 = BaseDAL.ExecuteDataTable(query); ohList = DataTableToCSV(dt1, ','); } catch (Exception ex) { Logger.Error(ex.Message, ex); } return(ohList); }
public static bool saveOptionalHolidays(string[] oh, string year) { var userId = HttpContext.Current.Session[Constants.SessionName.USERID]; string empQuery = string.Format("select EmployeeCode from [dbo].[User] where UserId='{0}'", userId); Object obj = BaseDAL.ExecuteScalar(empQuery); var empCode = obj.ToString(); var query = "Select * From dbo.OhLog Where emp_Code =" + empCode + " And Year =" + year; DataTable dt1 = BaseDAL.ExecuteDataTable(query); if (dt1.Rows.Count > 0) { //already entry for employee return(false); } else { try { var OhString = string.Join(",", oh); string query1 = string.Format("insert into [dbo].[OhLog]([Year],[emp_Code],[Holidays]) values('{0}','{1}','{2}')", year, empCode, OhString); int result = BaseDAL.ExecuteNonQuery(query1); if (result > 0) { return(true); } return(false); } catch (Exception ex) { return(false); } } }
public void BindGridView() { SqlParameter[] parameter = new SqlParameter[6]; if (ResourceId == 0 && ProjectID == 0) { parameter[0] = new SqlParameter("@ResourceID", _resourceID == null ? "0" : _resourceID); parameter[1] = new SqlParameter("@dayfrom", _dayFrom == null ? "" : _dayFrom); parameter[2] = new SqlParameter("@dayto", _dayTo == null ? "" : _dayTo); parameter[3] = new SqlParameter("@Year", Session["Year"]); parameter[4] = new SqlParameter("@Status", _Status); parameter[5] = new SqlParameter("@ProjectID", _projectId); } else //int year = Convert.ToInt16(Session["Year"]); { parameter[0] = new SqlParameter("@ResourceID", ResourceId); parameter[1] = new SqlParameter("@dayfrom", dayfrom); parameter[2] = new SqlParameter("@dayto", dayto); parameter[3] = new SqlParameter("@Year", Session["Year"]); parameter[4] = new SqlParameter("@Status", Status); parameter[5] = new SqlParameter("@ProjectID", ProjectID); } DataSet dsProjectDetails = BaseDAL.ExecuteSPDataSet("GetTimeTrackerAudit", parameter); if (dsProjectDetails != null && dsProjectDetails.Tables.Count > 0) { gvTimeTrackerAudit.DataSource = dsProjectDetails.Tables[0] as DataTable; gvTimeTrackerAudit.DataBind(); } }
private void BindGridView() { SqlParameter[] parameter = new SqlParameter[2]; parameter[0] = new SqlParameter("@ProjectId", EstAudProjectId); parameter[1] = new SqlParameter("@Year", 1); DataSet dsProjectDetails = BaseDAL.ExecuteSPDataSet("GetProjectAudit", parameter); gvProjectAudit.DataSource = dsProjectDetails.Tables[0] as DataTable; gvProjectAudit.DataBind(); }
// Method to bind the roles in the DropDownList private void BindDropDownRole() { parameter = new List <SqlParameter>(); DataTable dt = BaseDAL.ExecuteSPDataTable("GetUserRole", parameter); ddlRole.DataSource = dt; ddlRole.DataTextField = "UserRole"; ddlRole.DataValueField = "UserRoleID"; ddlRole.DataBind(); ddlRole.Items.Insert(0, new ListItem("Select", "0")); }
// Method to add the user details private string InsertUser(string FirstName, string LastName, string UserName, string Password, string EmailId, string MiddleName, int roleID, string EmpCode) { int radioButtonStatus = 0; if (RdActive.Checked == true) { radioButtonStatus = 1; } else { radioButtonStatus = 0; } string status = "I"; try { FirstName = FirstName.Replace("<script", "[script").Replace("</script>", "[/script]"); LastName = LastName.Replace("<script", "[script").Replace("</script>", "[/script]"); UserName = UserName.Replace("<script", "[script").Replace("</script>", "[/script]"); Password = PMOscar.BaseDAL.EncryptText(Password); EmailId = EmailId.Replace("<script", "[script").Replace("</script>", "[/script]"); MiddleName = MiddleName.Replace("<script", "[script").Replace("</script>", "[/script]"); SqlParameter[] parameters = new SqlParameter[15]; parameters[0] = new SqlParameter("@FirstName", FirstName); parameters[1] = new SqlParameter("@LastName", LastName); parameters[2] = new SqlParameter("@UserName", UserName); parameters[3] = new SqlParameter("@Password", Password); parameters[4] = new SqlParameter("@EmailId", EmailId); parameters[5] = new SqlParameter("@Status", status); parameters[6] = new SqlParameter("@UpdatedBy", Session["UserID"]); parameters[7] = new SqlParameter("@UpdatedDate", DateTime.Now); parameters[8] = new SqlParameter("@CreatedBy", Session["UserID"]); parameters[9] = new SqlParameter("@Createddate", DateTime.Now); parameters[10] = new SqlParameter("@middleName", MiddleName); parameters[11] = new SqlParameter("@userID", 1); parameters[12] = new SqlParameter("@IsActive", radioButtonStatus); parameters[13] = new SqlParameter("@UserRoleID", roleID); parameters[14] = new SqlParameter("@EmployeeCode", EmpCode); parameters[5].Direction = ParameterDirection.InputOutput; BaseDAL.ExecuteSPNonQuery("[UserOperations]", parameters); status = parameters[5].Value.ToString(); return(status); } catch (Exception ex) { return(status); throw ex; } }
public void BindGridView() { EstAudProjectId = Convert.ToInt32(Session["AudProjectId"]); SqlParameter[] parameter = new SqlParameter[2]; parameter[0] = new SqlParameter("@ProjectId", EstAudProjectId); parameter[1] = new SqlParameter("@Year", 1); DataTable dt = PMOscar.BaseDAL.ExecuteDataTable("SELECT DeliveryDate,RevisedDeliveryDate FROM Project where ProjectId =" + EstAudProjectId + " "); DataSet dsProjectDetails = BaseDAL.ExecuteSPDataSet("GetProjectAudit", parameter); gvProjectAudit.DataSource = dsProjectDetails.Tables[0] as DataTable; gvProjectAudit.DataBind(); }
public void BindGridView() { EstProjectId = Convert.ToInt32(Session["EstAudProjectId"]); SqlParameter[] parameter = new SqlParameter[2]; parameter[0] = new SqlParameter("@ProjectID", EstProjectId); parameter[1] = new SqlParameter("@Year", 1); DataSet dsProjectDetails = BaseDAL.ExecuteSPDataSet("GetProjectEstimationAudit", parameter); DataTable dt = AddToBugetRevisionDataTable(dsProjectDetails.Tables[0]); gvProjectEstimationAudit.DataSource = dt; //gvProjectEstimationAudit.DataSource = dsProjectDetails.Tables[0] as DataTable; gvProjectEstimationAudit.DataBind(); }
private void BindComboDetails() { parameter = new List <SqlParameter>(); DataTable dt = BaseDAL.ExecuteSPPhaseDataTable("GetPhase", parameter); ddlPhase.DataSource = dt; ddlPhase.DataTextField = "Phase"; ddlPhase.DataValueField = "PhaseID"; ddlPhase.DataBind(); ddlPhase.Items.Insert(0, new ListItem("Select", "0")); dt = PMOscar.BaseDAL.ExecuteDataTable("Select EstimationRoleID,RoleName from EstimationRole "); ddlRole.DataSource = dt; ddlRole.DataTextField = "RoleName"; ddlRole.DataValueField = "EstimationRoleID"; ddlRole.DataBind(); ddlRole.Items.Insert(0, new ListItem("Select", "0")); dt.Dispose(); }
public void BindGridView() { ActProjectId = Convert.ToInt32(Session["ActProjectId"]); ActPhaseId = Convert.ToInt32(Session["ActPhaseId"]); ActEstRoleId = Convert.ToInt32(Session["ActEstRoleId"]); SqlParameter[] parameter = new SqlParameter[3]; parameter[0] = new SqlParameter("@ProjectId", ActProjectId); parameter[1] = new SqlParameter("@PhaseID", ActPhaseId); parameter[2] = new SqlParameter("@EstimationRoleID", ActEstRoleId); DataSet dsProjectDetails = BaseDAL.ExecuteSPDataSet("GetActualHoursHistory", parameter); gvProjectAudit.DataSource = dsProjectDetails.Tables[0] as DataTable; gvProjectAudit.DataBind(); //Session["ActProjectId"] = 0; //Session["ActPhaseId"] = 0; //Session["ActEstRoleId"] = 0; }
// Method to update the user details private string UpdateUser(string FirstName, string LastName, string UserName, string EmailId, string pwd, string middlename, string employeecode) { userEditId = Convert.ToInt32(Request.QueryString["UserEditId"]); int radioButtonStatus = 0; if (rdActive.Checked == true) { radioButtonStatus = 1; } else { radioButtonStatus = 0; } string status = "U"; try { FirstName = FirstName.Replace("<script", "[script").Replace("</script>", "[/script]"); LastName = LastName.Replace("<script", "[script").Replace("</script>", "[/script]"); UserName = UserName.Replace("<script", "[script").Replace("</script>", "[/script]"); pwd = PMOscar.BaseDAL.EncryptText(pwd); EmailId = EmailId.Replace("<script", "[script").Replace("</script>", "[/script]"); middlename = middlename.Replace("<script", "[script").Replace("</script>", "[/script]"); employeecode = employeecode.Replace("<script", "[script").Replace("</script>", "[/script]"); SqlParameter[] parameters = new SqlParameter[15]; parameters[0] = new SqlParameter("@FirstName", FirstName); parameters[1] = new SqlParameter("@LastName", LastName); parameters[2] = new SqlParameter("@UserName", UserName); parameters[3] = new SqlParameter("@Password", pwd); parameters[4] = new SqlParameter("@EmailId", EmailId); parameters[5] = new SqlParameter("@Status", status); parameters[6] = new SqlParameter("@CreatedBy", Session["UserID"]); parameters[7] = new SqlParameter("@Createddate", DateTime.Now); parameters[8] = new SqlParameter("@UpdatedBy", Session["UserID"]); parameters[9] = new SqlParameter("@UpdatedDate", DateTime.Now); parameters[10] = new SqlParameter("@middleName", middlename); parameters[11] = new SqlParameter("@userID", userEditId); parameters[12] = new SqlParameter("@IsActive", radioButtonStatus); parameters[13] = new SqlParameter("@UserRoleID", Convert.ToInt32(ddlRole.SelectedValue.ToString())); if (employeecode != null) { parameters[14] = new SqlParameter("@EmployeeCode", employeecode); } else { parameters[14] = new SqlParameter("@EmployeeCode", ""); } parameters[5].Direction = ParameterDirection.InputOutput; BaseDAL.ExecuteSPNonQuery("[UserOperations]", parameters); status = parameters[5].Value.ToString(); return(status); } catch (Exception ex) { //Log.Write("Error in user Creation:" + Environment.NewLine + ex.StackTrace + ex.InnerException, ex); return(status); } }
// Method to get dashboard entries... private void GetDashBoardDetails() { var queryDashboard = BaseDAL.getProjectDashboard("ProjectDashboardID", ProjectDashBoardEditId); DataSet dsDashBoard = BaseDAL.ExecuteDataSet(queryDashboard); DataSet dsBudget = BaseDAL.ExecuteDataSet("SELECT ProjectID,sum(ISNULL(BudgetHours,0)) BudgetHours,sum(ISNULL(RevisedBudgetHours,0)) RevisedBudgetHours,sum(ISNULL(ActualHrs,0)) ActualHrs FROM (SELECT dt1.ProjectID,dt1.PhaseID,sum(dt1.BudgetHours) BudgetHours,sum(dt1.RevisedBudgetHours) RevisedBudgetHours,sum(dt1.ActualHrs) ActualHrs FROM(SELECT projest.EstimationRoleID,projest.PhaseID,pro.ProjectID ,avg(ISNULL(projest.BudgetHours,0)) BudgetHours,avg(ISNULL(projest.RevisedBudgetHours,0)) RevisedBudgetHours,sum(ISNULL(projest.ActualHrs,0)) ActualHrs FROM ProjectDashBoardEstimation projest right join project pro on projest.ProjectID=pro.ProjectID GROUP BY projest.EstimationRoleID,projest.PhaseID,pro.ProjectID) AS dt1 GROUP BY dt1.PhaseID,dt1.ProjectID ) AS d1 GROUP BY d1.projectID"); if (dsDashBoard.Tables[0].Rows.Count > 0) { DataSet dsDash = BaseDAL.ExecuteDataSet("SELECT DashboardID,Name,FromDate,ToDate,PeriodType,Status From Dashboard where DashboardID=" + dsDashBoard.Tables[0].Rows[dsDashBoard.Tables[0].Rows.Count - 1].ItemArray[23] + ""); if (dsDashBoard.Tables[0].Rows.Count > 0) { if (dsDashBoard.Tables[0].Rows[0].ItemArray[3].ToString() == "4") { RBCG.Checked = false; RBCY.Checked = false; RBCR.Checked = false; } else if (dsDashBoard.Tables[0].Rows[0].ItemArray[3].ToString() == "3") { RBCG.Checked = true; RBCY.Checked = false; RBCR.Checked = false; } else if (dsDashBoard.Tables[0].Rows[0].ItemArray[3].ToString() == "1") { RBCG.Checked = false; RBCY.Checked = false; RBCR.Checked = true; } else { RBCG.Checked = false; RBCY.Checked = true; RBCR.Checked = false; } if (dsDashBoard.Tables[0].Rows[0].ItemArray[4].ToString() == "4") { RBTG.Checked = false; RBTR.Checked = false; RBTY.Checked = false; } else if (dsDashBoard.Tables[0].Rows[0].ItemArray[4].ToString() == "3") { RBTG.Checked = true; RBTR.Checked = false; RBTY.Checked = false; } else if (dsDashBoard.Tables[0].Rows[0].ItemArray[4].ToString() == "1") { RBTG.Checked = false; RBTR.Checked = true; RBTY.Checked = false; } else { RBTG.Checked = false; RBTR.Checked = false; RBTY.Checked = true; } //for (int i = 0; i < dsBudget.Tables[0].Rows.Count; i++) //{ //if (dsDash.Tables[0].Rows[0].ItemArray[5].Equals("I")) //{ // if (dsDashBoard.Tables[0].Rows[0].ItemArray[1].ToString() == dsBudget.Tables[0].Rows[i].ItemArray[0].ToString()) // { // if (Convert.ToInt32(dsBudget.Tables[0].Rows[i].ItemArray[3]) > Convert.ToInt32(dsBudget.Tables[0].Rows[i].ItemArray[1].ToString())) // { // RBBG.Checked = false; // RBBR.Checked = true; // RBBY.Checked = false; // } // else // { // if (dsDashBoard.Tables[0].Rows[0].ItemArray[5].ToString() == "3") // { // RBBG.Checked = true; // RBBR.Checked = false; // RBBY.Checked = false; // } // else if (dsDashBoard.Tables[0].Rows[0].ItemArray[5].ToString() == "1") // { // RBBG.Checked = false; // RBBR.Checked = true; // RBBY.Checked = false; // } // else if (dsDashBoard.Tables[0].Rows[0].ItemArray[5].ToString() == "2") // { // RBBG.Checked = false; // RBBR.Checked = false; // RBBY.Checked = true; // } // } // } //} //else //{ if (dsDashBoard.Tables[0].Rows[0].ItemArray[5].ToString() == "4") { RBBG.Checked = false; RBBR.Checked = false; RBBY.Checked = false; } else if (dsDashBoard.Tables[0].Rows[0].ItemArray[5].ToString() == "3") { RBBG.Checked = true; RBBR.Checked = false; RBBY.Checked = false; } else if (dsDashBoard.Tables[0].Rows[0].ItemArray[5].ToString() == "1") { RBBG.Checked = false; RBBR.Checked = true; RBBY.Checked = false; } else if (dsDashBoard.Tables[0].Rows[0].ItemArray[5].ToString() == "2") { RBBG.Checked = false; RBBR.Checked = false; RBBY.Checked = true; } // } //} if (dsDashBoard.Tables[0].Rows[0].ItemArray[6].ToString() == "4") { RBEG.Checked = false; RBER.Checked = false; RBEY.Checked = false; } else if (dsDashBoard.Tables[0].Rows[0].ItemArray[6].ToString() == "3") { RBEG.Checked = true; RBER.Checked = false; RBEY.Checked = false; } else if (dsDashBoard.Tables[0].Rows[0].ItemArray[6].ToString() == "1") { RBEG.Checked = false; RBER.Checked = true; RBEY.Checked = false; } else { RBEG.Checked = false; RBER.Checked = false; RBEY.Checked = true; } txtWeeklyComments.Text = Server.HtmlDecode(dsDashBoard.Tables[0].Rows[0]["Comments"].ToString()); } } }