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);
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("RBS Resource List : " + 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 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
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,
COUNT(CASE WHEN ([TimeSheet Status] = 'Approved') THEN [TimeSheet Status] END) AS Approved
FROM [#t2]
--WHERE ([TimeSheet Status] <> 'Approved')
GROUP BY PeriodName
ORDER BY PeriodName
drop table #t2
";
s_GridResult result = new s_GridResult();
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> rowsadded = new List<s_RowData>();
int idx = 1;
try
{
foreach (DataRow row in maintbl.Rows)
{
s_RowData newrow = new s_RowData();
newrow.id = idx++;
//Tabel Column List -- ResourceName - 0,TimeSheet Period - 1,TimeSheet Period Status - 2
newrow.cell = new string[5]; //total number of columns
newrow.cell[0] = row[0].ToString(); //TimeSheet Period Name
newrow.cell[1] = row[1].ToString(); //In Progress
newrow.cell[2] = row[2].ToString(); //Not Created
newrow.cell[3] = row[3].ToString(); //Submitted
newrow.cell[4] = row[4].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();
}
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());
}
public static s_GridResult GetDataTable(string _search, string nd, int rows, int page, string sidx, string sord)
{
string user = userAccountName;
Debug.WriteLine(user + " KÄYTTÄJÄN NIMI GRID HAUSSA");
int startindex = (page - 1);
int endindex = page;
string sql = @"SELECT Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as Id
,[Title_9691DD10_7211_C835_E3E7_6B38AF8B8104] as Title
,[Description_59B77FD5_FE0E_D2B5_D541_0EBBD1EC9A2B] as Description
,[ResolutionDescription_85E8B5FA_3ECB_9B6C_0A02_A8C9EC085A39] as ResolutionDescription
,IncidentStatus.[DisplayName] as IncidentStatus
,[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688] as CreatedDate
,AssignedToUser.[DisplayName] as AssignedToUser
,AffectedUser.UserName_6AF77E23_669B_123F_B392_323C17097BBD AffectedUsername
,AffectedUser.DisplayName as AffectedUser
,AssignedToUser.UserName_6AF77E23_669B_123F_B392_323C17097BBD as AssignedUsername
,IncidentClassification.[DisplayName] as IncidentClassification
,[Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794] as Priority
,[ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E] as ResolvedDate
,[Escalated_525F1F92_CEB3_079D_C0A5_E7A06AC4D6A5] as Escalated
FROM [ServiceManager].[dbo].[MT_System$WorkItem$Incident]
INNER JOIN [ServiceManager].[dbo].[Relationship] AssignedToUserRel ON
[ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = AssignedToUserRel.[SourceEntityId]
AND AssignedToUserRel.[RelationshipTypeId] = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AssignedToUser ON
AssignedToUserRel.[TargetEntityId] = AssignedToUser.[BaseManagedEntityId]
INNER JOIN [ServiceManager].[dbo].[Relationship] AffectedUserRel ON
[ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = AffectedUserRel.[SourceEntityId]
AND AffectedUserRel.[RelationshipTypeId] = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'
INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AffectedUser ON
AffectedUserRel.[TargetEntityId] = AffectedUser.[BaseManagedEntityId]
INNER JOIN [ServiceManager].[dbo].[DisplayStringView] IncidentClassification ON
[ServiceManager].[dbo].[MT_System$WorkItem$Incident].[Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA] = IncidentClassification.LTStringId
AND IncidentClassification.LanguageCode = 'ENU'
INNER JOIN [ServiceManager].[dbo].[DisplayStringView] IncidentStatus ON
[ServiceManager].[dbo].[MT_System$WorkItem$Incident].[Status_785407A9_729D_3A74_A383_575DB0CD50ED] = IncidentStatus.LTStringId
AND IncidentStatus.LanguageCode = 'ENU'
WHERE AffectedUser.[UserName_6AF77E23_669B_123F_B392_323C17097BBD] = '" + user+"' ;";
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["tkuscsm-dwsConnectionString"].ConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(dt);
s_GridResult result = new s_GridResult();
List<s_RowData> rowsadded = new List<s_RowData>();
int idx = 1;
foreach (DataRow row in dt.Rows)
{
s_RowData newrow = new s_RowData();
newrow.id = idx++;
newrow.cell = new string[14]; //total number of columns
newrow.cell[0] = row[0].ToString();
newrow.cell[1] = row[1].ToString();
newrow.cell[2] = row[2].ToString();
newrow.cell[3] = row[3].ToString();
newrow.cell[4] = row[4].ToString();
newrow.cell[5] = row[5].ToString();
newrow.cell[6] = row[6].ToString();
newrow.cell[7] = row[7].ToString();
newrow.cell[8] = row[8].ToString();
newrow.cell[9] = row[9].ToString();
newrow.cell[10] = row[10].ToString();
newrow.cell[11] = row[11].ToString();
newrow.cell[12] = row[12].ToString();
newrow.cell[13] = row[13].ToString();
rowsadded.Add(newrow);
}
result.rows = rowsadded.ToArray();
result.page = page;
result.total = dt.Rows.Count;
result.record = rowsadded.Count;
return result;
}
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();
}
public static s_GridResult GetIncidentTable(string _search, string nd, int rows, int page, string sidx, string sord)
{
int startindex = (page - 1);
int endindex = page;
string sql = @"SELECT I.Priority,
I.ClosedDate,
I.InsertedBatchId,
I.Id as incidentId,
I.Title,
I.Description,
I.CreatedDate,
month(I.CreatedDate) as slahdeti_month,
day(I.CreatedDate) as slahdeti_day,
year(I.CreatedDate) as slahdeti_year,
I.FirstAssignedDate,
I.DisplayName as ticket_DisplayName,
I.IsDeleted,
itq.IncidentTierQueuesValue,
ISt.IncidentStatusValue,
IU.IncidentUrgencyValue,
II.IncidentImpactValue,
IC.IncidentClassificationValue,
ISo.IncidentSourceValue
FROM DWRepository.dbo.IncidentDim as I
INNER JOIN DWRepository.dbo.IncidentTierQueuesvw as ITQ on itq.ID = I.TierQueue
INNER JOIN DWRepository.dbo.IncidentStatusvw as ISt on ISt.ID = I.Status
INNER JOIN DWRepository.dbo.IncidentUrgencyvw as IU on IU.ID = I.Urgency
INNER JOIN DWRepository.dbo.IncidentImpactvw as II on II.ID = I.Impact
INNER JOIN DWRepository.dbo.IncidentClassificationvw as IC on IC.ID = I.Classification
INNER JOIN DWRepository.dbo.IncidentSourcevw as ISo on Iso.ID = I.Source
WHERE I.Id = '" + v + "';";
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["tkuscsm-dwsConnectionString"].ConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(dt);
s_GridResult result = new s_GridResult();
List<s_RowData> rowsadded = new List<s_RowData>();
int idx = 1;
foreach (DataRow row in dt.Rows)
{
s_RowData newrow = new s_RowData();
newrow.id = idx++;
newrow.cell = new string[19]; //total number of columns
newrow.cell[0] = row[0].ToString();
newrow.cell[1] = row[1].ToString();
newrow.cell[2] = row[2].ToString();
newrow.cell[3] = row[3].ToString();
newrow.cell[4] = row[4].ToString();
newrow.cell[5] = row[5].ToString();
newrow.cell[6] = row[6].ToString();
newrow.cell[7] = row[7].ToString();
newrow.cell[8] = row[8].ToString();
newrow.cell[9] = row[9].ToString();
newrow.cell[10] = row[10].ToString();
newrow.cell[11] = row[11].ToString();
newrow.cell[12] = row[12].ToString();
newrow.cell[13] = row[13].ToString();
newrow.cell[14] = row[14].ToString();
newrow.cell[15] = row[15].ToString();
newrow.cell[16] = row[16].ToString();
newrow.cell[17] = row[17].ToString();
newrow.cell[18] = row[18].ToString();
rowsadded.Add(newrow);
}
result.rows = rowsadded.ToArray();
result.page = page;
result.total = dt.Rows.Count;
result.record = rowsadded.Count;
return result;
}
//public static s_GridResult GetDataTable(string _search, string nd, int rows, int page, string sidx, string sord)
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);
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("RBS Resource List : " + 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 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
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,
COUNT(CASE WHEN ([TimeSheet Status] = 'Approved') THEN [TimeSheet Status] END) AS Approved
FROM [#t2]
--WHERE ([TimeSheet Status] <> 'Approved')
GROUP BY PeriodName
ORDER BY PeriodName
drop table #t2
";
s_GridResult result = new s_GridResult();
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> rowsadded = new List <s_RowData>();
int idx = 1;
try
{
foreach (DataRow row in maintbl.Rows)
{
s_RowData newrow = new s_RowData();
newrow.id = idx++;
//Tabel Column List -- ResourceName - 0,TimeSheet Period - 1,TimeSheet Period Status - 2
newrow.cell = new string[5]; //total number of columns
newrow.cell[0] = row[0].ToString(); //TimeSheet Period Name
newrow.cell[1] = row[1].ToString(); //In Progress
newrow.cell[2] = row[2].ToString(); //Not Created
newrow.cell[3] = row[3].ToString(); //Submitted
newrow.cell[4] = row[4].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());
}
