public static s_GridResult GetDataTable(string nd, int rows, int page, string sidx, string sord)
        {
            try
            {
                string   _stdate           = HttpContext.Current.Request.QueryString["_stdate"].ToString();
                string   _enddate          = HttpContext.Current.Request.QueryString["_enddate"].ToString();
                DateTime _enddateformatted = Convert.ToDateTime(_enddate);
                //MyConfiguration.ErrorLog("Start Date : " + _stdate + "   End Date : " + _enddate, EventLogEntryType.SuccessAudit);
                string siteurl                  = HttpContext.Current.Request.UrlReferrer.ToString();
                int    startindex               = (page - 1);
                int    endindex                 = page;
                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 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
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 projwkspaceurlqry        = @"SELECT     ProjectUID, ProjectWorkspaceInternalHRef
                                            FROM         MSP_EpmProject_UserView";
                WindowsImpersonationContext wik = null;
                wik = WindowsIdentity.Impersonate(IntPtr.Zero);
                s_GridResult result = new s_GridResult();
                //string siteurl = "http://epmdr/2011";  //right now i am hard coding it in the myconfiguration itself
                try
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        SqlConnection con = new SqlConnection(MyConfiguration.GetDataBaseConnectionString(siteurl));
                        con.Open();
                        DataSet dt = new DataSet();
                        DataSet pjwkspacerecord = new DataSet();
                        SqlDataAdapter adapter  = new SqlDataAdapter(new SqlCommand(gridqry, con));
                        adapter.Fill(dt);
                        adapter = new SqlDataAdapter(new SqlCommand(projwkspaceurlqry, con));
                        adapter.Fill(pjwkspacerecord);
                        DataTable maintbl = dt.Tables[0];
                        maintbl.Columns.Add("HRISID");
                        maintbl.Columns.Add("OLDEMPID");
                        maintbl.Columns.Add("PayRolStDate");
                        maintbl.Columns.Add("PayRolEntity");
                        maintbl.Columns.Add("staftype");
                        //maintbl.Columns.Add("ProjRole");
                        DataView view          = new DataView(dt.Tables[0]);
                        DataTable resourcelist = view.ToTable(true, "ResourceName", "res_email");
                        string resname         = string.Empty;
                        string resemail        = string.Empty;
                        //DataTable projlist = view.ToTable(true, "ProjUID", "ResourceName", "res_email");

                        try
                        {
                            using (SPSite site = new SPSite(MyConfiguration.frm_siteurl_GetSiteURL(siteurl)))
                            {
                                #region Project Role Manipulation

                                /*
                                 * foreach (DataRow row in projlist.Rows)
                                 * {
                                 *  foreach (DataRow siteurlrow in pjwkspacerecord.Tables[0].Select("ProjectUID ='" + row["ProjUID"] + "'"))
                                 *  {
                                 *      string pjwkspaceurl = siteurlrow["ProjectWorkspaceInternalHRef"].ToString();
                                 *      try
                                 *      {
                                 *          SPWeb pjwkspaceWeb = site.AllWebs["pjwkspaceurl"];
                                 *          try
                                 *          {
                                 *              SPList pjwkspace_list = pjwkspaceWeb.Lists["Project Resource Details"];
                                 *              SPQuery resdetailqry = new SPQuery();
                                 *              resdetailqry.Query = @"
                                 *              <Where>
                                 *                  <Eq>
                                 *                      <FieldRef Name='Resource_x0020_Name' />
                                 *                      <Value Type='User'>" + row["ResourceName"] + @"</Value>
                                 *                  </Eq>
                                 *              </Where>
                                 *              <OrderBy>
                                 *                  <FieldRef Name='ID' Ascending='False' />
                                 *              </OrderBy>
                                 *              <RowLimit>1</RowLimit>";
                                 *              SPListItemCollection resdetailitems = pjwkspace_list.GetItems(resdetailqry);
                                 *              if (resdetailitems.Count > 0)
                                 *              {
                                 *                  foreach (SPListItem itm in resdetailitems)
                                 *                  {
                                 *                      if ((itm.Fields.ContainsField("Project_x0020_Role")) && (itm["Project_x0020_Role"] != null))
                                 *                      {
                                 *                          foreach (DataRow maintblrow in maintbl.Select("ProjectUID ='" + row["ProjUID"] + "'"))
                                 *                          {
                                 *                              maintblrow["ProjRole"] = itm["Project_x0020_Role"].ToString();
                                 *                          }
                                 *                      }
                                 *                      else
                                 *                      {
                                 *                          MyConfiguration.ErrorLog(row["ResourceName"].ToString() + " don't have any Role in this Project", EventLogEntryType.Error);
                                 *
                                 *                          foreach (DataRow maintblrow in maintbl.Select("ProjUID ='" + row["ProjUID"] + "' AND ResourceName ='" + row["ResourceName"] + "'"))
                                 *                          {
                                 *                              maintblrow["ProjRole"] = "NA";
                                 *                          }
                                 *                      }
                                 *                  }
                                 *              }
                                 *              else
                                 *              {
                                 *                  MyConfiguration.ErrorLog(row["ProjUID"].ToString() + " Don't have any Resource Detail Record", EventLogEntryType.Error);
                                 *
                                 *                  foreach (DataRow maintblrow in maintbl.Select("ProjUID ='" + row["ProjUID"] + "' AND ResourceName ='" + row["ResourceName"] + "'"))
                                 *                  {
                                 *                      maintblrow["ProjRole"] = "NA";
                                 *                  }
                                 *              }
                                 *          }
                                 *          catch (Exception ex)
                                 *          {
                                 *              MyConfiguration.ErrorLog(row["ProjUID"].ToString() + " Don't have Resource Detail List", EventLogEntryType.Error);
                                 *              foreach (DataRow maintblrow in maintbl.Select("ProjUID ='" + row["ProjUID"] + "'"))
                                 *              {
                                 *                  maintblrow["ProjRole"] = "NA";
                                 *              }
                                 *          }
                                 *      }
                                 *      catch (Exception ex)
                                 *      {
                                 *          MyConfiguration.ErrorLog(row["ProjUID"].ToString() + " Don't have Workspace", EventLogEntryType.Error);
                                 *          foreach (DataRow maintblrow in maintbl.Select("ProjUID ='" + row["ProjUID"] + "'"))
                                 *          {
                                 *              maintblrow["ProjRole"] = "NA";
                                 *          }
                                 *      }
                                 *  }
                                 * }
                                 */

                                #endregion Project Role Manipulation

                                foreach (DataRow row in resourcelist.Rows)
                                {
                                    SPWeb hrWeb = site.AllWebs["HRMV02"];
                                    //MyConfiguration.ErrorLog("SPWeb URL : " + hrWeb.Url.ToString(), EventLogEntryType.SuccessAudit);
                                    //SPDataSource resdetds = new SPDataSource();
                                    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>";
                                    //resdetds.List = hrWeb.Lists["Resource Details"];
                                    SPListItemCollection items = res_detail.GetItems(qry);
                                    //MyConfiguration.ErrorLog("Resource Details Return Rows : " + items.Count, EventLogEntryType.SuccessAudit);
                                    if (items.Count > 0)
                                    {
                                        foreach (SPListItem itm in items)
                                        {
                                            #region Secondment Data manipulation

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

                                            #endregion Secondment Data manipulation
                                        }
                                    }
                                    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";
                                        }
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MyConfiguration.ErrorLog("Error in accessing SPSite" + ex.Message, EventLogEntryType.Error);
                        }
                        List <s_RowData> rowsadded = new List <s_RowData>();
                        int idx = 1;
                        maintbl.DefaultView.Sort = "staftype DESC";
                        try
                        {
                            foreach (DataRow row in maintbl.Rows)
                            {
                                s_RowData newrow = new s_RowData();
                                newrow.id        = idx++;
                                //Tabel Column List -- ResUID - 0,ProjUID - 1,ProjName - 2,total_time - 3,pct_total_time - 4,ResourceName - 5,
                                //costcode - 6,res_email - 7,HRISID - 8,OLDEMPID - 9,PayRolStDate - 10,PayRolEntity - 11,staftype - 12
                                newrow.cell    = new string[8];                                             //total number of columns
                                newrow.cell[0] = row[5].ToString();                                         //resource name from project server
                                newrow.cell[1] = row[8].ToString();                                         //HRIS Number
                                newrow.cell[2] = row[9].ToString();                                         //OldEmpNo
                                newrow.cell[3] = row[11].ToString();                                        //Pay Role Entity
                                newrow.cell[4] = row[10].ToString();                                        //Pay Role Start Date
                                newrow.cell[5] = row[2].ToString();                                         //project name from server
                                newrow.cell[6] = row[6].ToString();                                         //cost code
                                newrow.cell[7] = Convert.ToString(Convert.ToDouble(row[3].ToString()) / 8); //total time clocked
                                rowsadded.Add(newrow);
                            }
                        }
                        catch (Exception ex)
                        {
                            MyConfiguration.ErrorLog("Error At Manipulating Joson Data" + ex.Message, EventLogEntryType.Error);
                        }
                        result.rows   = rowsadded.ToArray();
                        result.page   = page;
                        result.total  = dt.Tables[0].Rows.Count;
                        result.record = rowsadded.Count;
                    });
                }
                catch (Exception ex)
                {
                    MyConfiguration.ErrorLog("Error at SPSecurity Delegate :" + ex.Message, EventLogEntryType.Error);
                }
                return(result);
            }
            catch (Exception ex)
            {
                MyConfiguration.ErrorLog("Error at web method due to " + ex.Message, EventLogEntryType.Error);
            }
            return(new s_GridResult());
        }
