Yes sure.
CS
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.IO;
using System.Text;
using System.Web.Security;
public partial class _DetailsReportingLine : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
string SearchString = "";
protected void Page_Load(object sender, EventArgs e)
{
// txtSearch.Attributes.Add("onkeyup", "setTimeout('__doPostBack(\\'" + txtSearch.ClientID.Replace("_", "$") + "\\',\\'\\')', 0);");
String sqlType = "";
String sqlCategory = "";
String sqlYear = "";
if (Request.QueryString["type"] == "FINAL")
{
lblType.Text = "FINAL YEAR";
hfKeyType.Value = "final";
sqlType = " AND a.a_type = 'final'";
}
else
{
lblType.Text = "KPI PLANNING";
hfKeyType.Value = "kpi";
sqlType = " AND a.a_type = 'kpi'";
}
if (rbKeyCategory.SelectedValue.ToString() == "project")
{
sqlCategory = " AND a.a_category = 'project'";
}
else if (rbKeyCategory.SelectedValue.ToString() == "transferred")
{
sqlCategory = " AND a.a_category = 'transferred'";
}
else
{
sqlCategory = " AND a.a_category = 'default'";
}
sqlYear = " WHERE a.a_year = " + ddlKeyYear.SelectedValue;
lblYear.Text = ddlKeyYear.SelectedValue;
if (Request.IsAuthenticated)
{
string userName = Profile.UserName;
string c_companyid = Profile.c_companyid;
string roles = "";
ProfileCommon userProfile = (ProfileCommon)
ProfileCommon.Create(userName, true);
c_companyid = userProfile.c_companyid;
string[] arrRoles = Roles.GetRolesForUser(userName);
foreach (string sRole in arrRoles)
{
roles += sRole;
}
if (Request.QueryString["type"] == "FINAL")
{
if (HttpContext.Current.User.IsInRole("HRADMIN"))
{
if (rbKeyCategory.SelectedValue == "project")
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, mp.mp_flag AS Flag, (CASE WHEN mp.mp_flag = '1' THEN 'OPCOS MD' WHEN mp.mp_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN mp.mp_flag = '3' THEN 'NORMAL STAFF' WHEN mp.mp_flag = '4' THEN 'PROJECT ENGINEER' WHEN mp.mp_flag = '5' THEN 'PROJECT MANAGER' WHEN mp.mp_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' WHEN a.a_status = '3' THEN 'REVIEWED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN mp.mp_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN mp.mp_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblKPIMultipleProject mp on mp.mp_id = a.a_referenceid inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " AND a.a_companyid=" + c_companyid + " ORDER BY e.s_firstname ASC";
else if (rbKeyCategory.SelectedValue == "transferred")
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, ch.ch_flag AS Flag, (CASE WHEN ch.ch_flag = '1' THEN 'OPCOS MD' WHEN ch.ch_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN ch.ch_flag = '3' THEN 'NORMAL STAFF' WHEN ch.ch_flag = '4' THEN 'PROJECT ENGINEER' WHEN ch.ch_flag = '5' THEN 'PROJECT MANAGER' WHEN ch.ch_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' WHEN a.a_status = '3' THEN 'REVIEWED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN ch.ch_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN ch.ch_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblKPICompanyHistory ch on ch.ch_id = a.a_referenceid inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " AND a.a_companyid=" + c_companyid + " ORDER BY e.s_firstname ASC";
else
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, e.s_flag AS Flag, (CASE WHEN e.s_flag = '1' THEN 'OPCOS MD' WHEN e.s_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN e.s_flag = '3' THEN 'NORMAL STAFF' WHEN e.s_flag = '4' THEN 'PROJECT ENGINEER' WHEN e.s_flag = '5' THEN 'PROJECT MANAGER' WHEN e.s_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' WHEN a.a_status = '3' THEN 'REVIEWED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN e.s_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN e.s_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on e.s_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " AND a.a_companyid=" + c_companyid + " ORDER BY e.s_firstname ASC";
}
else
{
if (rbKeyCategory.SelectedValue == "project")
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, mp.mp_flag AS Flag, (CASE WHEN mp.mp_flag = '1' THEN 'OPCOS MD' WHEN mp.mp_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN mp.mp_flag = '3' THEN 'NORMAL STAFF' WHEN mp.mp_flag = '4' THEN 'PROJECT ENGINEER' WHEN mp.mp_flag = '5' THEN 'PROJECT MANAGER' WHEN mp.mp_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' WHEN a.a_status = '3' THEN 'REVIEWED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser, (CASE WHEN mp.mp_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN mp.mp_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblKPIMultipleProject mp on mp.mp_id = a.a_referenceid inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " ORDER BY e.s_firstname ASC";
else if (rbKeyCategory.SelectedValue == "transferred")
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, ch.ch_flag AS Flag, (CASE WHEN ch.ch_flag = '1' THEN 'OPCOS MD' WHEN ch.ch_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN ch.ch_flag = '3' THEN 'NORMAL STAFF' WHEN ch.ch_flag = '4' THEN 'PROJECT ENGINEER' WHEN ch.ch_flag = '5' THEN 'PROJECT MANAGER' WHEN ch.ch_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' WHEN a.a_status = '3' THEN 'REVIEWED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser, (CASE WHEN ch.ch_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN ch.ch_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblKPICompanyHistory ch on ch.ch_id = a.a_referenceid inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " ORDER BY e.s_firstname ASC";
else
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, e.s_flag AS Flag, (CASE WHEN e.s_flag = '1' THEN 'OPCOS MD' WHEN e.s_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN e.s_flag = '3' THEN 'NORMAL STAFF' WHEN e.s_flag = '4' THEN 'PROJECT ENGINEER' WHEN e.s_flag = '5' THEN 'PROJECT MANAGER' WHEN e.s_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' WHEN a.a_status = '3' THEN 'REVIEWED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN e.s_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN e.s_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " ORDER BY e.s_firstname ASC";
}
}
else
{
if (HttpContext.Current.User.IsInRole("HRADMIN"))
{
if (rbKeyCategory.SelectedValue == "project")
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, mp.mp_flag AS Flag, (CASE WHEN mp.mp_flag = '1' THEN 'OPCOS MD' WHEN mp.mp_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN mp.mp_flag = '3' THEN 'NORMAL STAFF' WHEN mp.mp_flag = '4' THEN 'PROJECT ENGINEER' WHEN mp.mp_flag = '5' THEN 'PROJECT MANAGER' WHEN mp.mp_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN mp.mp_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN mp.mp_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblKPIMultipleProject mp on mp.mp_id = a.a_referenceid inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " AND a.a_companyid=" + c_companyid + " ORDER BY e.s_firstname ASC";
else if (rbKeyCategory.SelectedValue == "transferred")
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, ch.ch_flag AS Flag, (CASE WHEN ch.ch_flag = '1' THEN 'OPCOS MD' WHEN ch.ch_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN ch.ch_flag = '3' THEN 'NORMAL STAFF' WHEN ch.ch_flag = '4' THEN 'PROJECT ENGINEER' WHEN ch.ch_flag = '5' THEN 'PROJECT MANAGER' WHEN ch.ch_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN ch.ch_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN ch.ch_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblKPICompanyHistory ch on ch.ch_id = a.a_referenceid inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " AND a.a_companyid=" + c_companyid + " ORDER BY e.s_firstname ASC";
else
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, e.s_flag AS Flag, (CASE WHEN e.s_flag = '1' THEN 'OPCOS MD' WHEN e.s_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN e.s_flag = '3' THEN 'NORMAL STAFF' WHEN e.s_flag = '4' THEN 'PROJECT ENGINEER' WHEN e.s_flag = '5' THEN 'PROJECT MANAGER' WHEN e.s_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN e.s_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN e.s_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on e.s_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " AND a.a_companyid=" + c_companyid + " ORDER BY e.s_firstname ASC";
}
else
{
if (rbKeyCategory.SelectedValue == "project")
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, mp.mp_flag AS Flag, (CASE WHEN mp.mp_flag = '1' THEN 'OPCOS MD' WHEN mp.mp_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN mp.mp_flag = '3' THEN 'NORMAL STAFF' WHEN mp.mp_flag = '4' THEN 'PROJECT ENGINEER' WHEN mp.mp_flag = '5' THEN 'PROJECT MANAGER' WHEN mp.mp_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN mp.mp_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN mp.mp_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblKPIMultipleProject mp on mp.mp_id = a.a_referenceid inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " ORDER BY e.s_firstname ASC";
else if (rbKeyCategory.SelectedValue == "transferred")
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, ch.ch_flag AS Flag, (CASE WHEN ch.ch_flag = '1' THEN 'OPCOS MD' WHEN ch.ch_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN ch.ch_flag = '3' THEN 'NORMAL STAFF' WHEN ch.ch_flag = '4' THEN 'PROJECT ENGINEER' WHEN ch.ch_flag = '5' THEN 'PROJECT MANAGER' WHEN ch.ch_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN ch.ch_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN ch.ch_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblKPICompanyHistory ch on ch.ch_id = a.a_referenceid inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " ORDER BY e.s_firstname ASC";
else
SqlDataSource4.SelectCommand = "select DISTINCT a.a_appraisalid AS a_appraisalid, e.s_firstname as Name, e.s_flag AS Flag, (CASE WHEN e.s_flag = '1' THEN 'OPCOS MD' WHEN e.s_flag = '2' THEN 'FUNCTIONAL CHQ' WHEN e.s_flag = '3' THEN 'NORMAL STAFF' WHEN e.s_flag = '4' THEN 'PROJECT ENGINEER' WHEN e.s_flag = '5' THEN 'PROJECT MANAGER' WHEN e.s_flag = '6' THEN 'PROJECT DIRECTOR' END) As Tagging,(CASE WHEN a.a_status IN (NULL,' ','NULL') THEN 'PENDING SUBMISSION' WHEN a.a_status = '1' THEN 'SUBMITTED' WHEN a.a_status = '2' THEN 'APPROVED' END) As StatusKPI,e.s_icno as ICNumber, e.s_email as Email,f.c_compname as Company,z.d_name as Division, d.d_name as Department, a.a_position as Position, g.pg_name as Grade, a.a_worklocation as Location, e.s_joindate as JoinDate, a.a_reportingline1 as AppraiserIC, m.s_firstname as Appraiser , (CASE WHEN e.s_flag = '1' THEN '' ELSE a.a_reportingline2 END) as ReviewerIc, (CASE WHEN e.s_flag = '1' THEN '' ELSE n.s_firstname END) as Reviewer, a.* FROM tblStaff e inner join tblKPIAppraisal a on e.s_icno = a.a_icno inner join tblStaff m on a.a_reportingline1 = m.s_icno inner join tblStaff n on (a.a_reportingline2 = n.s_icno or a.a_reportingline2 = '') inner join tblPostGrade g on a.a_grade = pg_gradeid inner join tblDepartment d on a.a_departmentid = d.d_departmentid inner join tblDivision z on a.a_divisionid = z.d_divisionid inner join tblCompany f on a.a_companyid = f.c_companyid " + sqlYear + sqlType + sqlCategory + " ORDER BY e.s_firstname ASC";
}
}
}
if (!IsPostBack)
{
GridView1.DataBind();
}
}
protected void GridView1_PageIndexChanged (object sender, EventArgs e)
{
}
protected void btnSearch_Click (object sender, EventArgs e)
{
SearchString = txtKeyWord.Text;
GridView1.DataBind();
}
public string HighlightText (string InputTxt)
{
string Search_Str = txtKeyWord.Text.ToString();
// Setup the regular expression and add the Or operator.
Regex RegExp = new Regex(Search_Str.Replace(" ", "|").Trim(), RegexOptions.IgnoreCase);
// Highlight keywords by calling the
//delegate each time a keyword is found.
return RegExp.Replace(InputTxt, new MatchEvaluator(ReplaceKeyWords));
// Set the RegExp to null.
RegExp = null;
}
public string ReplaceKeyWords(Match m)
{
return "<span class=highlight>" + m.Value + "</span>";
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
this.GridView1.Columns[0].Visible = false;
this.GridView1.Columns[1].Visible = true;
this.GridView1.Columns[4].Visible = true;
this.GridView1.Columns[5].Visible = true;
this.GridView1.Columns[6].Visible = true;
this.GridView1.Columns[7].Visible = true;
this.GridView1.Columns[10].Visible = true;
this.GridView1.Columns[11].Visible = true;
this.GridView1.Columns[12].Visible = true;
this.GridView1.Columns[13].Visible = true;
this.GridView1.Columns[14].Visible = true;
this.GridView1.Columns[15].Visible = true;
this.GridView1.Columns[16].Visible = true;
this.GridView1.Columns[17].Visible = true;
this.GridView1.Columns[18].Visible = true;
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=KPI_REPORT"+ DateTime.Now +".xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
GridView1.DataBind();
// updated by NBN 30102015 - remove style formating
if (GridView1.HeaderRow != null && GridView1.HeaderRow.Cells != null)
{
for (int ct = 0; ct < GridView1.HeaderRow.Cells.Count; ct++)
{
string headerText = GridView1.HeaderRow.Cells[ct].Text;
if (GridView1.HeaderRow.Cells[ct].HasControls())
{
if (GridView1.HeaderRow.Cells[ct].Controls[0].GetType().ToString() == "System.Web.UI.WebControls.DataControlLinkButton")
{
headerText = ((LinkButton)GridView1.HeaderRow.Cells[ct].Controls[0]).Text;
}
GridView1.HeaderRow.Cells[ct].Controls.Clear();
}
// Reassign header text
GridView1.HeaderRow.Cells[ct].Text = headerText;
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
}
}
foreach (GridViewRow row in GridView1.Rows)
{
row.BackColor = System.Drawing.Color.White;
foreach (TableCell cell in row.Cells)
{
List<Control> controls = new List<Control>();
//Add controls to be removed to Generic List
foreach (Control control in cell.Controls)
{
controls.Add(control);
}
//Loop through the controls to be removed and replace then with Literal
foreach (Control control in controls)
{
switch (control.GetType().Name)
{
case "HyperLink":
cell.Controls.Add(new Literal { Text = (control as HyperLink).Text });
break;
case "TextBox":
cell.Controls.Add(new Literal { Text = (control as TextBox).Text });
break;
case "Label":
cell.Controls.Add(new Literal { Text = (control as Label).Text });
break;
}
cell.Controls.Remove(control);
}
}
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
protected void UpdateButton_Click(object sender, EventArgs e)
{
}
}
HTML
<%@ Page Title="" Language="C#" MasterPageFile="adminTemplate.master" EnableEventValidation = "false" AutoEventWireup="true" CodeFile="detailsreportingline.aspx.cs" Inherits="_DetailsReportingLine" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
<link href="../css/gridview.css" rel="stylesheet" type="text/css" />
<link href="../css/template.css" rel="stylesheet" type="text/css" />
<link href="../css/redmond/jquery-ui-1.7.2.custom.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript" src="http://ashishblog.com/ash/source/jquery.tablesorter-2.0.3.js"></script>
<link type="text/css" rel="stylesheet" href="http://ashishblog.com/ash/source/style.css" />
<script type="text/javascript">
jQuery(document).ready(function () {
$("#GridView1").tablesorter({ debug: false, widgets: ['zebra'], sortList: [[0, 0]] });
});
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<table width="100%" border="0" cellspacing="0" cellpadding="0" height="29" background="..\images\bgBelBan.jpg">
<tr>
<td class="auto-style1" colspan="2"> <b>ADMINISTRATION - DETAILS & REPORTING LINE ( <asp:Label ID="lblType" runat="server"></asp:Label> <asp:Label ID="lblYear" runat="server"></asp:Label><asp:HiddenField ID="hfKeyType" runat="server" />)
</b></td>
<td> </td>
</tr>
</table>
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="false" LoadScriptsBeforeUI="false" ScriptMode="Release">
</asp:ScriptManager>
<br />
<div align="left">
<table style="margin-left: 27px" >
<tr><td> </td><td colspan="2"><asp:HyperLink ID="lnkKPI" runat="server" NavigateUrl="detailsreportingline.aspx?type=KPI" Text=" KPI PLANNING " BorderWidth="1" BorderColor="#666666" BackColor="#6699ff" ForeColor="White" Font-Size="11" ></asp:HyperLink> <asp:HyperLink ID="lnkFINAL" runat="server" NavigateUrl="detailsreportingline.aspx?type=FINAL" Text=" FINAL REVIEW " BorderWidth="1" BorderColor="#666666" BackColor="#6699ff" ForeColor="White" Font-Size="11"></asp:HyperLink><br /><br /></td></tr>
<tr>
<td> </td>
<td valign="top"><asp:Label ID="lblKeyCategory" runat="server" Font-Bold="True" Text="SEARCH BY CATEGORY :"></asp:Label></td>
<td>
<asp:RadioButtonList ID="rbKeyCategory" runat="server" CellPadding="0" CellSpacing="0" RepeatDirection="Vertical" style="margin-left: 27px" Height="30px" >
<asp:ListItem Text=" DEFAULT " Value="default" Selected="True" />
<asp:ListItem Text=" MULTIPLE PROJECT " Value="project" />
<asp:ListItem Text=" TRANSFER / SECONDMENT " Value="transferred" />
</asp:RadioButtonList>
<br />
</td>
</tr>
<tr>
<td> </td>
<td><asp:Label ID="lblKeyYear" runat="server" Font-Bold="True" Text="SEARCH BY YEAR :"></asp:Label></td>
<td>
<asp:DropDownList ID="ddlKeyYear" runat="server" style="margin-left: 27px" Height="22px" Width="100px">
<asp:ListItem Text="2016" Value="2016" />
<asp:ListItem Text="2015" Value="2015" />
<asp:ListItem Text="2014" Value="2014" />
</asp:DropDownList>
</td>
</tr>
<tr>
<td> </td>
<td><asp:Label ID="lblKeyWord" runat="server" Font-Bold="True" Text="SEARCH BY KEYWORD :"></asp:Label></td>
<td>
<asp:DropDownList ID="ddlKeyWord" runat="server" style="margin-left: 27px" Height="22px" Width="100px">
<asp:ListItem>ICNumber</asp:ListItem>
<asp:ListItem>Name</asp:ListItem>
<asp:ListItem>Grade</asp:ListItem>
<asp:ListItem>Company</asp:ListItem>
<asp:ListItem>Department</asp:ListItem>
<asp:ListItem>Division</asp:ListItem>
<asp:ListItem>StatusKPI</asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="txtKeyWord" runat="server" Width="246px" Height="22px" /> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />
</td>
</tr>
</table>
</div>
<asp:UpdatePanel ID="UpdatePanel1" runat="server" >
<ContentTemplate>
<div align="center">
</div>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource4" AutoGenerateColumns="False" BorderWidth="1px" CellPadding="2" DataKeyNames="a_appraisalid" AllowPaging="True" AllowSorting="True" CssClass="GridViewStyle" PageSize="30" Width="100%">
<AlternatingRowStyle BackColor="PaleGoldenrod" />
<Columns>
<asp:HyperLinkField DataNavigateUrlFields="a_appraisalid" DataNavigateUrlFormatString="modify.detailsreportingline.aspx?a_appraisalid={0}" Text="Select" />
<asp:TemplateField HeaderText="No" SortExpression="id" ItemStyle-Width="10" Visible="false" >
<ItemStyle width ="25px" VerticalAlign="Middle" HorizontalAlign="Center" />
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width="50" Visible="false">
<ItemTemplate>
<asp:Label ID="lblIc" runat="server" Text='<%# Eval("ICNumber") %>' Visible="false"/>
<asp:Label ID="lblId" runat="server" Text='<%# Eval("a_appraisalid") %>' Visible="false"/>
<asp:HyperLink ID="AppID" Font-Bold="true" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="IC" InsertVisible="False" SortExpression="ICNumber" ItemStyle-Width="10%">
<ItemTemplate>
<asp:Label ID="ICNumber" runat="server" Text='<%# Bind("ICNumber") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name" InsertVisible="False" SortExpression="Name">
<ItemTemplate>
<asp:Label ID="Name" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="Name" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Category" InsertVisible="False" SortExpression="Tagging">
<ItemTemplate>
<asp:Label ID="Tagging" runat="server" Text='<%# Bind("Tagging") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="Tagging" runat="server" Text='<%# Eval("Tagging") %>'></asp:Label>
</EditItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Grade" HeaderText="Grade" SortExpression="Grade" visible="false"/>
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" visible="false"/>
<asp:BoundField DataField="Position" HeaderText="Position" SortExpression="Position" visible="false" />
<asp:BoundField DataField="Location" HeaderText="Location" SortExpression="Location" visible="false"/>
<asp:BoundField DataField="Company" HeaderText="Company" SortExpression="Company" visible="false"/>
<asp:BoundField DataField="Division" HeaderText="Division" SortExpression="Division" visible="false"/>
<asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" visible="false"/>
<asp:BoundField DataField="JoinDate" HeaderText="JoinDate" SortExpression="JoinDate" visible="false"/>
<asp:BoundField DataField="StatusKPI" HeaderText="Status" SortExpression="StatusKPI" visible="false"/>
<asp:BoundField DataField="AppraiserIC" HeaderText="AppraiserIC" SortExpression="AppraiserIC" visible="false"/>
<asp:BoundField DataField="Appraiser" HeaderText="Reporting Line 1" SortExpression="Appraiser" />
<asp:BoundField DataField="ReviewerIC" HeaderText="ReviewerIC" SortExpression="ReviewerIC" visible="false"/>
<asp:BoundField DataField="Reviewer" HeaderText="Reporting Line 2" SortExpression="Reviewer" />
</Columns>
<PagerSettings FirstPageText="First" LastPageText="Last" Mode="NumericFirstLast" />
<RowStyle CssClass="RowStyle" />
<EmptyDataRowStyle CssClass="EmptyRowStyle" />
<PagerStyle CssClass="PagerStyle" />
<SelectedRowStyle CssClass="SelectedRowStyle" />
<HeaderStyle CssClass="HeaderStyle" />
<EditRowStyle CssClass="EditRowStyle" />
<SortedAscendingCellStyle BackColor="#FAFAE7" />
<SortedAscendingHeaderStyle BackColor="#DAC09E" />
<SortedDescendingCellStyle BackColor="#E1DB9C" />
<SortedDescendingHeaderStyle BackColor="#C2A47B" />
<EmptyDataTemplate>
No record found
</EmptyDataTemplate>
</asp:GridView>
<br />
<div align="center">
<asp:Button ID="btnExportExcel" runat="server" Text="ExportToExcel" OnClick="btnExportExcel_Click" />
</div>
</ContentTemplate>
</asp:UpdatePanel>
<br />
<br />
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
FilterExpression=" a_category = '{0}' AND a_year = '{1}' AND {2} LIKE '%{3}%'">
<FilterParameters>
<asp:ControlParameter ControlID="rbKeyCategory" Name="rbKeyCategory" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="ddlKeyYear" Name="ddlKeyYear" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="ddlKeyWord" Name="ddlKeyWord" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="txtKeyWord" Name="txtKeyWord" />
</FilterParameters>
</asp:SqlDataSource>
</asp:Content>
