コード例 #1
0
 protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {
         string en_id = Request.QueryString["en_ID"].ToString();
         if (en_id != string.Empty)
         {
             HiddenField1.Value = en_id;
             using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
             {
                 SPWeb  hrWeb      = site.AllWebs["HRMV02"];
                 SPList entitylist = hrWeb.Lists["Entity"];
                 //SPQuery qry_edit_entity = new SPQuery();
                 //qry_edit_entity.Query = @"<Where><Eq><FieldRef Name='ID'/><Value Type='Counter'>"+en_id+@"</Value></Eq></Where>";
                 SPListItem edit_entitylist_item = entitylist.GetItemById(Convert.ToInt32(en_id.ToString()));
                 txtbox_edit_EntityName.Text = edit_entitylist_item["Title"].ToString();
                 rbtn_EntityStatus.Items.Clear();
                 SPFieldChoice rbt_entity_status = new SPFieldChoice(edit_entitylist_item.Fields, "Status");
                 for (int i = 0; i <= (Convert.ToInt16(rbt_entity_status.Choices.Count.ToString()) - 1); i++)
                 {
                     rbtn_EntityStatus.Items.Add(rbt_entity_status.Choices[i].ToString());
                     if (rbtn_EntityStatus.Items[i].Text == edit_entitylist_item["Status"].ToString())
                     {
                         rbtn_EntityStatus.Items[i].Selected = true;
                         //rbtn_EntityStatus.Items[i].Attributes["AutoPostBack"] = "false";
                     }
                 }
             }
         }
     }
 }
コード例 #2
0
        protected void btn_Cancel_Entity_update_Click(object sender, EventArgs e)
        {
            SPSite site  = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current));
            SPWeb  hrweb = site.AllWebs["HRMV02"];

            Response.Redirect(hrweb.Url.ToString());
        }
コード例 #3
0
 protected SPDataSource entityds()
 {
     using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
     {
         SPWeb        hrWeb    = site.AllWebs["HRMV02"];
         SPDataSource entityds = new SPDataSource();
         entityds.List = hrWeb.Lists["Entity"];
         return(entityds);
     }
 }
コード例 #4
0
        protected void Page_Load(object sender, EventArgs e)
        {
            string siteurl      = MyConfiguration.GetSiteURL(SPContext.Current);
            var    Resource_Svc = new Resource();

            Resource_Svc.AllowAutoRedirect     = true;
            Resource_Svc.UseDefaultCredentials = true;
            Resource_Svc.Url      = siteurl + "/_vti_bin/psi/resource.asmx";
            LblCurrentResUID.Text = Resource_Svc.GetCurrentUserUid().ToString();
            //LblCurrentResUID.Text = "6FF0A657-63BC-4390-8AAF-7EE5CE033088";
        }
コード例 #5
0
        protected void btn_Save_Entity_update_Click(object sender, EventArgs e)
        {
            string cusiteurl = string.Empty;

            using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
            {
                SPWeb hrWeb = site.AllWebs["HRMV02"];
                cusiteurl = hrWeb.Url.ToString();
                hrWeb.AllowUnsafeUpdates = true;
                SPList     entitylist           = hrWeb.Lists["Entity"];
                SPListItem edit_entitylist_item = entitylist.GetItemById(Convert.ToInt32(HiddenField1.Value.ToString()));
                edit_entitylist_item["Title"]  = txtbox_edit_EntityName.Text;
                edit_entitylist_item["Status"] = rbtn_EntityStatus.SelectedValue.ToString();
                edit_entitylist_item.Update();
            }
            Response.Redirect(cusiteurl);
        }
コード例 #6
0
        protected void Approve_Click(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                string siteurl = MyConfiguration.GetSiteURL(SPContext.Current);

                var Resource_Svc = new Resource();
                Resource_Svc.AllowAutoRedirect     = true;
                Resource_Svc.UseDefaultCredentials = true;
                Resource_Svc.Url = siteurl + "/_vti_bin/psi/resource.asmx";

                var Timesheet_Svc = new TimeSheet();
                Timesheet_Svc.AllowAutoRedirect     = true;
                Timesheet_Svc.UseDefaultCredentials = true;
                Timesheet_Svc.Url = siteurl + "/_vti_bin/psi/timesheet.asmx";

                string[] selectedtsuids = Request.Form["LstSelectedtsuids"].Split('#');
                if (selectedtsuids.Length > 0)
                {
                    var grouped_tsuids = (from c in selectedtsuids
                                          group c by c).ToList();
                    foreach (IGrouping <string, string> tsuid_str in grouped_tsuids)
                    {
                        if (tsuid_str.Key != string.Empty)
                        {
                            try
                            {
                                var tsuid        = new Guid(tsuid_str.Key);
                                var jobUID       = Guid.NewGuid();
                                var nextapprover = Resource_Svc.GetCurrentUserUid();
                                Timesheet_Svc.QueueReviewTimesheet(jobUID, tsuid, nextapprover,
                                                                   "Approving using utility",
                                                                   ITXProjectsLibrary.WebSvcTimeSheet.Action.Approve);
                            }
                            catch (Exception ex)
                            {
                                MyConfiguration.ErrorLog("Error at approving timesheet due to " + ex.Message,
                                                         EventLogEntryType.Error);
                            }
                        }
                    }
                }
                LblStatus.Text = "Timesheet approval process completed successfully.";
            }
        }