Example #2
0
        public static s_GridResult_subgrid GetSubGridData(string nd, int rows, int page, string sidx, string sord)
        {
            try
            {
                string   _stdate           = HttpContext.Current.Request.QueryString["_stdate"].ToString();
                string   _enddate          = HttpContext.Current.Request.QueryString["_enddate"].ToString();
                string   _periodname       = HttpContext.Current.Request.QueryString["_periodname"].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];
                int      startindex = (page - 1);
                int      endindex   = page;
                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) + ")";
                MyConfiguration.ErrorLog("My Resource UIDs : " + filterresource, EventLogEntryType.Information);
                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		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') AND (PeriodName = '"     + _periodname + @"')
WHERE		(PeriodName = '"     + _periodname + @"')
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
";
                s_GridResult_subgrid result = new s_GridResult_subgrid();
                try
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        DataSet dt             = new DataSet();
                        SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(gridqry, con));
                        adapter.Fill(dt);
                        DataTable maintbl = dt.Tables[0];
                        List <s_RowData_subgrid> rowsadded = new List <s_RowData_subgrid>();
                        int idx = 1;
                        try
                        {
                            foreach (DataRow row in maintbl.Rows)
                            {
                                s_RowData_subgrid newrow = new s_RowData_subgrid();
                                newrow.id = idx++;
                                //Tabel Column List -- TM Name - 0,Resource Name - 1,In Progress - 2,Not Created - 3,Submitted - 4
                                newrow.cell    = new string[6];     //total number of columns
                                newrow.cell[0] = row[0].ToString(); //Timesheet Manager
                                newrow.cell[1] = row[1].ToString(); //Resource Name
                                newrow.cell[2] = row[2].ToString(); //In Progress
                                newrow.cell[3] = row[3].ToString(); //Not Created
                                newrow.cell[4] = row[4].ToString(); //Submitted
                                newrow.cell[5] = row[5].ToString(); //Approved
                                rowsadded.Add(newrow);
                            }
                        }
                        catch (Exception ex)
                        {
                            MyConfiguration.ErrorLog("Error At Manipulating Json Data" + ex.Message, EventLogEntryType.Error);
                        }
                        result.rows   = rowsadded.ToArray();
                        result.page   = page;
                        result.total  = dt.Tables[0].Rows.Count;
                        result.record = rowsadded.Count;
                    });
                }
                catch (Exception ex)
                {
                    MyConfiguration.ErrorLog("Error at SPSecurity Delegate :" + ex.Message, EventLogEntryType.Error);
                }
                return(result);
            }
            catch (Exception ex)
            {
                MyConfiguration.ErrorLog("Error at web method due to " + ex.Message, EventLogEntryType.Error);
            }
            return(new s_GridResult_subgrid());
        }
