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