protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string en_id = Request.QueryString["en_ID"].ToString();
if (en_id != string.Empty)
{
HiddenField1.Value = en_id;
using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
{
SPWeb hrWeb = site.AllWebs["HRMV02"];
SPList entitylist = hrWeb.Lists["Entity"];
//SPQuery qry_edit_entity = new SPQuery();
//qry_edit_entity.Query = @"<Where><Eq><FieldRef Name='ID'/><Value Type='Counter'>"+en_id+@"</Value></Eq></Where>";
SPListItem edit_entitylist_item = entitylist.GetItemById(Convert.ToInt32(en_id.ToString()));
txtbox_edit_EntityName.Text = edit_entitylist_item["Title"].ToString();
rbtn_EntityStatus.Items.Clear();
SPFieldChoice rbt_entity_status = new SPFieldChoice(edit_entitylist_item.Fields, "Status");
for (int i = 0; i <= (Convert.ToInt16(rbt_entity_status.Choices.Count.ToString()) - 1); i++)
{
rbtn_EntityStatus.Items.Add(rbt_entity_status.Choices[i].ToString());
if (rbtn_EntityStatus.Items[i].Text == edit_entitylist_item["Status"].ToString())
{
rbtn_EntityStatus.Items[i].Selected = true;
//rbtn_EntityStatus.Items[i].Attributes["AutoPostBack"] = "false";
}
}
}
}
}
}
protected void btn_Cancel_Entity_update_Click(object sender, EventArgs e)
{
SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current));
SPWeb hrweb = site.AllWebs["HRMV02"];
Response.Redirect(hrweb.Url.ToString());
}
protected SPDataSource entityds()
{
using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
{
SPWeb hrWeb = site.AllWebs["HRMV02"];
SPDataSource entityds = new SPDataSource();
entityds.List = hrWeb.Lists["Entity"];
return(entityds);
}
}
protected void Page_Load(object sender, EventArgs e)
{
string siteurl = MyConfiguration.GetSiteURL(SPContext.Current);
var Resource_Svc = new Resource();
Resource_Svc.AllowAutoRedirect = true;
Resource_Svc.UseDefaultCredentials = true;
Resource_Svc.Url = siteurl + "/_vti_bin/psi/resource.asmx";
LblCurrentResUID.Text = Resource_Svc.GetCurrentUserUid().ToString();
//LblCurrentResUID.Text = "6FF0A657-63BC-4390-8AAF-7EE5CE033088";
}
protected void btn_Save_Entity_update_Click(object sender, EventArgs e)
{
string cusiteurl = string.Empty;
using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
{
SPWeb hrWeb = site.AllWebs["HRMV02"];
cusiteurl = hrWeb.Url.ToString();
hrWeb.AllowUnsafeUpdates = true;
SPList entitylist = hrWeb.Lists["Entity"];
SPListItem edit_entitylist_item = entitylist.GetItemById(Convert.ToInt32(HiddenField1.Value.ToString()));
edit_entitylist_item["Title"] = txtbox_edit_EntityName.Text;
edit_entitylist_item["Status"] = rbtn_EntityStatus.SelectedValue.ToString();
edit_entitylist_item.Update();
}
Response.Redirect(cusiteurl);
}
protected void Approve_Click(object sender, EventArgs e)
{
if (IsPostBack)
{
string siteurl = MyConfiguration.GetSiteURL(SPContext.Current);
var Resource_Svc = new Resource();
Resource_Svc.AllowAutoRedirect = true;
Resource_Svc.UseDefaultCredentials = true;
Resource_Svc.Url = siteurl + "/_vti_bin/psi/resource.asmx";
var Timesheet_Svc = new TimeSheet();
Timesheet_Svc.AllowAutoRedirect = true;
Timesheet_Svc.UseDefaultCredentials = true;
Timesheet_Svc.Url = siteurl + "/_vti_bin/psi/timesheet.asmx";
string[] selectedtsuids = Request.Form["LstSelectedtsuids"].Split('#');
if (selectedtsuids.Length > 0)
{
var grouped_tsuids = (from c in selectedtsuids
group c by c).ToList();
foreach (IGrouping <string, string> tsuid_str in grouped_tsuids)
{
if (tsuid_str.Key != string.Empty)
{
try
{
var tsuid = new Guid(tsuid_str.Key);
var jobUID = Guid.NewGuid();
var nextapprover = Resource_Svc.GetCurrentUserUid();
Timesheet_Svc.QueueReviewTimesheet(jobUID, tsuid, nextapprover,
"Approving using utility",
ITXProjectsLibrary.WebSvcTimeSheet.Action.Approve);
}
catch (Exception ex)
{
MyConfiguration.ErrorLog("Error at approving timesheet due to " + ex.Message,
EventLogEntryType.Error);
}
}
}
}
LblStatus.Text = "Timesheet approval process completed successfully.";
}
}
protected SPDataSource resdetailds(int resfilterid)
{
using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
{
SPWeb hrWeb = site.AllWebs["HRMV02"];
SPDataSource resdetds = new SPDataSource();
resdetds.SelectCommand = @"<Query>
<Where>
<Eq>
<FieldRef Name='Department' LookupId='True' />
<Value Type='Lookup'>" + resfilterid + @"</Value>
</Eq>
</Where>
</Query>";
resdetds.List = hrWeb.Lists["Resource Details"];
return(resdetds);
}
}
protected SPDataSource deptds(int deptid)
{
using (SPSite site = new SPSite(MyConfiguration.GetSiteURL(SPContext.Current)))
{
SPWeb hrWeb = site.AllWebs["HRMV02"];
SPDataSource deptds = new SPDataSource();
deptds.SelectCommand = @"<Query>
<Where>
<Eq>
<FieldRef Name='Entity' LookupId='True'/>
<Value Type='Lookup'>" + deptid + @"</Value>
</Eq>
</Where>
</Query>";
deptds.List = hrWeb.Lists["Department"];
return(deptds);
}
}
protected void exportcsv_Click(object sender, EventArgs e)
{
string _stdate = _hiddenstdate.Value.ToString();
string _enddate = _hiddenenddate.Value.ToString();
DateTime _enddateformatted = Convert.ToDateTime(_enddate);
string siteurl = HttpContext.Current.Request.UrlReferrer.ToString();
var url = new Uri(siteurl);
string rbsurl = url.Scheme + "://" + url.Host + ":" + url.Port + url.Segments[0] + url.Segments[1];
Guid adminguid;
var resource_svc = new Resource();
adminguid = new Guid("6FF0A657-63BC-4390-8AAF-7EE5CE033088");
resource_svc.Url = "http://jump/cimb/_vti_bin/psi/resource.asmx";
if (url.Host.ToString() != "localhost")
{
//var rbs = new ReadRBSValues(System.Net.CredentialCache.DefaultNetworkCredentials, "http://jump/cimb");
resource_svc.Url = rbsurl + "/_vti_bin/psi/resource.asmx";
}
//var rbs = new ReadRBSValues(System.Net.CredentialCache.DefaultNetworkCredentials, rbsurl);
//var resuids = rbs.GetBottomLevelResouceUIDs();
resource_svc.UseDefaultCredentials = true;
resource_svc.AllowAutoRedirect = true;
Guid currentuserid = resource_svc.GetCurrentUserUid();
string res_under_curr_user = @"
SELECT ResourceUID
FROM dbo.MSP_EpmResource_UserView
WHERE (RBS Like ( (
SELECT RBS
FROM dbo.MSP_EpmResource_UserView
WHERE ResourceUID = '" + currentuserid.ToString() + @"'
)
+'.%')
) AND ResourceIsActive = 1
";
WindowsImpersonationContext wik = null;
wik = WindowsIdentity.Impersonate(IntPtr.Zero);
SqlConnection con = new SqlConnection(MyConfiguration.GetDataBaseConnectionString(siteurl));
con.Open();
DataSet filterresourcelist = new DataSet();
SqlDataAdapter filterresourceadapter = new SqlDataAdapter(res_under_curr_user, con);
filterresourceadapter.Fill(filterresourcelist);
string filterresource = "(";
foreach (DataRow row in filterresourcelist.Tables[0].Rows)
{
filterresource += "'" + row[0].ToString() + "',";
}
/* foreach (var resuid in resuids)
* {
* filterresource += "'" + resuid.ToString() + "',";
* }
*/
filterresource = filterresource.Substring(0, filterresource.Length - 1) + ")";
string gridqry = @"
SELECT res.ResourceUID, res.ResourceName, res.RBS, tperiod.PeriodUID, tperiod.PeriodStatusID, tperiod.StartDate, tperiod.EndDate, tperiod.PeriodName,
tperiod.LCID, ISNULL(TM_Name.ResourceName, 'Not Assigned') AS TM_Name
INTO [#t1]
FROM MSP_EpmResource_UserView AS TM_Name RIGHT OUTER JOIN
MSP_EpmResource_UserView AS res ON TM_Name.ResourceUID = res.ResourceTimesheetManagerUID CROSS JOIN
MSP_TimesheetPeriod AS tperiod
WHERE (tperiod.StartDate BETWEEN (
(SELECT CASE WHEN (TimeDayOfTheWeek = 2) THEN '" + _stdate + @"' WHEN (TimeDayOfTheWeek = 1) THEN DATEADD(d,1, '" + _stdate + @"' )
ELSE DATEADD(d,(2-TimeDayofTheWeek), '" + _stdate + @"' ) END AS stdate
FROM MSP_TimeByDay
WHERE (TimeByDay = CONVERT(DATETIME, '" + _stdate + @"' , 102)))
)
AND '" + _enddate + @"' ) AND (res.ResourceUID IN " + filterresource + @")
SELECT [#t1].PeriodUID, [#t1].ResourceUID,[#t1].TM_Name, [#t1].RBS, [#t1].ResourceName, [#t1].PeriodName,
ISNULl(tstatus.Description,'Not Created') AS [TimeSheet Status], [#t1].StartDate, [#t1].EndDate
INTO #t2
FROM MSP_TimesheetStatus AS tstatus INNER JOIN
MSP_Timesheet AS tsheet ON tstatus.TimesheetStatusID = tsheet.TimesheetStatusID INNER JOIN
MSP_TimesheetResource AS tres ON tsheet.OwnerResourceNameUID = tres.ResourceNameUID RIGHT OUTER JOIN
[#t1] ON [#t1].ResourceUID = tres.ResourceUID AND [#t1].PeriodUID = tsheet.PeriodUID
drop table #t1
SELECT PeriodName, TM_Name, ResourceName, COUNT(CASE WHEN ([TimeSheet Status] = 'In Progress') THEN [TimeSheet Status] END)
AS [In Progress], COUNT(CASE WHEN ([TimeSheet Status] = 'Not Created') THEN [TimeSheet Status] END) AS [Not Created],
COUNT(CASE WHEN ([TimeSheet Status] = 'Submitted') THEN [TimeSheet Status] END) AS Submitted,
COUNT(CASE WHEN ([TimeSheet Status] = 'Approved') THEN [TimeSheet Status] END) AS Approved
FROM [#t2]
--WHERE ([TimeSheet Status] <> 'Approved')
GROUP BY PeriodName, TM_Name, ResourceName
ORDER BY PeriodName, TM_Name, ResourceName
/*
SELECT PeriodName, COUNT(CASE WHEN ([TimeSheet Status] = 'In Progress') THEN [TimeSheet Status] END)
AS [In Progress], COUNT(CASE WHEN ([TimeSheet Status] = 'Not Created') THEN [TimeSheet Status] END) AS [Not Created],
COUNT(CASE WHEN ([TimeSheet Status] = 'Submitted') THEN [TimeSheet Status] END) AS Submitted
FROM [#t2]
WHERE ([TimeSheet Status] <> 'Approved')
GROUP BY PeriodName
ORDER BY PeriodName
*/
drop table #t2
";
SPSecurity.RunWithElevatedPrivileges(delegate()
{
DataSet dt = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(gridqry, con));
adapter.Fill(dt);
#region export function
try
{
// You should be very carefull with sharepoint layout folder, we have to specify full control permission to everybody.
string FolderPath = Server.MapPath("/_layouts/CIMB_TimeSheet/CSVFiles/");
if (Directory.Exists(FolderPath))
{
foreach (string file in Directory.GetFiles(FolderPath))
{
try
{
File.Delete(file);
}
catch (Exception ex)
{
}
}
}
else
{
Directory.CreateDirectory(FolderPath);
}
string clientfilename = DateTime.Now.ToString("ddMMyyhhmmss") + DateTime.Now.Millisecond.ToString();
string filename = FolderPath + clientfilename + ".csv";
var writer = new StreamWriter(filename);
//Writing column name at first row
string columnheaderline = string.Empty;
for (int i = 0; i < dt.Tables[0].Columns.Count; i++)
{
columnheaderline = columnheaderline + dt.Tables[0].Columns[i].ColumnName + ",";
}
writer.WriteLine(columnheaderline);
// Writing row values
foreach (DataRow row in dt.Tables[0].Rows)
{
string columnvalue = string.Empty;
for (int i = 0; i < dt.Tables[0].Columns.Count; i++)
{
columnvalue = columnvalue + row[i] + ",";
}
writer.WriteLine(columnvalue);
}
try
{
writer.Flush();
writer.Close();
writer.Dispose();
}
catch (Exception ex) { MyConfiguration.ErrorLog("Error in writing CSV : " + ex.Message, EventLogEntryType.Error); }
// Sending files here
Response.ContentType = "application/CSV";
Response.AddHeader("content-disposition", "attachment; filename=TimeSheetNonCompliance" + clientfilename + ".csv");
Response.TransmitFile(filename);
Response.Flush();
Response.End();
}
catch (Exception)
{
}
#endregion export function
});
}
protected void exportcsv_Click(object sender, EventArgs e)
{
string _stdate = _hiddenstdate.Value;
string _enddate = _hiddenenddate.Value;
DateTime _enddateformatted = Convert.ToDateTime(_enddate);
string siteurl = HttpContext.Current.Request.UrlReferrer.ToString();
#region sqlqry
string gridqry = @"
SELECT t_res.ResourceUID AS ResUID, t_proj.ProjectUID AS ProjUID, t_res.ResourceName AS ResName,
CASE WHEN t_class.Type <> 0 THEN t_class.ClassNAME ELSE t_proj.ProjectName END AS ProjName, t_act.TimeByDay AS t_date,
t_act.TimeByDay_DayOfWeek AS t_wkday, t_class.Type AS t_type, CASE WHEN ((t_act.TimeByDay_DayOfWeek = 1 OR
t_act.TimeByDay_DayOfWeek = 7) AND (t_class.Type = 1)) THEN '0' WHEN (t_class.Type = 1 AND
(t_act.ActualWorkBillable + t_act.ActualWorkNonBillable + t_act.ActualOvertimeWorkBillable + t_act.ActualOvertimeWorkNonBillable <= 4))
THEN '4' WHEN (t_class.Type = 1 AND
(t_act.ActualWorkBillable + t_act.ActualWorkNonBillable + t_act.ActualOvertimeWorkBillable + t_act.ActualOvertimeWorkNonBillable > 4))
THEN '8' ELSE t_act.ActualWorkBillable + t_act.ActualWorkNonBillable + t_act.ActualOvertimeWorkBillable + t_act.ActualOvertimeWorkNonBillable END
AS totaltime
INTO [#t1]
FROM MSP_TimesheetActual AS t_act INNER JOIN
MSP_TimesheetResource AS t_res ON t_act.LastChangedResourceNameUID = t_res.ResourceNameUID INNER JOIN
MSP_TimesheetLine AS t_line ON t_act.TimesheetLineUID = t_line.TimesheetLineUID INNER JOIN
MSP_TimesheetClass AS t_class ON t_line.ClassUID = t_class.ClassUID INNER JOIN
MSP_TimesheetProject AS t_proj ON t_line.ProjectNameUID = t_proj.ProjectNameUID
WHERE (t_act.TimeByDay BETWEEN '" + _stdate + @"' AND '" + _enddate + @"')
ORDER BY ResUID, t_date, t_type
SELECT #t1.ResUID, #t1.ProjUID, #t1.ResName, #t1.ProjName, #t1.t_date, #t1.t_wkday, #t1.t_type, #t1.totaltime,
ISNULL(dummyt1_nkwking.nonwktotal,0) AS nwktotal, ISNULL(dummyt1.daytotal,0) AS daytotal,
CASE WHEN #t1.t_type <> 1 AND ((dummyt1_nkwking.nonwktotal = 4 AND dummyt1.daytotal >4)or (ISNULL(dummyt1_nkwking.nonwktotal,0) =8) or
(ISNULL(dummyt1_nkwking.nonwktotal,0) = 0 AND dummyt1.daytotal > 8))
THEN (8-ISNULL(dummyt1_nkwking.nonwktotal,0))*#t1.totaltime/(CASE WHEN dummyt1.daytotal=0 THEN 1.0 ELSE ISNULL(dummyt1.daytotal,1) END)
ELSE #t1.totaltime END AS normalizedtime into #t2
FROM #t1 LEFT OUTER JOIN(
SELECT dummyt1_nwking.ResUID, dummyt1_nwking.t_date, SUM(dummyt1_nwking.totaltime) AS nonwktotal
FROM [#t1] AS dummyt1_nwking
WHERE (dummyt1_nwking.t_type = 1)
GROUP BY dummyt1_nwking.ResUID, dummyt1_nwking.t_date
--ORDER BY dummyt1_nwking.ResUID, dummyt1_nwking.t_date
) AS dummyt1_nkwking ON #t1.ResUID = dummyt1_nkwking.ResUID AND #t1.t_date = dummyt1_nkwking.t_date LEFT OUTER JOIN
(
SELECT dummyt1.ResUID, dummyt1.t_date, SUM(dummyt1.totaltime) AS daytotal
FROM [#t1] AS dummyt1
WHERE (dummyt1.t_type <> 1)
GROUP BY dummyt1.ResUID, dummyt1.t_date
--ORDER BY dummyt1.ResUID, dummyt1.t_date
) AS dummyt1 ON #t1.ResUID = dummyt1.ResUID AND #t1.t_date = dummyt1.t_date
ORDER BY #t1.ResUID, #t1.t_date, #t1.t_type
drop table #t1
SELECT distinct TimeByDay AS missing_date,TimeDayOfTheWeek AS missing_wkday, t_Resources.ResUID AS missing_ResUID, t_Resources.ResName INTO #t3
FROM MSP_TimeByDay CROSS JOIN (SELECT distinct [#t2].ResUID, #t2.ResName FROM [#t2]) AS t_Resources
WHERE (TimeByDay BETWEEN '" + _stdate + @"' AND '" + _enddate + @"') AND
(NOT EXISTS
(SELECT *
FROM [#t2] WHERE MSP_TimeByDay.TimeByDay = [#t2].t_date AND t_Resources.ResUID = [#t2].ResUID))
INSERT INTO #t2
SELECT #t3.missing_ResUID, 'E38038FA-F8CA-47D1-BFD4-6B45B8462972',#t3.ResName,'NotClocked',#t3.missing_date, #t3.missing_wkday, 1, 8, 8,0,8
FROM #t3 WHERE #t3.missing_wkday <> '1' AND #t3.missing_wkday <> '7'
drop table #t3
SELECT #t2.ResUID, SUM(#t2.normalizedtime) AS total_paid_leave INTO #t4
FROM #t2
WHERE #t2.t_type = 1 AND #t2.ProjName = 'Paid Leave-Public Holiday'
GROUP By #t2.ResUID
SELECT #t2.ResUID,#t2.ProjUID,
SUM(
(CONVERT(NUMERIC(18,14),#t2.normalizedtime) /(CASE WHEN nortotal.total_Proj = 0 THEN 1.0 ELSE CONVERT(NUMERIC(18,14),nortotal.total_Proj) END)
)*CONVERT(NUMERIC(18,14),ISNULL(#t4.total_paid_leave,0)) + CONVERT(NUMERIC(18,14),#t2.normalizedtime)
) AS normalized_proj_time
INTO #t5
FROM #t2 INNER JOIN
(select #t2.ResUID, SUM(#t2.normalizedtime) AS total_Proj FROM #t2 WHERE t_type = 0 GROUP BY #t2.ResUID) AS nortotal ON #t2.ResUID = nortotal.ResUID
LEFT OUTER JOIN #t4 ON #t2.ResUID = #t4.ResUID
WHERE #t2.t_type = 0
GROUP BY #t2.ResUID, #t2.ProjUID, #t2.ProjName, #t2.t_type
--select * from #t2
drop table #t4
SELECT #t2.ResUID, #t2.ProjUID, #t2.ProjName, #t2.t_type, SUM (convert(numeric(18,14),#t2.normalizedtime)) AS nor_time
INTO #t6
FROM #t2
GROUP By #t2.ResUID, #t2.ProjUID, #t2.ProjName,#t2.t_type
drop table #t2
select #t6.ResUID, #t6.ProjUID, #t6.ProjName,
CASE WHEN #t6.t_type = 0 THEN #t5.normalized_proj_time ELSE #t6.nor_time END AS total_time,
CASE WHEN #t6.t_type = 0 THEN (convert (float,#t5.normalized_proj_time)/res_total.res_total_time)*100
ELSE (convert(float,#t6.nor_time) /res_total.res_total_time)*100 END
AS pct_total_time, #t6.t_type INTO #t7
from #t6 LEFT OUTER JOIN #t5 on #t6.ResUID = #t5.ResUID and #t6.ProjUID = #t5.ProjUID LEFT OUTER JOIN
(select #t6.ResUID, SUM (#t6.nor_time) AS res_total_time FROM #t6 GROUP BY #t6.ResUID) AS res_total ON #t6.ResUID = res_total.ResUID
where #t6.ProjName <> 'Paid Leave-Public Holiday'
order by #t6.ResUID
drop table #t5
drop table #t6
select #t7.ResUID, #t7.ProjUID, #t7.ProjName, #t7.total_time, #t7.pct_total_time,uv_res.ResourceName,
ISNULL (uv_proj.Project_RC_Code, #t7.ProjName) AS costcode, ISNULL(uv_res.ResourceEmailAddress,'NoEmail') AS res_email, ISNULL(uv_res.RBS, 'NoRBS') AS RBS
FROM #t7 left outer join dbo.MSP_EpmResource_UserView as uv_res on #t7.ResUID = uv_res.ResourceUID
left outer join dbo.MSP_EpmProject_UserView as uv_proj on #t7.ProjUID = uv_proj.ProjectUID
order by #t7.resuid, #t7.t_type
drop table #t7
";
string rc_code_qry = @"
SELECT MemberFullValue
FROM MSPLT_Project_RC_Code_UserView
WHERE (ParentLookupMemberUID IS NOT NULL)
ORDER BY CAST(MemberFullValue AS varchar(500))
";
#endregion sqlqry
WindowsImpersonationContext wik = null;
wik = WindowsIdentity.Impersonate(IntPtr.Zero);
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SqlConnection con = new SqlConnection(MyConfiguration.GetDataBaseConnectionString(siteurl));
con.Open();
DataSet dt = new DataSet();
DataSet rc_code_dt = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(gridqry, con));
adapter.Fill(dt);
DataTable maintbl = dt.Tables[0];
adapter = new SqlDataAdapter(new SqlCommand(rc_code_qry, con));
adapter.Fill(rc_code_dt);
DataTable rc_code_table = rc_code_dt.Tables[0];
MyConfiguration.ErrorLog("Main Table Row count = " + dt.Tables[0].Rows.Count, EventLogEntryType.SuccessAudit);
maintbl.Columns.Add("HRISID");
maintbl.Columns.Add("OLDEMPID");
maintbl.Columns.Add("PayRolStDate");
maintbl.Columns.Add("PayRolEntity");
maintbl.Columns.Add("staftype");
string pctcolumntype = maintbl.Columns["pct_total_time"].DataType.ToString();
DataView view = new DataView(dt.Tables[0]);
DataTable resourcelist = view.ToTable(true, "ResourceName", "res_email");
string resname = string.Empty;
string resemail = string.Empty;
try
{
using (SPSite site = new SPSite(MyConfiguration.frm_siteurl_GetSiteURL(siteurl)))
{
foreach (DataRow row in resourcelist.Rows)
{
SPWeb hrWeb = site.AllWebs["HRMV02"];
SPList res_detail = hrWeb.Lists["Resource Details"];
SPList seconded = hrWeb.Lists["Resource Secondment"];
SPQuery qry = new SPQuery();
qry.Query = @"<Where>
<Eq>
<FieldRef Name='EMail'/>
<Value Type='Text'>" + row[1].ToString() + @"</Value>
</Eq>
</Where>";
SPListItemCollection items = res_detail.GetItems(qry);
#region secondment and resource data from HR Manipulation
if (items.Count > 0)
{
foreach (SPListItem itm in items)
{
#region secondment query
SPQuery secondedqry = new SPQuery();
secondedqry.Query = @"
<ViewFields>
<FieldRef Name='Department' />
<FieldRef Name='From_x0020_Date' />
<FieldRef Name='To_x0020_Date' />
</ViewFields>
<Where>
<And>
<Eq>
<FieldRef Name='Resource_x0020_Name' />
<Value Type='Lookup'>" + itm["Resource Name"].ToString() + @"</Value>
</Eq>
<Leq>
<FieldRef Name='From_x0020_Date' />
<Value Type='DateTime'>" + string.Format("{0:yyyy-MM-dd}", _enddateformatted) + @"</Value>
</Leq>
</And>
</Where>
<OrderBy>
<FieldRef Name='ID' Ascending='False' />
</OrderBy>
<RowLimit>1</RowLimit>
";
#endregion secondment query
//MyConfiguration.ErrorLog("List Resource Name : " + itm["Resource Name"].ToString(), EventLogEntryType.SuccessAudit);
//MyConfiguration.ErrorLog("List Department Name : " + itm["Department"].ToString(), EventLogEntryType.SuccessAudit);
foreach (DataRow ro in maintbl.Select("res_email = '" + row[1].ToString() + "'"))
{
ro["ResourceName"] = itm["Resource Name"].ToString();
if ((itm.Fields.ContainsField("HRIS_x0020_ID")) && (itm["HRIS_x0020_ID"] != null))
{
ro["HRISID"] = itm["HRIS_x0020_ID"].ToString();
}
else
{
ro["HRISID"] = "-";
}
if ((itm.Fields.ContainsField("Old_x0020_Emp_x0020_Num")) && (itm["Old_x0020_Emp_x0020_Num"] != null))
{
ro["OLDEMPID"] = itm["Old_x0020_Emp_x0020_Num"].ToString();
}
else
{
ro["OLDEMPID"] = "-";
}
if ((itm.Fields.ContainsField("Date_x0020_On_x0020_Board")) && (itm["Date_x0020_On_x0020_Board"] != null))
{
ro["PayRolStDate"] = itm["Date_x0020_On_x0020_Board"].ToString();
}
else
{
ro["PayRolStDate"] = "01-01-1900";
}
if ((itm.Fields.ContainsField("Department")) && (itm["Department"] != null))
{
string[] deptname = itm["Department"].ToString().Split(new char[] { ';', '#' }, StringSplitOptions.RemoveEmptyEntries);
SPList deptlist = hrWeb.Lists["Department"];
SPListItem deptitem = deptlist.GetItemById(Convert.ToInt32(deptname[0].ToString()));
string[] entityname = deptitem["Entity"].ToString().Split(new char[] { ';', '#' }, StringSplitOptions.RemoveEmptyEntries);
ro["PayRolEntity"] = entityname[1].ToString();
if (ro["ProjName"].ToString() != string.Empty && ro["ProjName"].ToString() == "Orignal Department")
{
ro["ProjName"] = deptname[1].ToString();
if ((deptitem.Fields.ContainsField("Cost_x0020_Code")) && (deptitem["Cost_x0020_Code"] != null))
{
ro["costcode"] = deptitem["Cost_x0020_Code"].ToString();
}
else
{
ro["costcode"] = deptname[1].ToString();
}
}
}
else
{
ro["PayRolEntity"] = "-";
}
if ((itm.Fields.ContainsField("Resource_x0020_Type")) && (itm["Resource_x0020_Type"] != null))
{
ro["staftype"] = itm["Resource_x0020_Type"].ToString();
}
else
{
ro["staftype"] = "-";
}
SPListItemCollection secondeditems = seconded.GetItems(secondedqry);
if (secondeditems.Count > 0)
{
foreach (SPListItem secondeditem in secondeditems)
{
if ((secondeditem.Fields.ContainsField("Department")) && (secondeditem["Department"] != null))
{
string[] deptname = secondeditem["Department"].ToString().Split(new char[] { ';', '#' }, StringSplitOptions.RemoveEmptyEntries);
SPList deptlist = hrWeb.Lists["Department"];
SPListItem deptitem = deptlist.GetItemById(Convert.ToInt32(deptname[0].ToString()));
string[] entityname = deptitem["Entity"].ToString().Split(new char[] { ';', '#' }, StringSplitOptions.RemoveEmptyEntries);
ro["PayRolEntity"] = entityname[1].ToString();
if (ro["ProjName"].ToString() != string.Empty && ro["ProjName"].ToString() == "TO BAU")
{
ro["ProjName"] = deptname[1].ToString();
if ((deptitem.Fields.ContainsField("Cost_x0020_Code")) && (deptitem["Cost_x0020_Code"] != null))
{
ro["costcode"] = deptitem["Cost_x0020_Code"].ToString();
}
else
{
ro["costcode"] = deptname[1].ToString();
}
}
//MyConfiguration.ErrorLog("Resource Name: " + ro["ResourceName"].ToString(), EventLogEntryType.SuccessAudit);
//MyConfiguration.ErrorLog("Deptment: " + ro["ProjName"].ToString(), EventLogEntryType.SuccessAudit);
//MyConfiguration.ErrorLog("Code: " + ro["costcode"].ToString(), EventLogEntryType.SuccessAudit);
}
}
}
}
}
}
else
{
foreach (DataRow ro in maintbl.Select("res_email = '" + row[1].ToString() + "'"))
{
ro["HRISID"] = "NA in HRM";
ro["OLDEMPID"] = "NA in HRM";
ro["PayRolStDate"] = "01-01-1900";
ro["PayRolEntity"] = "NA in HRM";
ro["staftype"] = "Internal";
}
}
#endregion secondment and resource data from HR Manipulation
}
}
}
catch (Exception ex)
{
MyConfiguration.ErrorLog("Error in accessing SPSite" + ex.Message, EventLogEntryType.Error);
}
maintbl.DefaultView.Sort = "staftype DESC";
DataView pivotview = new DataView(maintbl);
DataTable pivotviewtable = pivotview.ToTable(false, "ResUID", "costcode", "pct_total_time");
var qyery = (from c in maintbl.AsEnumerable()
group c by new { cost_code = c.Field <string>("costcode"), res_uid = c.Field <Guid>("ResUID") } into grp
orderby grp.Key.res_uid, grp.Key.cost_code
select new
{
ResUID = grp.Key.res_uid,
costcode = grp.Key.cost_code,
pct_total_time = grp.Sum(r => r.Field <double>("pct_total_time"))
});
DataTable pvtable = qyery.CopyToDataTable();
DataTable resulttbl = new DataTable();
try
{
resulttbl = Pivot(pvtable, "ResUID", "costcode", "pct_total_time");
}
catch (Exception ex) { MyConfiguration.ErrorLog("Error in Pivot data: " + ex.Message, EventLogEntryType.Error); }
try
{
resulttbl.Columns.Add("RBS");
resulttbl.Columns.Add("ResourceName");
resulttbl.Columns.Add("HRISID");
resulttbl.Columns.Add("OLDEMPID");
resulttbl.Columns.Add("PayRolStDate");
resulttbl.Columns.Add("PayRolEntity");
resulttbl.Columns.Add("staftype");
resulttbl.Columns["RBS"].SetOrdinal(1);
resulttbl.Columns["ResourceName"].SetOrdinal(2);
resulttbl.Columns["HRISID"].SetOrdinal(3);
resulttbl.Columns["OLDEMPID"].SetOrdinal(4);
resulttbl.Columns["PayRolStDate"].SetOrdinal(5);
resulttbl.Columns["PayRolEntity"].SetOrdinal(6);
resulttbl.Columns["staftype"].SetOrdinal(7);
}
catch (Exception ex) { MyConfiguration.ErrorLog("Error Adding Field: " + ex.Message, EventLogEntryType.Error); }
try
{
if (rc_code_table.Rows.Count > 0)
{
int columnindex = 8;
foreach (DataRow ro in rc_code_table.Rows)
{
bool found = false;
foreach (DataColumn resultcolumn in resulttbl.Columns)
{
if (ro[0].ToString() == resultcolumn.ColumnName)
{
found = true;
}
}
if (found == false)
{
resulttbl.Columns.Add(ro[0].ToString());
}
resulttbl.Columns[ro[0].ToString()].SetOrdinal(columnindex++);
}
}
}
catch (Exception ex) { MyConfiguration.ErrorLog("Error in adding column:" + ex.Message, EventLogEntryType.Error); }
DataView resdetailview = new DataView(maintbl);
DataTable resdetailtable = resdetailview.ToTable(true, "RBS", "ResUID", "ResourceName", "HRISID", "OLDEMPID", "PayRolStDate", "PayRolEntity", "staftype");
try
{
foreach (DataRow ro in resulttbl.Rows)
{
foreach (DataRow mtlrow in resdetailtable.Select("ResUID = '" + ro["ResUID"] + "'"))
{
ro["RBS"] = mtlrow["RBS"];
ro["ResourceName"] = mtlrow["ResourceName"];
ro["HRISID"] = mtlrow["HRISID"].ToString();
ro["OLDEMPID"] = mtlrow["OLDEMPID"];
if (mtlrow["PayRolStDate"].ToString() != "00-00-000")
{
ro["PayRolStDate"] = Convert.ToDateTime(mtlrow["PayRolStDate"]).ToString("dd-MM-yyyy");
}
else
{
ro["PayRolStDate"] = mtlrow["PayRolStDate"];
}
ro["PayRolEntity"] = mtlrow["PayRolEntity"];
ro["staftype"] = mtlrow["staftype"];
}
}
}
catch (Exception ex) { MyConfiguration.ErrorLog("Error in Adding Resource Details:" + ex.Message, EventLogEntryType.Error); }
#region export function
WindowsImpersonationContext wic = null;
try
{
try
{
wic = WindowsIdentity.Impersonate(IntPtr.Zero);
}
catch (Exception)
{
}
// You should be very carefull with sharepoint layout folder, we have to specify full control permission to everybody.
string FolderPath = Server.MapPath("/_layouts/CIMB_TimeSheet/CSVFiles/");
if (Directory.Exists(FolderPath))
{
foreach (string file in Directory.GetFiles(FolderPath))
{
try
{
File.Delete(file);
}
catch (Exception ex)
{
}
}
}
else
{
Directory.CreateDirectory(FolderPath);
}
string clientfilename = DateTime.Now.ToString("ddMMyyhhmmss") + DateTime.Now.Millisecond.ToString();
string filename = FolderPath + clientfilename + ".csv";
var writer = new StreamWriter(filename);
//Writing column name at first row
string columnheaderline = string.Empty;
for (int i = 1; i < resulttbl.Columns.Count; i++)
{
columnheaderline = columnheaderline + resulttbl.Columns[i].ColumnName + ",";
}
writer.WriteLine(columnheaderline);
// Writing row values
foreach (DataRow row in resulttbl.Rows)
{
string columnvalue = string.Empty;
for (int i = 1; i < resulttbl.Columns.Count; i++)
{
if (i > 7)
{
if (row[i].ToString() != string.Empty)
{
columnvalue = columnvalue + row[i] + "%,";
}
else
{
columnvalue = columnvalue + "0%,";
}
}
else
{
columnvalue = columnvalue + row[i] + ",";
}
}
writer.WriteLine(columnvalue);
}
try
{
writer.Flush();
writer.Close();
writer.Dispose();
}
catch (Exception ex) { MyConfiguration.ErrorLog("Error in writing CSV : " + ex.Message, EventLogEntryType.Error); }
// Sending files here
Response.ContentType = "application/CSV";
Response.AddHeader("content-disposition", "attachment; filename=Res_Captilisation" + clientfilename + ".csv");
Response.TransmitFile(filename);
Response.Flush();
Response.End();
}
catch (Exception)
{
}
#endregion export function
});
}