public void execute(SPSite site, string data) { WebAppId = site.WebApplication.Id; using (SqlConnection cn = CreateConnection()) { try { cn.Open(); bool liveHours = false; bool.TryParse(EPMLiveCore.CoreFunctions.getConfigSetting(site.RootWeb, "EPMLiveTSLiveHours"), out liveHours); //string[] tsuids = data.Split(','); //foreach(string tsuidData in tsuids) { //if (actualWork != "") //{ if (!liveHours) { sErrors = SharedFunctions.processActualWork(cn, TSUID.ToString(), site, true, true); } //} } if (sErrors != "") { bErrors = true; } } catch (Exception ex) { throw ex; } finally { if (site != null) { site.Dispose(); } data = null; } } }
protected void Page_Load(object sender, EventArgs e) { string strAction = Request["action"]; string period = Request["period"]; Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.Expires = -1; data = ""; string fEmail = SPContext.Current.Web.CurrentUser.Email; SPSite site = SPContext.Current.Site; //using () { //using () SPWeb web = SPContext.Current.Web; { try { SqlConnection cn = null; SPSecurity.RunWithElevatedPrivileges(delegate() { cn = new SqlConnection(EPMLiveCore.CoreFunctions.getConnectionString(site.WebApplication.Id)); cn.Open(); string requestedUser = Page.Request["duser"]; if (requestedUser != null && requestedUser != "") { if (SharedFunctions.canUserImpersonate(username, requestedUser, SPContext.Current.Site.RootWeb, out resName)) { username = requestedUser; } else { impFailed = true; } } bool.TryParse(EPMLiveCore.CoreFunctions.getConfigSetting(SPContext.Current.Site.RootWeb, "EPMLiveTSLiveHours"), out liveHours); }); if (impFailed) { data = "Error: Impersonation Failed"; } else { if (cn != null) { SqlCommand cmd; int iperiod; SqlDataReader dr; switch (strAction) { case "deleteTS": if (web.CurrentUser.IsSiteAdmin) { string[] tsuids = Request["ts_uids"].Split(','); foreach (string tsuidData in tsuids) { cmd = new SqlCommand("DELETE FROM TSTIMESHEET where ts_uid=@ts_uid", cn); cmd.Parameters.AddWithValue("@ts_uid", tsuidData); cmd.ExecuteNonQuery(); } data = "Success"; } else { data = "Error: Access Denied"; } break; case "closePeriod": cmd = new SqlCommand("update tsperiod set locked=1 where period_id=@periodid and site_id=@siteid", cn); cmd.Parameters.AddWithValue("@siteid", SPContext.Current.Site.ID); cmd.Parameters.AddWithValue("@periodid", period); cmd.ExecuteNonQuery(); data = period; break; case "openPeriod": cmd = new SqlCommand("update tsperiod set locked=0 where period_id=@periodid and site_id=@siteid", cn); cmd.Parameters.AddWithValue("@siteid", SPContext.Current.Site.ID); cmd.Parameters.AddWithValue("@periodid", period); cmd.ExecuteNonQuery(); data = period; break; case "submitTime": cmd = new SqlCommand("update TSTIMESHEET set submitted=1,approval_status=0,lastmodifiedbyu=@u,lastmodifiedbyn=@n where ts_uid=@ts_uid", cn); cmd.Parameters.AddWithValue("@ts_uid", Request["ts_uid"]); cmd.Parameters.AddWithValue("@u", SPContext.Current.Web.CurrentUser.LoginName); cmd.Parameters.AddWithValue("@n", SPContext.Current.Web.CurrentUser.Name); cmd.ExecuteNonQuery(); SPSecurity.RunWithElevatedPrivileges(delegate() { SPWeb tweb = SPContext.Current.Web; { SharedFunctions.processResources(cn, Request["ts_uid"], tweb, username); } }); if (EPMLiveCore.CoreFunctions.getConfigSetting(SPContext.Current.Site.RootWeb, "EPMLiveTSDisableApprovals").ToLower() == "true") { approve(Request["ts_uid"], SPContext.Current.Web, Request["Period"]); } else { string actualWork = ""; //SPSecurity.RunWithElevatedPrivileges(delegate() //{ // actualWork = EPMLiveCore.CoreFunctions.getConfigSetting(SPContext.Current.Site.RootWeb, "EPMLiveTSActualWork"); //}); //if (actualWork != "") //{ if (!liveHours) { data = SharedFunctions.processActualWork(cn, Request["ts_uid"], site, false, true); } //} } if (data == "") { data = "Success"; } cmd = new SqlCommand("select ts_item_uid,web_uid,list_uid,item_id,project from TSITEM where TS_UID=@ts_uid", cn); cmd.Parameters.AddWithValue("@ts_uid", Request["ts_uid"]); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); SPList pList = null; SPWeb iWeb = null; SPList iList = null; Guid webGuid = Guid.Empty; Guid listGuid = Guid.Empty; foreach (DataRow dataRow in ds.Tables[0].Rows) { try { Guid wGuid = new Guid(dataRow["WEB_UID"].ToString()); Guid lGuid = new Guid(dataRow["LIST_UID"].ToString()); if (webGuid != wGuid) { if (iWeb != null) { iWeb.Close(); iWeb = site.OpenWeb(wGuid); } else { iWeb = site.OpenWeb(wGuid); } webGuid = iWeb.ID; } if (listGuid != lGuid) { iList = iWeb.Lists[lGuid]; try { pList = SharedFunctions.getProjectCenterList(iList); } catch { } listGuid = iList.ID; } SPListItem li = iList.GetItemById(int.Parse(dataRow["ITEM_ID"].ToString())); SharedFunctions.processMeta(iWeb, iList, li, new Guid(dataRow["ts_item_uid"].ToString()), dataRow["project"].ToString(), cn, pList); } catch { } } break; case "unsubmitTime": cmd = new SqlCommand("update TSTIMESHEET set submitted=0,approval_status=0,lastmodifiedbyu=@u,lastmodifiedbyn=@n where ts_uid=@ts_uid", cn); cmd.Parameters.AddWithValue("@ts_uid", Request["ts_uid"]); cmd.Parameters.AddWithValue("@u", SPContext.Current.Web.CurrentUser.LoginName); cmd.Parameters.AddWithValue("@n", SPContext.Current.Web.CurrentUser.Name); cmd.ExecuteNonQuery(); if (EPMLiveCore.CoreFunctions.getConfigSetting(SPContext.Current.Site.RootWeb, "EPMLiveTSDisableApprovals").ToLower() == "true" && !liveHours) { data = SharedFunctions.processActualWork(cn, Request["ts_uid"], site, true, true); } if (data == "") { data = "Success"; } break; case "deletePeriod": cmd = new SqlCommand("delete from tsperiod where period_id=@periodid and site_id=@siteid", cn); cmd.Parameters.AddWithValue("@siteid", SPContext.Current.Site.ID); cmd.Parameters.AddWithValue("@periodid", period); cmd.ExecuteNonQuery(); data = "Success"; break; case "addPeriod": cmd = new SqlCommand("select top 1 period_id from tsperiod where site_id=@siteid order by period_id desc", cn); cmd.Parameters.AddWithValue("@siteid", SPContext.Current.Site.ID); dr = cmd.ExecuteReader(); iperiod = 1; if (dr.Read()) { iperiod = dr.GetInt32(0) + 1; } dr.Close(); cmd = new SqlCommand("insert into tsperiod (period_start,period_end,period_id,site_id) values (@periodstart,@periodend,@period_id,@siteid)", cn); cmd.Parameters.AddWithValue("@periodstart", Request["start"]); cmd.Parameters.AddWithValue("@periodend", Request["end"]); cmd.Parameters.AddWithValue("@period_id", iperiod); cmd.Parameters.AddWithValue("@siteid", SPContext.Current.Site.ID); cmd.ExecuteNonQuery(); data = "Success"; break; case "addPeriods": var periods = JsonConvert.DeserializeObject <List <Dictionary <string, string> > >(Request[JsonDataParameter]); var createdIds = CreatePeriods(cn, periods); data = string.Format("Success,{0},{1}", strAction, string.Join(",", createdIds)); break; case "addType": cmd = new SqlCommand("select top 1 tstype_id from tstype where site_uid=@siteid order by tstype_id desc", cn); cmd.Parameters.AddWithValue("@siteid", SPContext.Current.Site.ID); dr = cmd.ExecuteReader(); iperiod = 1; if (dr.Read()) { iperiod = dr.GetInt32(0) + 1; } dr.Close(); cmd = new SqlCommand("insert into tstype (tstype_id,tstype_name,site_uid) values (@tstype_id,@tstype_name,@siteid)", cn); cmd.Parameters.AddWithValue("@tstype_name", Request["typename"]); cmd.Parameters.AddWithValue("@tstype_id", iperiod); cmd.Parameters.AddWithValue("@siteid", SPContext.Current.Site.ID); cmd.ExecuteNonQuery(); data = "Success"; break; case "editType": cmd = new SqlCommand("update tstype set tstype_name = @tstype_name where tstype_id=@tstype_id and site_uid=@siteid", cn); cmd.Parameters.AddWithValue("@tstype_name", Request["typename"]); cmd.Parameters.AddWithValue("@tstype_id", Request["typeid"]); cmd.Parameters.AddWithValue("@siteid", SPContext.Current.Site.ID); cmd.ExecuteNonQuery(); data = "Success"; break; case "approveTS": { approve(Request["ts_uids"].ToString(), SPContext.Current.Web, Request["Period"]); if (data == "") { data = "Success"; } } break; case "rejectTS": { string[] tsuids = Request["ts_uids"].Split(','); foreach (string tsuidData in tsuids) { string[] tsuid = tsuidData.Split('|'); cmd = new SqlCommand("update TSTIMESHEET set approval_status=2,approval_notes=@notes where ts_uid=@ts_uid", cn); cmd.Parameters.AddWithValue("@ts_uid", tsuid[0]); cmd.Parameters.AddWithValue("@notes", tsuid[1]); cmd.ExecuteNonQuery(); data += SharedFunctions.processActualWork(cn, tsuid[0], site, true, true); } if (data == "") { data = "Success"; } } break; case "unlockTS": { string[] tsuids = Request["ts_uids"].Split(','); foreach (string tsuidData in tsuids) { string[] tsuid = tsuidData.Split('|'); cmd = new SqlCommand("update TSTIMESHEET set approval_status=0 where ts_uid=@ts_uid", cn); cmd.Parameters.AddWithValue("@ts_uid", tsuid[0]); cmd.ExecuteNonQuery(); } data = "Success"; } break; case "rejectEmail": { string[] tsuids = Request["ts_uids"].Split(','); foreach (string tsuid in tsuids) { cmd = new SqlCommand("select username,approval_notes,period_start,period_end from vwTSApprovalNotes where ts_uid=@ts_uid", cn); cmd.Parameters.AddWithValue("@ts_uid", tsuid); dr = cmd.ExecuteReader(); if (dr.Read()) { string username = dr.GetString(0); string notes = dr.GetString(1); try { SPUser user = web.AllUsers[username]; if (user.Email != "") { System.Net.Mail.MailMessage mailMsg = new MailMessage(); mailMsg.From = new MailAddress(fEmail); mailMsg.To.Add(new MailAddress(user.Email)); mailMsg.Subject = web.Title + " Timesheet approval notice"; mailMsg.Body = "Your timesheet for period (" + dr.GetDateTime(2).ToShortDateString() + " - " + dr.GetDateTime(3).ToShortDateString() + ") has been rejected:<br>" + notes; mailMsg.IsBodyHtml = true; mailMsg.BodyEncoding = System.Text.Encoding.UTF8; mailMsg.Priority = MailPriority.Normal; // Configure the mail server SmtpClient smtpClient = new SmtpClient(); SPAdministrationWebApplication spWebAdmin = Microsoft.SharePoint.Administration.SPAdministrationWebApplication.Local; string sMailSvr = spWebAdmin.OutboundMailServiceInstance.Server.Name; smtpClient.Host = sMailSvr; smtpClient.Send(mailMsg); } } catch { } } dr.Close(); } } data = "Success"; break; case "autoadd": //string flagfield = ""; string lists = ""; SPSecurity.RunWithElevatedPrivileges(delegate() { using (SPSite uSite = SPContext.Current.Site) { //flagfield = EPMLiveCore.CoreFunctions.getConfigSetting(uSite.RootWeb, "EPMLiveTSFlag"); lists = EPMLiveCore.CoreFunctions.getConfigSetting(uSite.RootWeb, "EPMLiveTSLists"); } }); autoAdd(cn, Request["ts_uid"], web, lists); data = "Success"; break; case "approvePM": { string[] tsitemuids = Request["tsitemuids"].Split(','); foreach (string tsitemuid in tsitemuids) { //string[] tsuid = tsuidData.Split('|'); cmd = new SqlCommand("update tsitem set approval_status=1 where ts_item_uid=@tsitemuid", cn); cmd.Parameters.AddWithValue("@tsitemuid", tsitemuid); cmd.ExecuteNonQuery(); } data = "Success"; } break; case "rejectPM": { string[] tsitemuids = Request["tsitemuids"].Split(','); foreach (string tsitemuid in tsitemuids) { //string[] tsuid = tsuidData.Split('|'); cmd = new SqlCommand("update tsitem set approval_status=2 where ts_item_uid=@tsitemuid", cn); cmd.Parameters.AddWithValue("@tsitemuid", tsitemuid); cmd.ExecuteNonQuery(); } } data = "Success"; break; default: data = "Error: Invalid Command"; break; } ; } } } catch (Exception ex) { data = "Error: " + ex.Message; } } } }