コード例 #7
0
 protected SPDataSource resdetailds(int resfilterid)
 {
     using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
     {
         SPWeb        hrWeb    = site.AllWebs["HRMV02"];
         SPDataSource resdetds = new SPDataSource();
         resdetds.SelectCommand = @"<Query>
                                     <Where>
                                         <Eq>
                                             <FieldRef Name='Department' LookupId='True' />
                                             <Value Type='Lookup'>" + resfilterid + @"</Value>
                                         </Eq>
                                     </Where>
                                 </Query>";
         resdetds.List          = hrWeb.Lists["Resource Details"];
         return(resdetds);
     }
 }
コード例 #8
0
 protected SPDataSource deptds(int deptid)
 {
     using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
     {
         SPWeb        hrWeb  = site.AllWebs["HRMV02"];
         SPDataSource deptds = new SPDataSource();
         deptds.SelectCommand = @"<Query>
                                     <Where>
                                         <Eq>
                                             <FieldRef Name='Entity' LookupId='True'/>
                                             <Value Type='Lookup'>" + deptid + @"</Value>
                                         </Eq>
                                     </Where>
                                 </Query>";
         deptds.List          = hrWeb.Lists["Department"];
         return(deptds);
     }
 }
        protected void exportcsv_Click(object sender, EventArgs e)
        {
            string   _stdate           = _hiddenstdate.Value.ToString();
            string   _enddate          = _hiddenenddate.Value.ToString();
            DateTime _enddateformatted = Convert.ToDateTime(_enddate);
            string   siteurl           = HttpContext.Current.Request.UrlReferrer.ToString();
            var      url    = new Uri(siteurl);
            string   rbsurl = url.Scheme + "://" + url.Host + ":" + url.Port + url.Segments[0] + url.Segments[1];
            Guid     adminguid;
            var      resource_svc = new Resource();

            adminguid        = new Guid("6FF0A657-63BC-4390-8AAF-7EE5CE033088");
            resource_svc.Url = "http://jump/cimb/_vti_bin/psi/resource.asmx";

            if (url.Host.ToString() != "localhost")
            {
                //var rbs = new ReadRBSValues(System.Net.CredentialCache.DefaultNetworkCredentials, "http://jump/cimb");
                resource_svc.Url = rbsurl + "/_vti_bin/psi/resource.asmx";
            }
            //var rbs = new ReadRBSValues(System.Net.CredentialCache.DefaultNetworkCredentials, rbsurl);
            //var resuids = rbs.GetBottomLevelResouceUIDs();
            resource_svc.UseDefaultCredentials = true;
            resource_svc.AllowAutoRedirect     = true;
            Guid   currentuserid            = resource_svc.GetCurrentUserUid();
            string res_under_curr_user      = @"
                SELECT		ResourceUID
                FROM		dbo.MSP_EpmResource_UserView
                WHERE		(RBS Like (	(
						                SELECT	RBS
						                FROM	dbo.MSP_EpmResource_UserView
						                WHERE	ResourceUID = '"                         + currentuserid.ToString() + @"'
						                )
					                +'.%')
			                ) AND ResourceIsActive = 1
                ";
            WindowsImpersonationContext wik = null;

            wik = WindowsIdentity.Impersonate(IntPtr.Zero);
            SqlConnection con = new SqlConnection(MyConfiguration.GetDataBaseConnectionString(siteurl));

            con.Open();
            DataSet        filterresourcelist    = new DataSet();
            SqlDataAdapter filterresourceadapter = new SqlDataAdapter(res_under_curr_user, con);

            filterresourceadapter.Fill(filterresourcelist);
            string filterresource = "(";

            foreach (DataRow row in filterresourcelist.Tables[0].Rows)
            {
                filterresource += "'" + row[0].ToString() + "',";
            }

            /*                foreach (var resuid in resuids)
             *              {
             *                  filterresource += "'" + resuid.ToString() + "',";
             *              }
             */
            filterresource = filterresource.Substring(0, filterresource.Length - 1) + ")";
            string gridqry = @"
SELECT		res.ResourceUID, res.ResourceName, res.RBS, tperiod.PeriodUID, tperiod.PeriodStatusID, tperiod.StartDate, tperiod.EndDate, tperiod.PeriodName,
            tperiod.LCID, ISNULL(TM_Name.ResourceName, 'Not Assigned') AS TM_Name
INTO        [#t1]
FROM        MSP_EpmResource_UserView AS TM_Name RIGHT OUTER JOIN
            MSP_EpmResource_UserView AS res ON TM_Name.ResourceUID = res.ResourceTimesheetManagerUID CROSS JOIN
            MSP_TimesheetPeriod AS tperiod
WHERE		(tperiod.StartDate BETWEEN (
			(SELECT		CASE WHEN (TimeDayOfTheWeek = 2) THEN '"                 + _stdate + @"' WHEN (TimeDayOfTheWeek = 1) THEN DATEADD(d,1, '" + _stdate + @"' )
						ELSE DATEADD(d,(2-TimeDayofTheWeek), '"                         + _stdate + @"' ) END AS stdate
			FROM        MSP_TimeByDay
			WHERE		(TimeByDay = CONVERT(DATETIME, '"                 + _stdate + @"' , 102)))
			)
			AND '"             + _enddate + @"' ) AND (res.ResourceUID IN " + filterresource + @")
SELECT      [#t1].PeriodUID, [#t1].ResourceUID,[#t1].TM_Name, [#t1].RBS, [#t1].ResourceName, [#t1].PeriodName,
			ISNULl(tstatus.Description,'Not Created') AS [TimeSheet Status], [#t1].StartDate, [#t1].EndDate
INTO #t2
FROM        MSP_TimesheetStatus AS tstatus INNER JOIN
            MSP_Timesheet AS tsheet ON tstatus.TimesheetStatusID = tsheet.TimesheetStatusID INNER JOIN
            MSP_TimesheetResource AS tres ON tsheet.OwnerResourceNameUID = tres.ResourceNameUID RIGHT OUTER JOIN
            [#t1] ON [#t1].ResourceUID = tres.ResourceUID AND [#t1].PeriodUID = tsheet.PeriodUID
drop table	#t1
SELECT		PeriodName, TM_Name, ResourceName, COUNT(CASE WHEN ([TimeSheet Status] = 'In Progress') THEN [TimeSheet Status] END)
            AS [In Progress], COUNT(CASE WHEN ([TimeSheet Status] = 'Not Created') THEN [TimeSheet Status] END) AS [Not Created],
            COUNT(CASE WHEN ([TimeSheet Status] = 'Submitted') THEN [TimeSheet Status] END) AS Submitted,
            COUNT(CASE WHEN ([TimeSheet Status] = 'Approved') THEN [TimeSheet Status] END) AS Approved
FROM        [#t2]
--WHERE		([TimeSheet Status] <> 'Approved')
GROUP BY	PeriodName, TM_Name, ResourceName
ORDER BY	PeriodName, TM_Name, ResourceName
/*
SELECT		PeriodName, COUNT(CASE WHEN ([TimeSheet Status] = 'In Progress') THEN [TimeSheet Status] END)
            AS [In Progress], COUNT(CASE WHEN ([TimeSheet Status] = 'Not Created') THEN [TimeSheet Status] END) AS [Not Created],
            COUNT(CASE WHEN ([TimeSheet Status] = 'Submitted') THEN [TimeSheet Status] END) AS Submitted
FROM        [#t2]
WHERE		([TimeSheet Status] <> 'Approved')
GROUP BY	PeriodName
ORDER BY	PeriodName
*/
drop table	#t2
";

            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                DataSet dt             = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(gridqry, con));
                adapter.Fill(dt);

                #region export function

                try
                {
                    // You should be very carefull with sharepoint layout folder, we have to specify full control permission to everybody.
                    string FolderPath = Server.MapPath("/_layouts/CIMB_TimeSheet/CSVFiles/");
                    if (Directory.Exists(FolderPath))
                    {
                        foreach (string file in Directory.GetFiles(FolderPath))
                        {
                            try
                            {
                                File.Delete(file);
                            }
                            catch (Exception ex)
                            {
                            }
                        }
                    }
                    else
                    {
                        Directory.CreateDirectory(FolderPath);
                    }
                    string clientfilename = DateTime.Now.ToString("ddMMyyhhmmss") + DateTime.Now.Millisecond.ToString();
                    string filename       = FolderPath + clientfilename + ".csv";
                    var writer            = new StreamWriter(filename);

                    //Writing column name at first row
                    string columnheaderline = string.Empty;

                    for (int i = 0; i < dt.Tables[0].Columns.Count; i++)
                    {
                        columnheaderline = columnheaderline + dt.Tables[0].Columns[i].ColumnName + ",";
                    }
                    writer.WriteLine(columnheaderline);
                    // Writing row values
                    foreach (DataRow row in dt.Tables[0].Rows)
                    {
                        string columnvalue = string.Empty;
                        for (int i = 0; i < dt.Tables[0].Columns.Count; i++)
                        {
                            columnvalue = columnvalue + row[i] + ",";
                        }
                        writer.WriteLine(columnvalue);
                    }
                    try
                    {
                        writer.Flush();
                        writer.Close();
                        writer.Dispose();
                    }
                    catch (Exception ex) { MyConfiguration.ErrorLog("Error in writing CSV : " + ex.Message, EventLogEntryType.Error); }
                    // Sending files here
                    Response.ContentType = "application/CSV";
                    Response.AddHeader("content-disposition", "attachment; filename=TimeSheetNonCompliance" + clientfilename + ".csv");
                    Response.TransmitFile(filename);
                    Response.Flush();
                    Response.End();
                }
                catch (Exception)
                {
                }

                #endregion export function
            });
        }
コード例 #10
0
        protected void exportcsv_Click(object sender, EventArgs e)
        {
            string   _stdate           = _hiddenstdate.Value;
            string   _enddate          = _hiddenenddate.Value;
            DateTime _enddateformatted = Convert.ToDateTime(_enddate);
            string   siteurl           = HttpContext.Current.Request.UrlReferrer.ToString();

            #region sqlqry

            string gridqry     = @"
SELECT     t_res.ResourceUID AS ResUID, t_proj.ProjectUID AS ProjUID, t_res.ResourceName AS ResName,
CASE WHEN t_class.Type <> 0 THEN t_class.ClassNAME ELSE t_proj.ProjectName END AS ProjName, t_act.TimeByDay AS t_date,
t_act.TimeByDay_DayOfWeek AS t_wkday, t_class.Type AS t_type, CASE WHEN ((t_act.TimeByDay_DayOfWeek = 1 OR
t_act.TimeByDay_DayOfWeek = 7) AND (t_class.Type = 1)) THEN '0' WHEN (t_class.Type = 1 AND
(t_act.ActualWorkBillable + t_act.ActualWorkNonBillable + t_act.ActualOvertimeWorkBillable + t_act.ActualOvertimeWorkNonBillable <= 4))
THEN '4' WHEN (t_class.Type = 1 AND
(t_act.ActualWorkBillable + t_act.ActualWorkNonBillable + t_act.ActualOvertimeWorkBillable + t_act.ActualOvertimeWorkNonBillable > 4))
THEN '8' ELSE t_act.ActualWorkBillable + t_act.ActualWorkNonBillable + t_act.ActualOvertimeWorkBillable + t_act.ActualOvertimeWorkNonBillable END
AS totaltime
INTO            [#t1]
FROM         MSP_TimesheetActual AS t_act INNER JOIN
                      MSP_TimesheetResource AS t_res ON t_act.LastChangedResourceNameUID = t_res.ResourceNameUID INNER JOIN
                      MSP_TimesheetLine AS t_line ON t_act.TimesheetLineUID = t_line.TimesheetLineUID INNER JOIN
                      MSP_TimesheetClass AS t_class ON t_line.ClassUID = t_class.ClassUID INNER JOIN
                      MSP_TimesheetProject AS t_proj ON t_line.ProjectNameUID = t_proj.ProjectNameUID
WHERE     (t_act.TimeByDay BETWEEN '" + _stdate + @"' AND '" + _enddate + @"')
ORDER BY ResUID, t_date, t_type

SELECT #t1.ResUID, #t1.ProjUID, #t1.ResName, #t1.ProjName, #t1.t_date, #t1.t_wkday, #t1.t_type, #t1.totaltime,
		ISNULL(dummyt1_nkwking.nonwktotal,0) AS nwktotal, ISNULL(dummyt1.daytotal,0) AS daytotal,
		CASE WHEN #t1.t_type <> 1 AND ((dummyt1_nkwking.nonwktotal = 4 AND dummyt1.daytotal >4)or (ISNULL(dummyt1_nkwking.nonwktotal,0) =8) or
					(ISNULL(dummyt1_nkwking.nonwktotal,0) = 0 AND dummyt1.daytotal > 8))
			THEN (8-ISNULL(dummyt1_nkwking.nonwktotal,0))*#t1.totaltime/(CASE WHEN dummyt1.daytotal=0 THEN 1.0 ELSE ISNULL(dummyt1.daytotal,1) END)
		ELSE #t1.totaltime END AS normalizedtime into #t2
FROM #t1 LEFT OUTER JOIN(
SELECT     dummyt1_nwking.ResUID, dummyt1_nwking.t_date, SUM(dummyt1_nwking.totaltime) AS nonwktotal
FROM         [#t1] AS dummyt1_nwking
WHERE     (dummyt1_nwking.t_type = 1)
GROUP BY dummyt1_nwking.ResUID, dummyt1_nwking.t_date
--ORDER BY dummyt1_nwking.ResUID, dummyt1_nwking.t_date
) AS dummyt1_nkwking ON #t1.ResUID = dummyt1_nkwking.ResUID AND #t1.t_date = dummyt1_nkwking.t_date LEFT OUTER JOIN
(
SELECT     dummyt1.ResUID, dummyt1.t_date, SUM(dummyt1.totaltime) AS daytotal
FROM         [#t1] AS dummyt1
WHERE     (dummyt1.t_type <> 1)
GROUP BY dummyt1.ResUID, dummyt1.t_date
--ORDER BY dummyt1.ResUID, dummyt1.t_date
) AS dummyt1 ON #t1.ResUID = dummyt1.ResUID AND #t1.t_date = dummyt1.t_date
ORDER BY #t1.ResUID, #t1.t_date, #t1.t_type
drop table #t1

SELECT distinct     TimeByDay AS missing_date,TimeDayOfTheWeek AS missing_wkday, t_Resources.ResUID AS missing_ResUID, t_Resources.ResName INTO #t3
FROM         MSP_TimeByDay CROSS JOIN (SELECT distinct [#t2].ResUID, #t2.ResName FROM [#t2]) AS t_Resources
WHERE     (TimeByDay BETWEEN '" + _stdate + @"' AND '" + _enddate + @"') AND
(NOT EXISTS
(SELECT     *
FROM         [#t2] WHERE MSP_TimeByDay.TimeByDay = [#t2].t_date AND t_Resources.ResUID = [#t2].ResUID))

INSERT INTO #t2
SELECT #t3.missing_ResUID, 'E38038FA-F8CA-47D1-BFD4-6B45B8462972',#t3.ResName,'NotClocked',#t3.missing_date, #t3.missing_wkday, 1, 8, 8,0,8
FROM #t3 WHERE #t3.missing_wkday <> '1' AND #t3.missing_wkday <> '7'

drop table #t3

SELECT #t2.ResUID, SUM(#t2.normalizedtime) AS total_paid_leave INTO #t4
FROM #t2
WHERE #t2.t_type = 1 AND #t2.ProjName = 'Paid Leave-Public Holiday'
GROUP By #t2.ResUID
SELECT #t2.ResUID,#t2.ProjUID,
SUM(
	(CONVERT(NUMERIC(18,14),#t2.normalizedtime) /(CASE WHEN nortotal.total_Proj = 0 THEN 1.0 ELSE CONVERT(NUMERIC(18,14),nortotal.total_Proj) END)
													)*CONVERT(NUMERIC(18,14),ISNULL(#t4.total_paid_leave,0)) + CONVERT(NUMERIC(18,14),#t2.normalizedtime)
) AS normalized_proj_time
INTO #t5
FROM #t2 INNER JOIN
(select #t2.ResUID, SUM(#t2.normalizedtime) AS total_Proj FROM #t2 WHERE t_type = 0 GROUP BY #t2.ResUID) AS nortotal ON #t2.ResUID = nortotal.ResUID
LEFT OUTER JOIN #t4 ON #t2.ResUID = #t4.ResUID
WHERE #t2.t_type = 0
GROUP BY #t2.ResUID, #t2.ProjUID, #t2.ProjName, #t2.t_type

--select * from #t2
drop table #t4

SELECT #t2.ResUID, #t2.ProjUID, #t2.ProjName, #t2.t_type, SUM (convert(numeric(18,14),#t2.normalizedtime)) AS nor_time
INTO #t6
FROM #t2
GROUP By #t2.ResUID, #t2.ProjUID, #t2.ProjName,#t2.t_type

drop table #t2

select #t6.ResUID, #t6.ProjUID, #t6.ProjName,
CASE WHEN #t6.t_type = 0 THEN #t5.normalized_proj_time ELSE #t6.nor_time END AS total_time,
CASE WHEN #t6.t_type = 0 THEN (convert (float,#t5.normalized_proj_time)/res_total.res_total_time)*100
ELSE (convert(float,#t6.nor_time) /res_total.res_total_time)*100 END
AS pct_total_time, #t6.t_type INTO #t7
from #t6 LEFT OUTER JOIN #t5 on #t6.ResUID = #t5.ResUID and #t6.ProjUID = #t5.ProjUID LEFT OUTER JOIN
(select #t6.ResUID, SUM (#t6.nor_time) AS res_total_time FROM #t6 GROUP BY #t6.ResUID) AS res_total ON #t6.ResUID = res_total.ResUID
where #t6.ProjName <> 'Paid Leave-Public Holiday'
order by #t6.ResUID
drop table #t5
drop table #t6
select #t7.ResUID, #t7.ProjUID, #t7.ProjName, #t7.total_time, #t7.pct_total_time,uv_res.ResourceName,
ISNULL (uv_proj.Project_RC_Code, #t7.ProjName) AS costcode, ISNULL(uv_res.ResourceEmailAddress,'NoEmail') AS res_email, ISNULL(uv_res.RBS, 'NoRBS') AS RBS
FROM #t7 left outer join dbo.MSP_EpmResource_UserView as uv_res on #t7.ResUID = uv_res.ResourceUID
left outer join dbo.MSP_EpmProject_UserView as uv_proj on #t7.ProjUID = uv_proj.ProjectUID
order by #t7.resuid, #t7.t_type

drop table #t7
";
            string rc_code_qry = @"
                SELECT      MemberFullValue
                FROM        MSPLT_Project_RC_Code_UserView
                WHERE       (ParentLookupMemberUID IS NOT NULL)
                ORDER BY    CAST(MemberFullValue AS varchar(500))
            ";

            #endregion sqlqry

            WindowsImpersonationContext wik = null;
            wik = WindowsIdentity.Impersonate(IntPtr.Zero);
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                SqlConnection con = new SqlConnection(MyConfiguration.GetDataBaseConnectionString(siteurl));
                con.Open();
                DataSet dt             = new DataSet();
                DataSet rc_code_dt     = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(gridqry, con));
                adapter.Fill(dt);
                DataTable maintbl = dt.Tables[0];
                adapter           = new SqlDataAdapter(new SqlCommand(rc_code_qry, con));
                adapter.Fill(rc_code_dt);
                DataTable rc_code_table = rc_code_dt.Tables[0];
                MyConfiguration.ErrorLog("Main Table Row count = " + dt.Tables[0].Rows.Count, EventLogEntryType.SuccessAudit);
                maintbl.Columns.Add("HRISID");
                maintbl.Columns.Add("OLDEMPID");
                maintbl.Columns.Add("PayRolStDate");
                maintbl.Columns.Add("PayRolEntity");
                maintbl.Columns.Add("staftype");
                string pctcolumntype   = maintbl.Columns["pct_total_time"].DataType.ToString();
                DataView view          = new DataView(dt.Tables[0]);
                DataTable resourcelist = view.ToTable(true, "ResourceName", "res_email");
                string resname         = string.Empty;
                string resemail        = string.Empty;
                try
                {
                    using (SPSite site = new SPSite(MyConfiguration.frm_siteurl_GetSiteURL(siteurl)))
                    {
                        foreach (DataRow row in resourcelist.Rows)
                        {
                            SPWeb hrWeb                = site.AllWebs["HRMV02"];
                            SPList res_detail          = hrWeb.Lists["Resource Details"];
                            SPList seconded            = hrWeb.Lists["Resource Secondment"];
                            SPQuery qry                = new SPQuery();
                            qry.Query                  = @"<Where>
                                        <Eq>
                                            <FieldRef Name='EMail'/>
                                            <Value Type='Text'>" + row[1].ToString() + @"</Value>
                                        </Eq>
                                    </Where>";
                            SPListItemCollection items = res_detail.GetItems(qry);

                            #region secondment and resource data from HR Manipulation

                            if (items.Count > 0)
                            {
                                foreach (SPListItem itm in items)
                                {
                                    #region secondment query

                                    SPQuery secondedqry = new SPQuery();
                                    secondedqry.Query   = @"
<ViewFields>
    <FieldRef Name='Department' />
    <FieldRef Name='From_x0020_Date' />
    <FieldRef Name='To_x0020_Date' />
</ViewFields>
<Where>
    <And>
        <Eq>
            <FieldRef Name='Resource_x0020_Name' />
            <Value Type='Lookup'>" + itm["Resource Name"].ToString() + @"</Value>
        </Eq>
        <Leq>
            <FieldRef Name='From_x0020_Date' />
            <Value Type='DateTime'>" + string.Format("{0:yyyy-MM-dd}", _enddateformatted) + @"</Value>
        </Leq>
    </And>
</Where>
<OrderBy>
    <FieldRef Name='ID' Ascending='False' />
</OrderBy>
<RowLimit>1</RowLimit>
                                            ";

                                    #endregion secondment query

                                    //MyConfiguration.ErrorLog("List Resource Name : " + itm["Resource Name"].ToString(), EventLogEntryType.SuccessAudit);
                                    //MyConfiguration.ErrorLog("List Department Name : " + itm["Department"].ToString(), EventLogEntryType.SuccessAudit);
                                    foreach (DataRow ro in maintbl.Select("res_email = '" + row[1].ToString() + "'"))
                                    {
                                        ro["ResourceName"] = itm["Resource Name"].ToString();
                                        if ((itm.Fields.ContainsField("HRIS_x0020_ID")) && (itm["HRIS_x0020_ID"] != null))
                                        {
                                            ro["HRISID"] = itm["HRIS_x0020_ID"].ToString();
                                        }
                                        else
                                        {
                                            ro["HRISID"] = "-";
                                        }
                                        if ((itm.Fields.ContainsField("Old_x0020_Emp_x0020_Num")) && (itm["Old_x0020_Emp_x0020_Num"] != null))
                                        {
                                            ro["OLDEMPID"] = itm["Old_x0020_Emp_x0020_Num"].ToString();
                                        }
                                        else
                                        {
                                            ro["OLDEMPID"] = "-";
                                        }
                                        if ((itm.Fields.ContainsField("Date_x0020_On_x0020_Board")) && (itm["Date_x0020_On_x0020_Board"] != null))
                                        {
                                            ro["PayRolStDate"] = itm["Date_x0020_On_x0020_Board"].ToString();
                                        }
                                        else
                                        {
                                            ro["PayRolStDate"] = "01-01-1900";
                                        }
                                        if ((itm.Fields.ContainsField("Department")) && (itm["Department"] != null))
                                        {
                                            string[] deptname   = itm["Department"].ToString().Split(new char[] { ';', '#' }, StringSplitOptions.RemoveEmptyEntries);
                                            SPList deptlist     = hrWeb.Lists["Department"];
                                            SPListItem deptitem = deptlist.GetItemById(Convert.ToInt32(deptname[0].ToString()));
                                            string[] entityname = deptitem["Entity"].ToString().Split(new char[] { ';', '#' }, StringSplitOptions.RemoveEmptyEntries);
                                            ro["PayRolEntity"]  = entityname[1].ToString();
                                            if (ro["ProjName"].ToString() != string.Empty && ro["ProjName"].ToString() == "Orignal Department")
                                            {
                                                ro["ProjName"] = deptname[1].ToString();
                                                if ((deptitem.Fields.ContainsField("Cost_x0020_Code")) && (deptitem["Cost_x0020_Code"] != null))
                                                {
                                                    ro["costcode"] = deptitem["Cost_x0020_Code"].ToString();
                                                }
                                                else
                                                {
                                                    ro["costcode"] = deptname[1].ToString();
                                                }
                                            }
                                        }
                                        else
                                        {
                                            ro["PayRolEntity"] = "-";
                                        }
                                        if ((itm.Fields.ContainsField("Resource_x0020_Type")) && (itm["Resource_x0020_Type"] != null))
                                        {
                                            ro["staftype"] = itm["Resource_x0020_Type"].ToString();
                                        }
                                        else
                                        {
                                            ro["staftype"] = "-";
                                        }
                                        SPListItemCollection secondeditems = seconded.GetItems(secondedqry);
                                        if (secondeditems.Count > 0)
                                        {
                                            foreach (SPListItem secondeditem in secondeditems)
                                            {
                                                if ((secondeditem.Fields.ContainsField("Department")) && (secondeditem["Department"] != null))
                                                {
                                                    string[] deptname   = secondeditem["Department"].ToString().Split(new char[] { ';', '#' }, StringSplitOptions.RemoveEmptyEntries);
                                                    SPList deptlist     = hrWeb.Lists["Department"];
                                                    SPListItem deptitem = deptlist.GetItemById(Convert.ToInt32(deptname[0].ToString()));
                                                    string[] entityname = deptitem["Entity"].ToString().Split(new char[] { ';', '#' }, StringSplitOptions.RemoveEmptyEntries);
                                                    ro["PayRolEntity"]  = entityname[1].ToString();
                                                    if (ro["ProjName"].ToString() != string.Empty && ro["ProjName"].ToString() == "TO BAU")
                                                    {
                                                        ro["ProjName"] = deptname[1].ToString();
                                                        if ((deptitem.Fields.ContainsField("Cost_x0020_Code")) && (deptitem["Cost_x0020_Code"] != null))
                                                        {
                                                            ro["costcode"] = deptitem["Cost_x0020_Code"].ToString();
                                                        }
                                                        else
                                                        {
                                                            ro["costcode"] = deptname[1].ToString();
                                                        }
                                                    }
                                                    //MyConfiguration.ErrorLog("Resource Name: " + ro["ResourceName"].ToString(), EventLogEntryType.SuccessAudit);
                                                    //MyConfiguration.ErrorLog("Deptment: " + ro["ProjName"].ToString(), EventLogEntryType.SuccessAudit);
                                                    //MyConfiguration.ErrorLog("Code: " + ro["costcode"].ToString(), EventLogEntryType.SuccessAudit);
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            else
                            {
                                foreach (DataRow ro in maintbl.Select("res_email = '" + row[1].ToString() + "'"))
                                {
                                    ro["HRISID"]       = "NA in HRM";
                                    ro["OLDEMPID"]     = "NA in HRM";
                                    ro["PayRolStDate"] = "01-01-1900";
                                    ro["PayRolEntity"] = "NA in HRM";
                                    ro["staftype"]     = "Internal";
                                }
                            }

                            #endregion secondment and resource data from HR Manipulation
                        }
                    }
                }
                catch (Exception ex)
                {
                    MyConfiguration.ErrorLog("Error in accessing SPSite" + ex.Message, EventLogEntryType.Error);
                }
                maintbl.DefaultView.Sort = "staftype DESC";
                DataView pivotview       = new DataView(maintbl);
                DataTable pivotviewtable = pivotview.ToTable(false, "ResUID", "costcode", "pct_total_time");
                var qyery = (from c in maintbl.AsEnumerable()
                             group c by new { cost_code = c.Field <string>("costcode"), res_uid = c.Field <Guid>("ResUID") } into grp
                             orderby grp.Key.res_uid, grp.Key.cost_code
                             select new
                {
                    ResUID = grp.Key.res_uid,
                    costcode = grp.Key.cost_code,
                    pct_total_time = grp.Sum(r => r.Field <double>("pct_total_time"))
                });
                DataTable pvtable   = qyery.CopyToDataTable();
                DataTable resulttbl = new DataTable();
                try
                {
                    resulttbl = Pivot(pvtable, "ResUID", "costcode", "pct_total_time");
                }
                catch (Exception ex) { MyConfiguration.ErrorLog("Error in Pivot data: " + ex.Message, EventLogEntryType.Error); }
                try
                {
                    resulttbl.Columns.Add("RBS");
                    resulttbl.Columns.Add("ResourceName");
                    resulttbl.Columns.Add("HRISID");
                    resulttbl.Columns.Add("OLDEMPID");
                    resulttbl.Columns.Add("PayRolStDate");
                    resulttbl.Columns.Add("PayRolEntity");
                    resulttbl.Columns.Add("staftype");
                    resulttbl.Columns["RBS"].SetOrdinal(1);
                    resulttbl.Columns["ResourceName"].SetOrdinal(2);
                    resulttbl.Columns["HRISID"].SetOrdinal(3);
                    resulttbl.Columns["OLDEMPID"].SetOrdinal(4);
                    resulttbl.Columns["PayRolStDate"].SetOrdinal(5);
                    resulttbl.Columns["PayRolEntity"].SetOrdinal(6);
                    resulttbl.Columns["staftype"].SetOrdinal(7);
                }
                catch (Exception ex) { MyConfiguration.ErrorLog("Error Adding Field: " + ex.Message, EventLogEntryType.Error); }
                try
                {
                    if (rc_code_table.Rows.Count > 0)
                    {
                        int columnindex = 8;
                        foreach (DataRow ro in rc_code_table.Rows)
                        {
                            bool found = false;
                            foreach (DataColumn resultcolumn in resulttbl.Columns)
                            {
                                if (ro[0].ToString() == resultcolumn.ColumnName)
                                {
                                    found = true;
                                }
                            }
                            if (found == false)
                            {
                                resulttbl.Columns.Add(ro[0].ToString());
                            }
                            resulttbl.Columns[ro[0].ToString()].SetOrdinal(columnindex++);
                        }
                    }
                }
                catch (Exception ex) { MyConfiguration.ErrorLog("Error in adding column:" + ex.Message, EventLogEntryType.Error); }
                DataView resdetailview   = new DataView(maintbl);
                DataTable resdetailtable = resdetailview.ToTable(true, "RBS", "ResUID", "ResourceName", "HRISID", "OLDEMPID", "PayRolStDate", "PayRolEntity", "staftype");
                try
                {
                    foreach (DataRow ro in resulttbl.Rows)
                    {
                        foreach (DataRow mtlrow in resdetailtable.Select("ResUID = '" + ro["ResUID"] + "'"))
                        {
                            ro["RBS"]          = mtlrow["RBS"];
                            ro["ResourceName"] = mtlrow["ResourceName"];
                            ro["HRISID"]       = mtlrow["HRISID"].ToString();
                            ro["OLDEMPID"]     = mtlrow["OLDEMPID"];
                            if (mtlrow["PayRolStDate"].ToString() != "00-00-000")
                            {
                                ro["PayRolStDate"] = Convert.ToDateTime(mtlrow["PayRolStDate"]).ToString("dd-MM-yyyy");
                            }
                            else
                            {
                                ro["PayRolStDate"] = mtlrow["PayRolStDate"];
                            }
                            ro["PayRolEntity"] = mtlrow["PayRolEntity"];
                            ro["staftype"]     = mtlrow["staftype"];
                        }
                    }
                }
                catch (Exception ex) { MyConfiguration.ErrorLog("Error in Adding Resource Details:" + ex.Message, EventLogEntryType.Error); }

                #region export function

                WindowsImpersonationContext wic = null;
                try
                {
                    try
                    {
                        wic = WindowsIdentity.Impersonate(IntPtr.Zero);
                    }
                    catch (Exception)
                    {
                    }
                    // You should be very carefull with sharepoint layout folder, we have to specify full control permission to everybody.
                    string FolderPath = Server.MapPath("/_layouts/CIMB_TimeSheet/CSVFiles/");
                    if (Directory.Exists(FolderPath))
                    {
                        foreach (string file in Directory.GetFiles(FolderPath))
                        {
                            try
                            {
                                File.Delete(file);
                            }
                            catch (Exception ex)
                            {
                            }
                        }
                    }
                    else
                    {
                        Directory.CreateDirectory(FolderPath);
                    }
                    string clientfilename = DateTime.Now.ToString("ddMMyyhhmmss") + DateTime.Now.Millisecond.ToString();
                    string filename       = FolderPath + clientfilename + ".csv";
                    var writer            = new StreamWriter(filename);

                    //Writing column name at first row
                    string columnheaderline = string.Empty;

                    for (int i = 1; i < resulttbl.Columns.Count; i++)
                    {
                        columnheaderline = columnheaderline + resulttbl.Columns[i].ColumnName + ",";
                    }
                    writer.WriteLine(columnheaderline);
                    // Writing row values
                    foreach (DataRow row in resulttbl.Rows)
                    {
                        string columnvalue = string.Empty;
                        for (int i = 1; i < resulttbl.Columns.Count; i++)
                        {
                            if (i > 7)
                            {
                                if (row[i].ToString() != string.Empty)
                                {
                                    columnvalue = columnvalue + row[i] + "%,";
                                }
                                else
                                {
                                    columnvalue = columnvalue + "0%,";
                                }
                            }
                            else
                            {
                                columnvalue = columnvalue + row[i] + ",";
                            }
                        }
                        writer.WriteLine(columnvalue);
                    }
                    try
                    {
                        writer.Flush();
                        writer.Close();
                        writer.Dispose();
                    }
                    catch (Exception ex) { MyConfiguration.ErrorLog("Error in writing CSV : " + ex.Message, EventLogEntryType.Error); }
                    // Sending files here
                    Response.ContentType = "application/CSV";
                    Response.AddHeader("content-disposition", "attachment; filename=Res_Captilisation" + clientfilename + ".csv");
                    Response.TransmitFile(filename);
                    Response.Flush();
                    Response.End();
                }
                catch (Exception)
                {
                }

                #endregion export function
            });
        }