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