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;
}
}
}
public override void getParams(SPWeb curWeb)
{
base.getParams(curWeb);
isTimesheet = true;
string strPeriod = Request["period"];
workType = Request["workType"];
period = int.Parse(strPeriod);
//base.inEditMode = true;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
string requestedUser = Page.Request["duser"];
string resname = "";
if (requestedUser != null && requestedUser != "")
{
if (SharedFunctions.canUserImpersonate(username, requestedUser, site.RootWeb, out resname))
{
username = requestedUser;
}
}
});
gridname = "mywork" + gridname;
if (workType == "1" || (workType == "4" && Request["allowOther"] == "true"))
{
if (inEditMode)
{
filterfield = "IsAssignment";
filtervalue = "False";
}
else
{
filterfield = "IsAssignment";
filtervalue = "0' OR IsAssignment='";
}
}
}
private void autoAdd(SqlConnection cn, string tsuid, SPWeb web, string rolluplists)
{
SqlConnection cnwss = null;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SPSite s = SPContext.Current.Site;
{
string dbCon = s.ContentDatabase.DatabaseConnectionString;
cnwss = new SqlConnection(dbCon);
cnwss.Open();
}
});
if (cnwss.State == ConnectionState.Open)
{
string period = Request["period"];
string siteurl = web.ServerRelativeUrl.Substring(1);
if (tsuid == null || tsuid == "")
{
tsuid = Guid.NewGuid().ToString();
SqlCommand cmd1 = new SqlCommand("INSERT INTO TSTIMESHEET (TS_UID,USERNAME,PERIOD_ID,SITE_UID,resourcename) VALUES (@TS_UID,@USERNAME,@PERIOD_ID,@SITE_UID,@resourcename)", cn);
cmd1.Parameters.AddWithValue("@TS_UID", tsuid);
cmd1.Parameters.AddWithValue("@USERNAME", username);
cmd1.Parameters.AddWithValue("@resourcename", resName);
cmd1.Parameters.AddWithValue("@PERIOD_ID", period);
cmd1.Parameters.AddWithValue("@SITE_UID", web.Site.ID);
cmd1.ExecuteNonQuery();
}
SharedFunctions.processResources(cn, tsuid, web, username);
SqlCommand cmd = new SqlCommand("SELECT period_start,period_end from TSPERIOD where site_id=@siteid and period_id=@period_id", cn);
cmd.Parameters.AddWithValue("@siteid", web.Site.ID);
cmd.Parameters.AddWithValue("@period_id", period);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
DateTime pstart = dr.GetDateTime(0);
DateTime pend = dr.GetDateTime(1);
dr.Close();
foreach (string rlist in rolluplists.Replace("\r\n", "\n").Split('\n'))
{
string lists = "";
string query = "";
if (siteurl == "")
{
query = "SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE (dbo.Webs.FullUrl LIKE '" + siteurl + "%' OR dbo.Webs.FullUrl = '" + siteurl + "') AND (dbo.AllLists.tp_Title like '" + rlist.Replace("'", "''") + "')";
}
else
{
query = "SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE (dbo.Webs.FullUrl LIKE '" + siteurl + "/%' OR dbo.Webs.FullUrl = '" + siteurl + "') AND (dbo.AllLists.tp_Title like '" + rlist.Replace("'", "''") + "')";
}
cmd = new SqlCommand(query, cnwss);
dr = cmd.ExecuteReader();
while (dr.Read())
{
lists += "<List ID='" + dr.GetGuid(0).ToString() + "'/>";
}
dr.Close();
if (lists != "")
{
SPUser u = web.Site.RootWeb.AllUsers[username];
SPSiteDataQuery dq = new SPSiteDataQuery();
dq.ViewFields = "<FieldRef Name='Title' Nullable='TRUE'/><FieldRef Name='Project' Nullable='TRUE'/>";
dq.Webs = "<Webs Scope='Recursive'/>";
dq.Lists = "<Lists>" + lists + "</Lists>";
dq.Query = "<Where><And><And><And><Eq><FieldRef Name=\"AssignedTo\" LookupId='True'/><Value Type=\"User\">" + u.ID.ToString() + "</Value></Eq><Eq><FieldRef Name=\"Timesheet\" /><Value Type=\"Boolean\">1</Value></Eq></And><Geq><FieldRef Name=\"DueDate\" /><Value Type=\"DateTime\">" + pstart.ToString("u") + "</Value></Geq></And><Leq><FieldRef Name=\"StartDate\" /><Value Type=\"DateTime\">" + pend.ToString("u") + "</Value></Leq></And></Where>";
DataTable dt = web.GetSiteData(dq);
Guid webGuid = new Guid();
Guid listGuid = new Guid();
SPWeb iWeb = null;
SPSite iSite = web.Site;
SPList iList = null;
SPList pList = null;
foreach (DataRow dRow in dt.Rows)
{
cmd = new SqlCommand("SELECT * FROM TSITEM where WEB_UID=@web_uid and LIST_UID = @list_uid and item_id=@item_id and ts_uid=@ts_uid", cn);
cmd.Parameters.AddWithValue("@WEB_UID", dRow["WEBID"]);
cmd.Parameters.AddWithValue("@LIST_UID", dRow["LISTID"]);
cmd.Parameters.AddWithValue("@ITEM_ID", dRow["ID"]);
cmd.Parameters.AddWithValue("@ts_uid", tsuid);
dr = cmd.ExecuteReader();
bool found = false;
if (dr.Read())
{
found = true;
}
dr.Close();
if (!found)
{
Guid wGuid = new Guid(dRow["WEBID"].ToString());
Guid lGuid = new Guid(dRow["LISTID"].ToString());
if (webGuid != wGuid)
{
pList = null;
if (iWeb != null)
{
iWeb.Close();
iWeb = iSite.OpenWeb(wGuid);
}
else
{
iWeb = iSite.OpenWeb(wGuid);
}
webGuid = iWeb.ID;
}
if (listGuid != lGuid)
{
iList = iWeb.Lists[lGuid];
pList = pList = SharedFunctions.getProjectCenterList(iList);
listGuid = iList.ID;
}
SPListItem li = iList.GetItemById(int.Parse(dRow["ID"].ToString()));
string project = "";
string project_id = "";
try
{
//project = iList.Fields["Project"].GetFieldValueAsText(dRow["Project"].ToString());
try
{
SPFieldLookupValue lv = new SPFieldLookupValue(li["Project"].ToString());
project = lv.LookupValue;
project_id = lv.LookupId.ToString();
if (project == null)
{
project = "";
project_id = "0";
}
}
catch { }
}
catch { }
Guid newTS = Guid.NewGuid();
cmd = new SqlCommand("INSERT INTO TSITEM (TS_UID,TS_ITEM_UID,WEB_UID,LIST_UID,ITEM_TYPE,ITEM_ID,TITLE,PROJECT,LIST,PROJECT_ID,PROJECT_LIST_UID) VALUES (@TS_UID,@TS_ITEM_UID,@WEB_UID,@LIST_UID,@ITEM_TYPE,@ITEM_ID,@TITLE,@PROJECT,@LIST,@PROJECT_ID,@projectlistuid)", cn);
cmd.Parameters.AddWithValue("@TS_UID", tsuid);
cmd.Parameters.AddWithValue("@TS_ITEM_UID", newTS);
cmd.Parameters.AddWithValue("@WEB_UID", dRow["WEBID"].ToString());
cmd.Parameters.AddWithValue("@LIST_UID", dRow["LISTID"].ToString());
cmd.Parameters.AddWithValue("@ITEM_TYPE", 1);
cmd.Parameters.AddWithValue("@ITEM_ID", dRow["ID"].ToString());
cmd.Parameters.AddWithValue("@TITLE", dRow["Title"].ToString());
cmd.Parameters.AddWithValue("@PROJECT", project);
cmd.Parameters.AddWithValue("@PROJECT_ID", project_id);
cmd.Parameters.AddWithValue("@LIST", rlist);
if (pList != null)
{
cmd.Parameters.AddWithValue("@projectlistuid", pList.ID);
}
else
{
cmd.Parameters.AddWithValue("@projectlistuid", DBNull.Value);
}
cmd.ExecuteNonQuery();
SharedFunctions.processMeta(iWeb, iList, li, newTS, project, cn, pList);
//processMeta(cn, iWeb, iList, li, newTS, project, pList);
}
}
}
}
SqlCommand cmd2 = new SqlCommand("UPDATE TSTIMESHEET set lastmodifiedbyu=@u,lastmodifiedbyn=@n where ts_uid=@TS_UID", cn);
cmd2.Parameters.AddWithValue("@TS_UID", tsuid);
cmd2.Parameters.AddWithValue("@u", SPContext.Current.Web.CurrentUser.LoginName);
cmd2.Parameters.AddWithValue("@n", SPContext.Current.Web.CurrentUser.Name);
cmd2.ExecuteNonQuery();
cnwss.Close();
}
}
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;
}
}
}
}
/*private void processMeta(SPWeb iWeb, SPList iList, SPListItem li, Guid newTS, string project)
* {
* string[] fields = EPMLiveCore.CoreFunctions.getConfigSetting(iWeb.Site.RootWeb, "EPMLiveTSFields-" + System.IO.Path.GetDirectoryName(iList.DefaultView.Url)).Split(',');
*
* foreach (string field in fields)
* {
* SPField f = null;
* string val = "";
* try
* {
* f = iList.Fields.GetFieldByInternalName(field);
* val = f.GetFieldValueAsText(li[f.Id]);
* }
* catch { }
* if (f != null)
* {
* SqlCommand cmd = new SqlCommand("INSERT INTO TSMETA (TS_ITEM_UID,ColumnName,DisplayName,ColumnValue,ListName) VALUES (@TS_ITEM_UID,@ColumnName,@DisplayName,@ColumnValue,@ListName)", cn);
* cmd.Parameters.AddWithValue("@TS_ITEM_UID", newTS);
* cmd.Parameters.AddWithValue("@ColumnName", field);
* cmd.Parameters.AddWithValue("@DisplayName", f.Title);
* cmd.Parameters.AddWithValue("@ColumnValue", val);
* cmd.Parameters.AddWithValue("@ListName", li.ParentList.Title);
* cmd.ExecuteNonQuery();
* }
* }
*
* if (pList != null && project != "")
* {
* fields = EPMLiveCore.CoreFunctions.getConfigSetting(iWeb.Site.RootWeb, "EPMLiveTSFields-Lists\\Project Center").Split(',');
*
* SPQuery query = new SPQuery();
* query.Query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>" + project + "</Value></Eq></Where>";
*
* SPListItemCollection lic = pList.GetItems(query);
* if (lic.Count > 0)
* {
* foreach (string field in fields)
* {
* SPField f = null;
* string val = "";
* try
* {
* f = pList.Fields.GetFieldByInternalName(field);
* val = f.GetFieldValueAsText(lic[0][f.Id]);
* }
* catch { }
* if (f != null)
* {
* try
* {
* SqlCommand cmd = new SqlCommand("INSERT INTO TSMETA (TS_ITEM_UID,ColumnName,DisplayName,ColumnValue,ListName) VALUES (@TS_ITEM_UID,@ColumnName,@DisplayName,@ColumnValue,@ListName)", cn);
* cmd.Parameters.AddWithValue("@TS_ITEM_UID", newTS);
* cmd.Parameters.AddWithValue("@ColumnName", field);
* cmd.Parameters.AddWithValue("@DisplayName", f.Title);
* cmd.Parameters.AddWithValue("@ColumnValue", val);
* cmd.Parameters.AddWithValue("@ListName", "Project Center");
* cmd.ExecuteNonQuery();
* }
* catch { }
* }
* }
* }
* }
* }*/
private void processItem(string gr_id, SPWeb iWeb, SPList iList, SPList pList)
{
string status = "";
string tsitemuid = "";
string firstdate = "";
string datecount = "";
string fieldcount = "";
string itemid = "";
string listid = "";
string webid = "";
string siteid = "";
string title = "";
if (tsuid == "")
{
tsuid = Request["tsuid"];
}
try
{
status = Request[gr_id + "_!nativeeditor_status"].ToString();
}
catch { }
try
{
tsitemuid = Request[gr_id + "_tsitemuid"].ToString();
}
catch { }
try
{
firstdate = Request[gr_id + "_firstdate"].ToString();
}
catch { }
try
{
datecount = Request[gr_id + "_datecount"].ToString();
}
catch { }
try
{
fieldcount = Request[gr_id + "_fieldcount"].ToString();
}
catch { }
try
{
itemid = Request[gr_id + "_itemid"].ToString();
}
catch { }
try
{
listid = Request[gr_id + "_listid"].ToString();
}
catch { }
try
{
webid = Request[gr_id + "_webid"].ToString();
}
catch { }
try
{
siteid = Request[gr_id + "_siteid"].ToString();
}
catch { }
try
{
title = Request[gr_id + "_title"].ToString();
}
catch { }
if (itemid == "")
{
output += "<action type='update' sid='" + gr_id + "'/>";
}
else
{
if (status == "deleted")
{
if (tsitemuid != "")
{
SqlCommand cmd = new SqlCommand("DELETE from tsitemhours where ts_item_uid=@itemuid", cn);
cmd.Parameters.AddWithValue("@itemuid", tsitemuid);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE from tsitem where ts_item_uid=@itemuid", cn);
cmd.Parameters.AddWithValue("@itemuid", tsitemuid);
cmd.ExecuteNonQuery();
}
output += "<action type='delete' sid='" + gr_id + "'/>";
}
else
{
if (tsitemuid != "")
{
processedTsItems += "," + tsitemuid;
try
{
DateTime dtStart = DateTime.Parse(firstdate);
int intFieldCount = int.Parse(fieldcount) + 1;
int intDateCount = int.Parse(datecount);
SqlCommand cmd;
SPListItem li = iList.GetItemById(int.Parse(itemid));
cmd = new SqlCommand("UPDATE tsitem set title = @title, approval_status = 0,project_list_uid=@projectlistuid where ts_item_uid=@itemuid", cn);
cmd.Parameters.AddWithValue("@itemuid", tsitemuid);
cmd.Parameters.AddWithValue("@title", li.Title);
if (pList != null)
{
cmd.Parameters.AddWithValue("@projectlistuid", pList.ID);
}
else
{
cmd.Parameters.AddWithValue("@projectlistuid", DBNull.Value);
}
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE from tsitemhours where ts_item_uid=@itemuid", cn);
cmd.Parameters.AddWithValue("@itemuid", tsitemuid);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE from tsnotes where ts_item_uid=@itemuid", cn);
cmd.Parameters.AddWithValue("@itemuid", tsitemuid);
cmd.ExecuteNonQuery();
int daycounter = 0;
for (int i = 0; i < intDateCount; i++)
{
try
{
string showday = "";
try
{
showday = dayDefs[((int)dtStart.AddDays(daycounter).DayOfWeek) * 3];
}
catch { }
//if (dtStart.AddDays(i).DayOfWeek != DayOfWeek.Sunday && dtStart.AddDays(i).DayOfWeek != DayOfWeek.Saturday)
if (showday == "True")
{
string fieldData = Request[gr_id + "_c" + (intFieldCount + i + 1).ToString()];
if (timeeditor)
{
string[] strFieldData = fieldData.Split('|');
for (int j = 0; j < strFieldData.Length; j += 2)
{
ExecuteInsertCommand(strFieldData, j, tsitemuid, dtStart, daycounter);
}
}
else
{
if (fieldData != "0")
{
cmd = new SqlCommand("INSERT INTO TSITEMHOURS (TS_ITEM_UID,TS_ITEM_DATE,TS_ITEM_HOURS,TS_ITEM_TYPE_ID) VALUES (@itemuid,@itemdate,@hours,0)", cn);
cmd.Parameters.AddWithValue("@itemuid", tsitemuid);
cmd.Parameters.AddWithValue("@itemdate", dtStart.AddDays(daycounter));
cmd.Parameters.AddWithValue("@hours", fieldData.Split(',')[0]);
cmd.ExecuteNonQuery();
}
}
}
else
{
i--;
}
daycounter++;
}
catch { }
}
}
catch { }
}
else
{
try
{
DateTime dtStart = DateTime.Parse(firstdate);
int intFieldCount = int.Parse(fieldcount);
int intDateCount = int.Parse(datecount);
Guid newTS = Guid.NewGuid();
tsitemuid = newTS.ToString();
processedTsItems += "," + newTS.ToString();
if (tsuid == "")
{
tsuid = Guid.NewGuid().ToString();
SqlCommand cmd1 = new SqlCommand("INSERT INTO TSTIMESHEET (TS_UID,USERNAME,PERIOD_ID,SITE_UID,resourcename) VALUES (@TS_UID,@USERNAME,@PERIOD_ID,@SITE_UID,@resourcename)", cn);
cmd1.Parameters.AddWithValue("@TS_UID", tsuid);
cmd1.Parameters.AddWithValue("@USERNAME", username);
cmd1.Parameters.AddWithValue("@resourcename", resName);
cmd1.Parameters.AddWithValue("@PERIOD_ID", Request["period"]);
cmd1.Parameters.AddWithValue("@SITE_UID", siteid);
cmd1.ExecuteNonQuery();
}
SPListItem li = iList.GetItemById(int.Parse(itemid));
SPField pField = null;
string project = "";
string project_id = "0";
try
{
pField = li.ParentList.Fields.GetFieldByInternalName("Project");
}
catch { }
if (pField != null && pList != null)
{
try
{
SPFieldLookupValue lv = new SPFieldLookupValue(li["Project"].ToString());
project = lv.LookupValue;
project_id = lv.LookupId.ToString();
if (project == null)
{
project = "";
project_id = "0";
}
}
catch { }
}
int itemtype = 1;
if (nonworklist == iList.ID)
{
itemtype = 2;
}
// Checking if any customer is using custom projectcenter
string projectListName = string.Empty;
projectListName = EPMLiveCore.CoreFunctions.getConfigSetting(iWeb, "EPMLiveCustomProjectList");
if (!string.IsNullOrEmpty(projectListName))
{
ListProjectCenter = projectListName;
}
string rate = SharedFunctions.GetStandardRates(cn, tsuid.ToString(), iWeb, username, $"{webid}.{iWeb.Lists[ListProjectCenter].ID}.{project_id}");
SqlCommand cmd = new SqlCommand("INSERT INTO TSITEM (TS_UID,TS_ITEM_UID,WEB_UID,LIST_UID,ITEM_TYPE,ITEM_ID,TITLE,PROJECT,PROJECT_ID,LIST,PROJECT_LIST_UID,Rate) VALUES (@TS_UID,@TS_ITEM_UID,@WEB_UID,@LIST_UID,@ITEM_TYPE,@ITEM_ID,@TITLE,@PROJECT,@PROJECT_ID,@LIST,@projectlistuid,@rate)", cn);
cmd.Parameters.AddWithValue("@TS_UID", tsuid);
cmd.Parameters.AddWithValue("@TS_ITEM_UID", newTS);
cmd.Parameters.AddWithValue("@WEB_UID", webid);
cmd.Parameters.AddWithValue("@LIST_UID", listid);
cmd.Parameters.AddWithValue("@ITEM_TYPE", itemtype);
cmd.Parameters.AddWithValue("@ITEM_ID", itemid);
cmd.Parameters.AddWithValue("@TITLE", title);
cmd.Parameters.AddWithValue("@PROJECT", project);
cmd.Parameters.AddWithValue("@PROJECT_ID", project_id);
cmd.Parameters.AddWithValue("@LIST", li.ParentList.Title);
cmd.Parameters.AddWithValue("@rate", rate);
if (pList != null)
{
cmd.Parameters.AddWithValue("@projectlistuid", pList.ID);
}
else
{
cmd.Parameters.AddWithValue("@projectlistuid", DBNull.Value);
}
cmd.ExecuteNonQuery();
Logger.WriteLog(Logger.Category.Medium, "SaveTimesheet.aspx: ", string.Format("Adding item id: {0} to TS: {1}, user id: {2}", itemid, tsuid, username ?? ""));
if (pField != null)
{
SharedFunctions.processMeta(iWeb, iList, li, newTS, project, cn, pList);
}
int daycounter = 0;
for (int i = 0; i < intDateCount; i++)
{
string showday = "";
try
{
showday = dayDefs[((int)dtStart.AddDays(daycounter).DayOfWeek) * 3];
}
catch { }
//if (dtStart.AddDays(i).DayOfWeek != DayOfWeek.Sunday && dtStart.AddDays(i).DayOfWeek != DayOfWeek.Saturday)
if (showday == "True")
{
string fieldData = Request[gr_id + "_c" + (intFieldCount + i + 2).ToString()];
if (timeeditor)
{
string[] strFieldData = fieldData.Split('|');
for (int j = 0; j < strFieldData.Length; j += 2)
{
ExecuteInsertCommand(strFieldData, j, tsitemuid, dtStart, daycounter);
}
}
else
{
if (fieldData != "0")
{
cmd = new SqlCommand("INSERT INTO TSITEMHOURS (TS_ITEM_UID,TS_ITEM_DATE,TS_ITEM_HOURS,TS_ITEM_TYPE_ID) VALUES (@itemuid,@itemdate,@hours,0)", cn);
cmd.Parameters.AddWithValue("@itemuid", newTS);
cmd.Parameters.AddWithValue("@itemdate", dtStart.AddDays(daycounter));
cmd.Parameters.AddWithValue("@hours", fieldData);
cmd.ExecuteNonQuery();
}
}
}
else
{
i--;
}
daycounter++;
}
}
catch { }
}
output += "<action type='updateitem' sid='" + gr_id + "' tid='" + gr_id + "' tsitemuid='" + tsitemuid + "'/>";
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
Guid webGuid = new Guid();
Guid siteGuid = new Guid();
Guid listGuid = new Guid();
SPWeb iWeb = null;
SPSite iSite = null;
SPList iList = null;
SPList pList = null;
byte[] encodedDataAsBytes = System.Convert.FromBase64String(Request["columns"]);
strFields = System.Text.ASCIIEncoding.ASCII.GetString(encodedDataAsBytes).Split('\n');
if (Request["ids"] != null)
{
Response.ContentType = "text/xml";
Response.ContentEncoding = System.Text.Encoding.UTF8;
string[] ids = Request["ids"].Split(',');
output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><data>";
SPSecurity.RunWithElevatedPrivileges(delegate()
{
cn = new SqlConnection(EPMLiveCore.CoreFunctions.getConnectionString(SPContext.Current.Site.WebApplication.Id));
cn.Open();
try
{
nonworklist = SPContext.Current.Web.Site.RootWeb.Lists[EPMLiveCore.CoreFunctions.getConfigSetting(SPContext.Current.Web.Site.RootWeb, "EPMLiveTSNonWork")].ID;
}
catch { }
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;
}
}
dayDefs = EPMLiveCore.CoreFunctions.getConfigSetting(SPContext.Current.Site.RootWeb, "EPMLiveDaySettings").Split('|');
bool.TryParse(EPMLiveCore.CoreFunctions.getConfigSetting(SPContext.Current.Site.RootWeb, "EPMLiveTSLiveHours"), out liveHours);
});
if (impFailed)
{
output += "<action type='error100'>Unable to impersonate for: " + Request["duser"] + "</action>";
}
else
{
SqlCommand cmd = new SqlCommand("SELECT tstype_id,tstype_name from TSTYPE where site_uid=@site_id", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@site_id", SPContext.Current.Site.ID);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
timeeditor = true;
}
dr.Close();
if (EPMLiveCore.CoreFunctions.getConfigSetting(SPContext.Current.Web.Site.RootWeb, "EPMLiveTSAllowNotes").ToLower() == "true")
{
timeeditor = true;
}
if (cn.State == ConnectionState.Open)
{
foreach (string id in ids)
{
if (id != "")
{
string webId;
string listId;
string siteId;
SaveHelper.ParseSiteFromRequest(Request, id, out webId, out listId, out siteId);
if (!string.IsNullOrWhiteSpace(webId) && !string.IsNullOrWhiteSpace(listId) && !string.IsNullOrWhiteSpace(siteId))
{
try
{
SaveHelper.PopulateGuidData(
webId,
listId,
siteId,
list => pList = SharedFunctions.getProjectCenterList(list),
ref siteGuid,
ref iWeb,
ref iSite,
ref webGuid,
ref listGuid,
ref iList);
}
catch (Exception ex)
{
output += "<action type='error100'>Item: " + Request[id + "_title"].ToString() + " Message: " + ex.Message + "</action>";
}
if (iWeb != null)
{
string status = "";
try
{
status = Request[id + "_!nativeeditor_status"].ToString();
}
catch { }
processItem(id, iWeb, iList, pList);
if (liveHours)
{
processLiveHours(id, listGuid, iList);
}
if (status != "deleted" && bool.Parse(Request["edit"]))
{
processWssItem(id, iWeb, iList);
}
}
}
}
}
}
SqlCommand cmd1 = new SqlCommand("UPDATE TSTIMESHEET set approval_status=0,lastmodifiedbyu=@u,lastmodifiedbyn=@n where ts_uid=@TS_UID", cn);
cmd1.Parameters.AddWithValue("@TS_UID", tsuid);
cmd1.Parameters.AddWithValue("@u", SPContext.Current.Web.CurrentUser.LoginName);
cmd1.Parameters.AddWithValue("@n", SPContext.Current.Web.CurrentUser.Name);
cmd1.ExecuteNonQuery();
SharedFunctions.processResources(cn, tsuid, SPContext.Current.Web, username);
}
cn.Close();
output += "<action type='settsuid' tsuid='" + tsuid + "'/>";
output += "</data>";
}
}