Example #3
0
        public static string GetDataTable(string resuid)
        {
            MyConfiguration.ErrorLog(resuid, EventLogEntryType.SuccessAudit);
            string OutputXMLValue = "<?xml version='1.0' encoding='utf-8' ?><rows>";

            try
            {
                var    curesuid  = new Guid(resuid);
                string getresqry = @" SELECT   ResourceUID
                                            FROM     MSP_EpmResource_UserView
                                            WHERE    (ResourceTimesheetManagerUID = '" + curesuid + @"')";
                string siteurl   = HttpContext.Current.Request.UrlReferrer.ToString();
                WindowsImpersonationContext wik = WindowsIdentity.Impersonate(IntPtr.Zero);
                try
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate
                    {
                        var con = new SqlConnection(MyConfiguration.GetDataBaseConnectionString(siteurl));
                        con.Open();
                        var resoures_dt = new DataSet();
                        var adapter     = new SqlDataAdapter(new SqlCommand(getresqry, con));
                        adapter.Fill(resoures_dt);
                        var res_uids = string.Empty;
                        if (resoures_dt.Tables.Count > 0)
                        {
                            var resources_table = resoures_dt.Tables[0];
                            if (resources_table.Rows.Count > 0)
                            {
                                foreach (DataRow ro in resources_table.Rows)
                                {
                                    res_uids = res_uids + "'" + ro["ResourceUID"] + "',";
                                }
                            }
                            else
                            {
                                res_uids = "'" + curesuid + "',";
                            }
                        }
                        res_uids       = "(" + res_uids.Substring(0, res_uids.Length - 1) + ")";
                        string gridqry = @"
SELECT     MSP_TimesheetResource.ResourceName, MSP_TimesheetPeriod.PeriodName, CASE WHEN (MSP_TimesheetProject.ProjectName = 'Administrative')
                      THEN MSP_TimesheetClass.ClassName ELSE MSP_TimesheetProject.ProjectName END AS ProjectName,
                      SUM(MSP_TimesheetActual.ActualWorkBillable + MSP_TimesheetActual.ActualWorkNonBillable + MSP_TimesheetActual.ActualOvertimeWorkBillable + MSP_TimesheetActual.ActualOvertimeWorkNonBillable)
                       AS Actual,  MSP_Timesheet.TimesheetUID
FROM         MSP_TimesheetPeriod INNER JOIN
                      MSP_Timesheet ON MSP_TimesheetPeriod.PeriodUID = MSP_Timesheet.PeriodUID INNER JOIN
                      MSP_TimesheetActual INNER JOIN
                      MSP_TimesheetResource ON MSP_TimesheetActual.LastChangedResourceNameUID = MSP_TimesheetResource.ResourceNameUID INNER JOIN
                      MSP_TimesheetLine ON MSP_TimesheetActual.TimesheetLineUID = MSP_TimesheetLine.TimesheetLineUID INNER JOIN
                      MSP_TimesheetProject ON MSP_TimesheetLine.ProjectNameUID = MSP_TimesheetProject.ProjectNameUID ON
                      MSP_Timesheet.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID AND
                      MSP_Timesheet.TimesheetUID = MSP_TimesheetLine.TimesheetUID INNER JOIN
                      MSP_TimesheetClass ON MSP_TimesheetLine.ClassUID = MSP_TimesheetClass.ClassUID
                      WHERE     (MSP_Timesheet.TimesheetStatusID = 1) AND (MSP_TimesheetResource.ResourceUID IN " + res_uids + @")
GROUP BY MSP_TimesheetPeriod.PeriodName, MSP_TimesheetResource.ResourceName, CASE WHEN (MSP_TimesheetProject.ProjectName = 'Administrative')
                      THEN MSP_TimesheetClass.ClassName ELSE MSP_TimesheetProject.ProjectName END, MSP_Timesheet.TimesheetUID
";
                        //
                        var dt  = new DataSet();
                        adapter = new SqlDataAdapter(new SqlCommand(gridqry, con));
                        adapter.Fill(dt);
                        if (dt.Tables.Count > 0)
                        {
                            var maintbl     = dt.Tables[0];
                            OutputXMLValue += "<page>1</page><total>" + maintbl.Rows.Count + "</total><records>1</records>";
                            try
                            {
                                int RowIndex = 1;
                                int resourceIndex;
                                int periodIndex;
                                //Tabel Column List -- ResourceName-0,PeriodName-1,ProjectName-2, Actual-3
                                // Get all the resource names first
                                var resourcenames =
                                    from c in maintbl.AsEnumerable()
                                    group c by c["ResourceName"];
                                // loop for all the resources
                                foreach (IGrouping <object, DataRow> grouping in resourcenames)
                                {
                                    OutputXMLValue += "<row><cell>" + grouping.Key + "</cell>";
                                    var periodnames =
                                        (from c in maintbl.AsEnumerable()
                                         where
                                         c.Field <string>("ResourceName").
                                         Equals(grouping.Key)
                                         select c);
                                    var sum         = periodnames.Sum(row => row.Field <decimal>("Actual"));
                                    OutputXMLValue += "<cell>" + sum + "</cell><cell>false</cell><cell>" + grouping.First()["TimesheetUID"] + "</cell><cell>1</cell><cell>0</cell>";
                                    resourceIndex   = RowIndex++;
                                    if (periodnames.Count() > 0)
                                    {
                                        OutputXMLValue += "<cell>false</cell><cell>true</cell></row>";
                                    }
                                    else
                                    {
                                        OutputXMLValue += "<cell>true</cell><cell>true</cell></row>";
                                    }

                                    var groupedperiodnames = from c in periodnames.AsEnumerable()
                                                             group c by c["PeriodName"];
                                    // loop for all the Periodnames
                                    foreach (IGrouping <object, DataRow> row in groupedperiodnames)
                                    {
                                        if (row.Count() > 0)
                                        {
                                            OutputXMLValue  += "<row><cell>" + row.First()["PeriodName"] + "</cell>";
                                            var projectnames =
                                                (from c in
                                                 periodnames.AsEnumerable()
                                                 where
                                                 c.Field <string>(
                                                     "PeriodName").Equals(
                                                     row.First()["PeriodName"])
                                                 select c);
                                            sum =
                                                projectnames.Sum(
                                                    r =>
                                                    r.Field <decimal>("Actual"));
                                            OutputXMLValue += "<cell>" + sum +
                                                              "</cell><cell>false</cell><cell>" + row.First()["TimesheetUID"] + "</cell><cell>2</cell><cell>" +
                                                              resourceIndex +
                                                              "</cell>";
                                            periodIndex = RowIndex++;
                                            if (projectnames.Count() > 0)
                                            {
                                                OutputXMLValue +=
                                                    "<cell>false</cell><cell>false</cell></row>";
                                            }
                                            else
                                            {
                                                OutputXMLValue +=
                                                    "<cell>true</cell><cell>true</cell></row>";
                                            }
                                            var groupedprojectnames =
                                                from c in
                                                projectnames.AsEnumerable()
                                                group c by c["ProjectName"];
                                            // loop for all projectnames
                                            foreach (
                                                IGrouping <object, DataRow>
                                                dataRows in
                                                groupedprojectnames)
                                            {
                                                if (dataRows.Count() > 0)
                                                {
                                                    OutputXMLValue +=
                                                        "<row><cell>" +
                                                        dataRows.First()["ProjectName"] +
                                                        "</cell>";
                                                    OutputXMLValue +=
                                                        "<cell>" +
                                                        dataRows.First()["Actual"] + "</cell><cell>false</cell><cell>" + dataRows.First()["TimesheetUID"] + "</cell><cell>3</cell><cell>" + periodIndex + "</cell>";
                                                    OutputXMLValue +=
                                                        "<cell>true</cell><cell>true</cell></row>";
                                                    RowIndex++;
                                                }
                                            }
                                        }
                                    }
                                }
                                //select c;
                            }
                            catch (Exception ex)
                            {
                                MyConfiguration.ErrorLog("Error At Manipulating XML Data due to " + ex.Message, EventLogEntryType.Error);
                            }
                        }
                    });
                }
                catch (Exception ex)
                {
                    MyConfiguration.ErrorLog("Error at SPSecurity Delegate :" + ex.Message, EventLogEntryType.Error);
                }
            }
            catch (Exception ex)
            {
                MyConfiguration.ErrorLog("Error at web method due to " + ex.Message, EventLogEntryType.Error);
            }
            return(OutputXMLValue + "</rows>");
        }