private DataTable GetDataSource(SPListItemCollection items) { DataTable dt = new DataTable(); switch (this._webpartType) { case WebPartType.News: string newsType = this._newsType; string subNewsType = this._subNewsType; if (newsType.IsNotNullOrWhitespace() && subNewsType.IsNotNullOrWhitespace()) { var query = from e in items.GetDataTable().AsEnumerable() where newsType.Equals(e.Field<string>("NewsType")) && subNewsType.Equals(e.Field<string>("NewsSubType")) select e; dt = query.AsDataView().ToTable(); } break; case WebPartType.Announcement: dt = items.GetDataTable(); break; default: break; } return dt; }
private static SPListItemCollection GetCostsFromMonth(SPList costs, SPListItemCollection delegationsForSelectedMonth) { StringBuilder delegationIDBuilder = new StringBuilder(); foreach (SPListItem delegationItem in delegationsForSelectedMonth) { delegationIDBuilder.AppendFormat("<Value Type='Lookup'>{0}</Value>", delegationItem["ID"]); } string costsForSelectedDelagationsQueryString = string.Format( "<Where>" + "<In>" + "<FieldRef Name='{0}' LookupId='TRUE'/>" + "<Values>" + "{1}" + "</Values>" + "</In>" + "</Where>", DelegationsFields.Delegation.Name, delegationIDBuilder); SPQuery costsForSelectedDelegationsQuery = new SPQuery(); costsForSelectedDelegationsQuery.Query = costsForSelectedDelagationsQueryString; SPListItemCollection monthlyCosts = costs.GetItems(costsForSelectedDelegationsQuery); return monthlyCosts; }
protected void Page_Load(object sender, EventArgs e) { currentList = SPContext.Current.Web.Lists[new Guid(Context.Request["List"])]; SPView defView = currentList.DefaultView; items = currentList.GetItems(defView); if (!Page.IsPostBack) PopulatePage(); this.TemplatesList.SelectedIndexChanged += new EventHandler(TemplatesList_SelectedIndexChanged); this.DropDownList1.SelectedIndexChanged += new EventHandler(DropDownList1_SelectedIndexChanged); this.ImageButton1.Click += new System.Web.UI.ImageClickEventHandler(ImageButton1_Click); }
public static void ResoreTask(SPListItemCollection splicCompletedTasks, SPList splTask) { foreach (SPListItem item in splicCompletedTasks) { string sTaskID = item["TaskID"] == null ? string.Empty : item["TaskID"].ToString(); string sTitle = item["Title"] == null ? string.Empty : item["Title"].ToString().Replace(sRestoreTitle,""); // string sGUID = item["OriginalGuid"] == null ? string.Empty : item["OriginalGuid"].ToString(); if (CheckGUID(splTask, sTaskID)) // if (isUnique(splTask, sTitle, sAssignedTo)) { SPListItem TaskItem = splTask.Items.Add(); foreach (SPField field in TaskItem.Fields) { try { if ((!field.ReadOnlyField) && (field.InternalName != "Attachments")) { string sInernalName=field.InternalName; if (sInernalName == "Title") { TaskItem[field.InternalName] = item[field.InternalName].ToString() + sRestoreTitle; } else { TaskItem[sInernalName] = item[sInernalName]; } } } catch (Exception ex) { ex.ToString(); } } TaskItem["Outcome"] = item["Outcome"]; TaskItem["Link"] = item["Link"]; TaskItem.Update(); //only now you call update! TaskItem["DueDate"] = item["EndDate"]; //item["TaskID"] = TaskItem.ID;//更新CompletedTask的TaskID为Task的ID。 string sLog = "Restore task:" + item["Title"].ToString(); WriteErrorLog(sLog); } } }
protected void Query(SPWeb site, XElement caml) { AssertValidSite(site); AssertListExistence(site); AssertListDefinitionSetBySubclass(); var watch = new Stopwatch(); var list = site.Lists.TryGetListByInternalName(Definition.ListName); var query = new SPQuery { Query = caml.ToString() }; Debug.WriteLine( string.Format("About to run query against list '{0}': {1}", list, caml)); watch.Start(); Result = list.GetItems(query); watch.Stop(); Debug.WriteLine( string.Format("Query against '{0}' returned {1} rows in {2} ms", list, Result.Count, watch.ElapsedMilliseconds)); }
public List<object> GetSerializingList(SPListItemCollection items, object obj) { List<object> oList = new List<object>(); Hashtable hashtable; foreach (SPListItem item in items) { hashtable = new Hashtable(); foreach (PropertyInfo prop in obj.GetType().GetProperties()) { if (items.List.Fields.ContainsField(prop.Name)) { hashtable.Add(prop.Name, item[prop.Name].AsString()); } } oList.Add(hashtable); } return oList; }
public static void CopyList(SPListItemCollection spliicSoruce, SPList splTarget) { foreach (SPListItem item in spliicSoruce) { string sTitle = item["Title"] == null ? string.Empty : item["Title"].ToString(); if (sTitle.Contains(sRestoreTitle))//是还原过来的Task { continue; } if (isUnique(splTarget,item.ID.ToString()))// sTitle, sAssignedTo)) { SPListItem newDestItem = splTarget.Items.Add(); foreach (SPField field in item.Fields) { try { string sInternalName = field.InternalName; if ((!field.ReadOnlyField) && (sInternalName != "Attachments")) { newDestItem[sInternalName] = item[sInternalName]; } } catch (Exception ex) { ex.ToString(); } } newDestItem["TaskID"] = item.ID; newDestItem["Link"] = item["Link"]; newDestItem["EndDate"] = item["Modified"]; newDestItem["Outcome"] = item["Outcome"]; newDestItem.Update(); string sLog = "MoveTask:"+item["Title"].ToString(); WriteErrorLog(sLog); } } }
public List<ListFields> GetAllListColumns(string _siteName , string _listName) { List<ListFields> lsF = new List<ListFields>(); using (SPSite site = new SPSite(_siteName)) { using (web = site.OpenWeb()) { try { SPList list = web.Lists[_listName]; for (int i = 0; i < list.Fields.Count; i++) { ListFields ls = new ListFields(); ls.Title = list.Fields[i].Title; ls.InternalName = list.Fields[i].InternalName; lsF.Add(ls); } SPQuery query = new SPQuery(); ///TO DO order by Create Date query.Query = "<OrderBy><FieldRef Name=\"Order\" /></OrderBy>"; items = list.GetItems(query); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } return lsF; }
public Job[] DownloadNewJobs() { List <Job> jobs = new List <Job>(); string sWeb = System.Configuration.ConfigurationManager.AppSettings["Server"]; string sList = System.Configuration.ConfigurationManager.AppSettings["List"]; bool moderationEnabled = Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["ModerationEnabled"]); uint itemsToProcess = Convert.ToUInt32(System.Configuration.ConfigurationManager.AppSettings["ItemsToProcess"]); using (SPWeb web = new SPSite(sWeb).OpenWeb()) { SPList list = web.Lists[sList]; string camlQuery = string.Empty; if (!moderationEnabled) { camlQuery = @" <OrderBy> <FieldRef Name=""ID"" /> </OrderBy> <Where> <And> <Eq> <FieldRef Name=""Status"" /> <Value Type=""Text"">Not Started</Value> </Eq> <Lt> <FieldRef Name=""When"" /> <Value IncludeTimeValue='TRUE' Type=""DateTime""> <Today /> </Value> </Lt> </And> </Where>"; } else { camlQuery = @" <OrderBy> <FieldRef Name=""ID"" /> </OrderBy> <Where> <And> <And> <Eq> <FieldRef Name=""Status"" /> <Value Type=""Text"">Not Started</Value> </Eq> <Lt> <FieldRef Name=""When"" /> <Value IncludeTimeValue='TRUE' Type=""DateTime""><Today /></Value> </Lt> </And> <Eq> <FieldRef Name=""_ModerationStatus"" /> <Value Type=""ModStat"">Approved</Value> </Eq> </And> </Where>"; } SPQuery query = new SPQuery(); query.Query = camlQuery; query.RowLimit = itemsToProcess; SPListItemCollection results = list.GetItems(query); foreach (SPListItem item in results) { string pre = Convert.ToString(item["Prerequisite"]); Job job = new Job(item.ID, string.IsNullOrEmpty(pre) ? 0 : Convert.ToInt32(pre), item.Title, Convert.ToString(item["Task"]), Convert.ToDateTime(item["When"]), item.Attachments); jobs.Add(job); } } return(jobs.ToArray()); }
protected void Page_Load(object sender, EventArgs e) { if (SPContext.Current.Web != null) { try { SPWeb web = SPContext.Current.Site.RootWeb; SPList list = web.Lists["Footer Links"]; if (list != null) { SPQuery query = new SPQuery(); query.Query = @" <OrderBy> <FieldRef Name=""SortOrder"" Ascending=""True"" /> </OrderBy>"; // Returns all items SPListItemCollection footerItems = list.GetItems(query); // create data table with results returned DataTable dt = new DataTable(); dt.Columns.Add("Title"); dt.Columns.Add("Url"); foreach (SPListItem item in footerItems) { DataRow dr = dt.NewRow(); dr["Title"] = item[TitleField] as string; if (item[UrlField] != null) { SPFieldUrlValue url = new SPFieldUrlValue(item[UrlField].ToString()); if (!string.IsNullOrEmpty(url.Url)) { dr["Url"] = url.Url; } } dt.Rows.Add(dr); } FooterList.DataSource = dt.DefaultView; FooterList.DataBind(); } else { // clear controls this.Controls.Clear(); } } catch (Exception ex) { // clear controls this.Controls.Clear(); } } }
private void FillListItem(HtmlTextWriter writer, SPListItemCollection items, DataTable sourceData, string txtViewUrl) { foreach (SPListItem item in items) { DataRow row = sourceData.NewRow(); row["通知标题"] = item["标题"]; row["通知ID"] = item.ID; row["通知创建时间"] = string.Format("{0:yyyy-MM-dd}", item["创建时间"].ToString()); //item["创建时间"].ToString("d"); if (item["置顶截止时间"] != null) { row["置顶时间"] = string.Format("{0:yyyy-MM-dd}", item["置顶截止时间"].ToString()); } else { row["置顶时间"] = string.Format("{0:yyyy-MM-dd}", item["创建时间"].ToString()); } sourceData.Rows.Add(row); } int num = sourceData.Rows.Count; if (num > listCount) { num = listCount; } string lstUrl = items.List.DefaultViewUrl; string url = txtViewUrl.Replace("AllPosts.aspx", "Post.aspx?ID="); writer.Write("<ul class='ScrollNote-list'>"); for (int i = 0; i < num; i++) { string lUrl = url; lUrl = lUrl + sourceData.Rows[i]["通知ID"]; DateTime dtCreated = (DateTime)sourceData.Rows[i]["通知创建时间"]; DateTime dtTop = (DateTime)sourceData.Rows[i]["置顶时间"]; string dtStr = dtCreated.ToString("yyyy-MM-dd"); string strName = sourceData.Rows[i]["通知标题"].ToString(); if (strName.Length > 20) { strName = strName.Substring(0, 20) + "…"; } if (dtCreated < dtTop)//当前为置顶通知 { writer.Write("<li title='【重要通知】' style='background-image: url(/_layouts/15/2052/images/top.png);background-position:left top;background-size:20px 20px;background-repeat: no-repeat;padding-left:5px;'>"); } else { writer.Write("<li>"); } if (ShowIcon) { if (DateTime.Now.Subtract(dtCreated).Days <= this.BeforeDays) { writer.Write(string.Concat(new object[] { "<a title='", sourceData.Rows[i]["通知标题"], "' href='", lUrl, "'>", strName, "<img title='新' class='ms-newgif' alt='新' src='/_layouts/15/2052/images/star.png' height='20px' width='20px'><span> ", dtStr, "</span></a>" }) + "</li>"); } else { writer.Write(string.Concat(new object[] { "<a title='", sourceData.Rows[i]["通知标题"], "' href='", lUrl, "'>", strName, " <span>", dtStr, "</span></a>" }) + "</li>"); } } else { writer.Write(string.Concat(new object[] { "<a title='", sourceData.Rows[i]["通知标题"], "' href='", lUrl, "'>", strName, " <span>", dtStr, "</span></a>" }) + "</li>"); } } writer.Write("</ul>"); }
private static string GetBatchDeleteXmlCommand(SPList list, SPListItemCollection items) { StringBuilder xmlCommandStringBuilder = new StringBuilder(); xmlCommandStringBuilder.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>"); StringBuilder commandFormatBuilder = new StringBuilder(); commandFormatBuilder.Append("<Method>"); commandFormatBuilder.Append("<SetList Scope=\"Request\">" + list.ID + "</SetList>"); commandFormatBuilder.Append("<SetVar Name=\"ID\">{0}</SetVar>"); commandFormatBuilder.Append("<SetVar Name=\"owsfileref\">{1}</SetVar>"); commandFormatBuilder.Append("<SetVar Name=\"Cmd\">Delete</SetVar>"); commandFormatBuilder.Append("</Method>"); string commandFormat = commandFormatBuilder.ToString(); foreach (SPListItem item in items) { xmlCommandStringBuilder.Append(string.Format(commandFormat, item.ID.ToString(), HttpUtility.UrlDecode((string)item[SPBuiltInFieldId.EncodedAbsUrl]))); } xmlCommandStringBuilder.Append("</Batch>"); return xmlCommandStringBuilder.ToString(); }
internal static void User(SPUser user, DirectoryEntry directoryEntry, SPListItemCollection listItems, int itemCount, int u) { try { var j = 0; for (; j < itemCount; j++) { shouldUpdate = false; var item = listItems[j]; if (!string.Equals(item["Name"].ToString(), user.LoginName, StringComparison.CurrentCultureIgnoreCase)) { continue; } var title = (directoryEntry.Properties["displayName"].Value == null) ? string.Empty : directoryEntry.Properties["displayName"].Value.ToString(); try { TryUpdateValue(item, "Title", (string)item["Title"], title); } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "Title", item.DisplayName, item.ID, item.Web.Site.Url), null); } var eMail = (directoryEntry.Properties["mail"].Value == null) ? string.Empty : directoryEntry.Properties["mail"].Value.ToString(); try { TryUpdateValue(item, "EMail", (string)item["EMail"], eMail); } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "EMail", item.DisplayName, item.ID, item.Web.Site.Url), null); } var jobTitle = (directoryEntry.Properties["title"].Value == null) ? string.Empty : directoryEntry.Properties["title"].Value.ToString(); try { TryUpdateValue(item, "JobTitle", (string)item["JobTitle"], jobTitle); } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "JobTitle", item.DisplayName, item.ID, item.Web.Site.Url), null); } var mobilePhone = (directoryEntry.Properties["mobile"].Value == null) ? string.Empty : directoryEntry.Properties["mobile"].Value.ToString(); try { TryUpdateValue(item, "MobilePhone", (string)item["MobilePhone"], mobilePhone); } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "MobilePhone", item.DisplayName, item.ID, item.Web.Site.Url), null); } if (user.SystemUserKey != null) { var uri = ThumbnailHandler.GetThumbnail(user, directoryEntry); try { if (!string.IsNullOrEmpty(uri)) { item["Picture"] = uri; } else if (string.IsNullOrEmpty(uri)) { item["Picture"] = string.Empty; } } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "Picture", item.DisplayName, item.ID, item.Web.Site.Url), null); } } try { if (directoryEntry.Properties["proxyAddresses"].Value != null) { var array = (Array)directoryEntry.Properties["proxyAddresses"].Value; foreach (var o in from string o in array where o.Contains(("sip:")) select o) { var sipAddress = o.Remove(0, 4); try { TryUpdateValue(item, "SipAddress", (string)item["SipAddress"], sipAddress); } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "SipAddress", item.DisplayName, item.ID, item.Web.Site.Url), null); } } } } catch (InvalidCastException) { if (directoryEntry.Properties["proxyAddresses"].Value.ToString().Contains("sip:")) { var sipAddress = directoryEntry.Properties["proxyAddresses"].Value.ToString().Remove(0, 4); try { TryUpdateValue(item, "SipAddress", (string)item["SipAddress"], sipAddress); } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "SipAddress", item.DisplayName, item.ID, item.Web.Site.Url), null); } } else { try { item["SipAddress"] = string.Empty; } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "SipAddress", item.DisplayName, item.ID, item.Web.Site.Url), null); } } } var department = (directoryEntry.Properties["department"].Value == null) ? string.Empty : directoryEntry.Properties["department"].Value.ToString(); try { TryUpdateValue(item, "Department", (string)item["Department"], department); } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "Department", item.DisplayName, item.ID, item.Web.Site.Url), null); } try { var additionalAttributes = FoundationSyncSettings.Local.AdditionalUserAttributes; foreach (var ldapAttribute in additionalAttributes) { var value = (directoryEntry.Properties[ldapAttribute.Value].Value == null) ? string.Empty : directoryEntry.Properties[ldapAttribute.Value].Value.ToString(); TryUpdateValue(item, ldapAttribute.Key, (string)item[ldapAttribute.Key], value); } } catch (Exception) { FoudationSync.LogMessage(506, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, string.Format("Unable to update {0} for user {1} (ID {2}) on Site Collection {3}.", "AdditionalAttribues Value", item.DisplayName, item.ID, item.Web.Site.Url), null); } if (shouldUpdate) { FoudationSync.LogMessage(201, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Verbose, string.Format("Updating user {0} (ID {1}) on Site Collection {2}.", item.DisplayName, item.ID, item.Web.Site.Url), null); item.Update(); ++u; } return; } } catch (SPException exception) { FoudationSync.LogMessage(401, FoudationSync.LogCategories.FoundationSync, TraceSeverity.Unexpected, exception.Message + " " + exception.StackTrace, null); } }
private void GetItems() { if (_query == null) { _query = new SPQuery { ViewAttributes = "Scope=\"Recursive\"", RowLimit = 300 }; } _items = _list.GetItems(_query); _itemEnumerator = _items.GetEnumerator(); _query.ListItemCollectionPosition = _items.ListItemCollectionPosition; lastBatch = (_query.ListItemCollectionPosition == null); }
/// <summary> /// Get and return table with correct url /// </summary> /// <param name="categoryListName">categoryListName</param> /// <param name="items"></param> /// <returns></returns> public static DataTable GetTableWithCorrectUrlHotNews(string categoryListName, SPListItemCollection items) { var dataTable = items.GetDataTable(); if (!dataTable.Columns.Contains(FieldsName.CategoryId)) { dataTable.Columns.Add(FieldsName.CategoryId, Type.GetType("System.String")); } if (!dataTable.Columns.Contains(Constants.ListCategoryName)) { dataTable.Columns.Add(Constants.ListCategoryName, Type.GetType("System.String")); } if (!dataTable.Columns.Contains(Constants.ListName)) { dataTable.Columns.Add(Constants.ListName, Type.GetType("System.String")); } if (items != null && items.Count > 0) { string imagepath = string.Empty; ImageFieldValue imageIcon; SPFieldUrlValue advLink; for (int i = 0; i < items.Count; i++) { var listUrl = items.List.RootFolder.ServerRelativeUrl.Split('/'); dataTable.Rows[i][Constants.ListName] = listUrl[listUrl.Length - 1]; SPFieldLookup catFile = (SPFieldLookup)items.List.Fields.GetFieldByInternalName(FieldsName.NewsRecord.English.CategoryName); listUrl = SPContext.Current.Web.Lists.GetList(new Guid(catFile.LookupList), true).RootFolder.ServerRelativeUrl.Split('/'); dataTable.Rows[i][Constants.ListCategoryName] = listUrl[listUrl.Length - 1]; imagepath = Convert.ToString(items[i][FieldsName.NewsRecord.English.ThumbnailImage]); imageIcon = items[i][FieldsName.NewsRecord.English.PublishingPageImage] as ImageFieldValue; if (imageIcon != null) { dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imageIcon.ImageUrl; } else { if (imagepath.Length > 2) dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath.Trim().Substring(0, imagepath.Length - 2); else { dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath; } } if (items[i].Fields.ContainsField(FieldsName.NewsRecord.English.LinkAdv)) { advLink = new SPFieldUrlValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.LinkAdv])); dataTable.Rows[i][FieldsName.NewsRecord.English.LinkAdv] = advLink.Url; } dataTable.Rows[i][FieldsName.NewsRecord.English.ShortContent] = StripHtml(Convert.ToString(dataTable.Rows[i][FieldsName.NewsRecord.English.ShortContent])); if (!string.IsNullOrEmpty(Convert.ToString(items[i][FieldsName.NewsRecord.English.CategoryName]))) { SPFieldLookupValue catLK = new SPFieldLookupValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.CategoryName])); dataTable.Rows[i][FieldsName.CategoryId] = catLK.LookupId; } } } return dataTable; }
/// <summary> /// Converts the list items returned from the data query to a <see cref="List"/> of <see cref="ReviewModel"/> items. This /// method will also create the create / edit form hyperlink. /// </summary> /// <param name="listItems">A <see cref="SPListItemCollection"/> passed from the query.</param> /// <returns>A <see cref="List"/> of <see cref="ReviewModel"/> items.</returns> private List<ReviewModel> PopulateDataValues(SPListItemCollection listItems) { var data = new List<ReviewModel>(); foreach (SPListItem item in listItems) { string author = item["Author"].ToString(); if (IsUserAuthor(author)) { CreateFormHyperlink(false, item["ID"].ToString()); } else { CreateFormHyperlink(true, string.Empty); } data.Add(new ReviewModel { Id = (int)item["ID"], Title = item["Title"] as string, PublishDate = (DateTime)item["Modified"], Body = item["Review"].ToString(), Author = FormatAuthor(author) }); } return (data); }
public void UpdateCaseDetails() { string siteUrl = "http://azportal"; using (SPSite site = new SPSite(siteUrl)) { using (SPWeb web = site.OpenWeb()) { SPList list = web.GetList("http://azportal/Lists/Azure Outage List"); // Get the item collections which the case details haven't been updated SPQuery query = new SPQuery(); query.Query = "<Where><Neq><FieldRef Name=\"Active\" /><Value Type=\"Text\">" + "0" + "</Value></Neq></Where>";// active!=0 SPListItemCollection items = list.GetItems(query); // Get info and fill in all items in the item collection foreach (SPListItem item in items) { if ((string)item["Case ID"] == null) { continue; } string SRNumber = (item["Case ID"]).ToString(); string MSSolveBaseURL = @"https://mssolveweb.partners.extranet.microsoft.com/MSSolveWeb/Home"; try { // call the MSSolve Web Service to get the response. WebRequest webRequest = WebRequest.Create(MSSolveBaseURL + "/GetSR/" + SRNumber + "/0"); webRequest.Timeout = 180000; ((HttpWebRequest)webRequest).UserAgent = @"Mozilla/5.0 (Windows NT 6.3; WOW64; Trident/7.0; rv:11.0) like Gecko"; webRequest.UseDefaultCredentials = false; webRequest.Credentials = CredentialCache.DefaultCredentials; // Use t-zefu credential string text = string.Empty; WebResponse response = webRequest.GetResponse(); Stream responseStream = response.GetResponseStream(); StreamReader streamReader = new StreamReader(responseStream); text = streamReader.ReadToEnd(); response.Dispose(); responseStream.Close(); responseStream.Dispose(); // Use Newtonsoft to get the target data from json JObject obj = JObject.Parse(text); JObject ServiceRequest = (JObject)obj["Data"]["ServiceRequestResponseData"]["ServiceRequest"]; JObject EmailContact = (JObject)obj["Data"]["ServiceRequestResponseData"]["Contacts"]; IList <JToken> emailList = EmailContact["SRContacts"].Children().ToList(); // Fill out the list item with the data item["Case Status"] = (string)ServiceRequest["StatusName"]; item["Customer Company Name"] = (string)ServiceRequest["AccountIdName"]; item["Customer Name"] = (string)ServiceRequest["CurrentAuthorizedContactIdName"]; item["Customer Contact Email"] = (string)emailList[0]["PrimaryEmail"]; item["Premier/BC"] = (string)ServiceRequest["ServiceLevelName"]; item["Owner Name"] = (string)ServiceRequest["OwnerUserName"]; item["TAM Name"] = (string)ServiceRequest["PrimaryAccountManagerIdName"]; item["Calling Country"] = (string)ServiceRequest["CallingCountryCode"]; item["Contract Country"] = (string)ServiceRequest["ContractCountryIdName"]; item["Active"] = "0"; using (SqlConnection con = new SqlConnection(@"Server=detego-ctssql;Database=ssCTSDataMart;Integrated Security=True;")) { using (SqlCommand cmd = new SqlCommand("select distinct EmployeeEmail, workgroup from vwDimEmployee where iscurrent = 'yes' and EmployeeEmail = 'richen'", con)) { cmd.CommandTimeout = 2000; con.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string fields = reader["workgroup"].ToString(); int i = 0; } } } } if (item != null) { item.Update(); } } catch { //errorMessage is not used, just in case of invalid case ID string errorMessage = "case Id do not exist! or something else led to failure of getting other info"; } } } } }
internal static IEnumerable <T> ToEntities <T>(SPListItemCollection items) where T : Item { return(items.Cast <SPListItem>().Select(i => ToEntity <T>(i))); }
public Hashtable UpdateDueDates(SPUserCodeWorkflowContext context, string calendarName) { Hashtable results = new Hashtable(); results["result"] = string.Empty; try { using (SPSite site = new SPSite(context.CurrentWebUrl)) { using (SPWeb web = site.OpenWeb()) { SPList requirementsList = web.Lists["Requirements"]; SPList calendarList = web.Lists[calendarName]; if (requirementsList != null && calendarList != null) { using (DisabledItemEventsScope scope = new DisabledItemEventsScope()) { SPListItemCollection items = requirementsList.Items; foreach (SPListItem item in items) { int id = int.Parse(item["ID"].ToString()); if (id > 170 && id < 191) //temporary for tests only { if (item["Type of Due Date"] != null) { if (item["Type of Due Date"].ToString().Contains("Monthly")) { UpdateDate(item, "January", calendarList); UpdateDate(item, "February", calendarList); UpdateDate(item, "March", calendarList); UpdateDate(item, "April", calendarList); UpdateDate(item, "May", calendarList); UpdateDate(item, "June", calendarList); UpdateDate(item, "July", calendarList); UpdateDate(item, "August", calendarList); UpdateDate(item, "September", calendarList); UpdateDate(item, "October", calendarList); UpdateDate(item, "November", calendarList); UpdateDate(item, "December", calendarList); } else if (item["Type of Due Date"].ToString().Contains("Quarterly")) { UpdateDate(item, "1st Quarter", calendarList); UpdateDate(item, "2nd Quarter", calendarList); UpdateDate(item, "3rd Quarter", calendarList); UpdateDate(item, "4th Quarter", calendarList); } else if (item["Type of Due Date"].ToString().Contains("Semi-Annual")) { UpdateDate(item, "1st Semi-Annual", calendarList); UpdateDate(item, "2nd Semi-Annual", calendarList); } else if (item["Type of Due Date"].ToString().Contains("Annual")) { UpdateDate(item, "Annual", calendarList); } } } } } } } } results["success"] = true; } catch (Exception e) { results = new Hashtable(); results["result"] = e.ToString(); results["success"] = false; } return(results); }
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 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 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]; 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", "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, total_time = grp.Sum(r => r.Field <decimal>("total_time")) / 8 }); DataTable pvtable = qyery.CopyToDataTable(); DataTable resulttbl = new DataTable(); resulttbl = Pivot(pvtable, "ResUID", "costcode", "total_time"); 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["ResourceName"].SetOrdinal(1); resulttbl.Columns["HRISID"].SetOrdinal(2); resulttbl.Columns["OLDEMPID"].SetOrdinal(3); resulttbl.Columns["PayRolStDate"].SetOrdinal(4); resulttbl.Columns["PayRolEntity"].SetOrdinal(5); resulttbl.Columns["staftype"].SetOrdinal(6); if (rc_code_table.Rows.Count > 0) { int columnindex = 7; 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++); } } DataView resdetailview = new DataView(maintbl); DataTable resdetailtable = resdetailview.ToTable(true, "ResUID", "ResourceName", "HRISID", "OLDEMPID", "PayRolStDate", "PayRolEntity", "staftype"); foreach (DataRow ro in resulttbl.Rows) { foreach (DataRow mtlrow in resdetailtable.Select("ResUID = '" + ro["ResUID"] + "'")) { 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"]; } } #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++) { if (i > 6) { columnheaderline = columnheaderline + resulttbl.Columns[i].ColumnName + "(days),"; } else { 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 > 6) { if (row[i].ToString() != string.Empty) { columnvalue = columnvalue + row[i] + ","; } else { columnvalue = columnvalue + "0,"; } } else { columnvalue = columnvalue + row[i] + ","; } } writer.WriteLine(columnvalue); } writer.Flush(); writer.Close(); writer.Dispose(); // Sending files here Response.ContentType = "application/CSV"; Response.AddHeader("content-disposition", "attachment; filename=ManDayByProject" + clientfilename + ".csv"); Response.TransmitFile(filename); Response.Flush(); Response.End(); } catch (Exception) { } #endregion export function }); }
private void UpdateStatisticsList(SPWeb currentWeb, HttpContext ctx) { var cLoginName = string.Empty; var cDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now); var cURL = ctx.Request.Url.AbsoluteUri.ToString(); if (currentWeb.CurrentUser == null || string.IsNullOrEmpty(currentWeb.CurrentUser.LoginName)) { cLoginName = "Annonymous"; } else { cLoginName = currentWeb.CurrentUser.LoginName; } var cIP = ctx.Request.UserHostAddress; var cBrowser = ctx.Request.Browser.Browser; var camlQuery = "<Where><And><Eq>" + "<FieldRef Name='Url' />" + "<Value Type='Text'>" + cURL + "</Value></Eq>" + "<And><Eq><FieldRef Name='Title' />" + "<Value Type='Text'>" + cLoginName + "</Value></Eq>" + "<And><Eq><FieldRef Name='DateHit' /><Value Type='DateTime' IncludeTime='FALSE'>" + cDate + "</Value></Eq>" + "<And><Eq><FieldRef Name='Browser' /><Value Type='Text'>" + cBrowser + "</Value></Eq><Eq><FieldRef Name='IP' /><Value Type='Text'>" + cIP + "</Value></Eq>" + "</And></And></And></And></Where>"; SPSecurity.RunWithElevatedPrivileges(() => { using (var site = new SPSite(currentWeb.Site.ID)) { using (var web = site.OpenWeb(currentWeb.ID)) { try { SPQuery spQuery = new SPQuery { Query = camlQuery, RowLimit = 1 }; SPList list = Utilities.GetListFromUrl(web, ListsName.English.StatisticsList); if (list != null) { SPListItemCollection items = list.GetItems(spQuery); if (items == null || items.Count <= 0) { var item = list.Items.Add(); item["Title"] = cLoginName; item["Url"] = cURL; item["IP"] = cIP; item["DateHit"] = DateTime.Now; item["Browser"] = cBrowser; web.AllowUnsafeUpdates = true; item.Update(); } } } catch (Exception ex) { } } } }); }
/// <summary> /// An item was updated. /// </summary> public override void ItemUpdated(SPItemEventProperties properties) { base.ItemUpdated(properties); //Ausführen des folgenden Codes nur, wenn ein PDF vorhanden ist if (properties.ListItem.Name.Contains(".pdf")) { //Durch folgende Zeile wird der enthaltene Code als SHAREPOINT\System ausgeführt und nicht unter dem Kontext des Benutzers, der den Auftrag unterschrieben hat SPSecurity.RunWithElevatedPrivileges(delegate() { //Laden des aktuellen Webs; das ist nötig, um alle möglichen Operationen mit Bibliotheken und Elementen auszuführen //dieses wird wieder geschlossen bzw. freigegeben, wenn die geschweifte Klammer von "using" geschlossen wird using (SPSite site = new SPSite(properties.WebUrl)) { using (SPWeb web = site.OpenWeb()) { //Festlegen von Variablen //Initialisieren der "Helper"-Klasse. Diese enthält selbst geschriebene Methoden, die hier aufgerufen werden. Helper helper = new Helper(); //Laden des aktuell geänderten bzw. unterschrieben Elements SPListItem _currentItem = web.Lists[properties.ListId].GetItemById(properties.ListItemId); //Abspeichern von Spaltenwerten des Elements in Variablen; der String in den eckigen Klammern ist der Spaltenname string auftragsnummer = _currentItem["Auftragsnummer"].ToString(); string bestellmethode = _currentItem["Bestellmethode"].ToString(); SPFieldBoolean DrittlandBoolField = _currentItem.Fields["Drittland"] as SPFieldBoolean; bool Drittland = (bool)DrittlandBoolField.GetFieldValue(_currentItem["Drittland"].ToString()); string DocID = _currentItem.Properties["_dlc_DocId"].ToString(); //Die Summe muss in ein double-Wert umgewandelt werden, damit er später richtig verwendet werden kann double summe = Convert.ToDouble(_currentItem["Summe"]); //Laden der Bibliothek "Temp" und des Unterordners, welcher als Namen die Auftragsnummer hat SPList tempbibliothek = web.Lists["Temp"]; var tempfolder = web.GetFolder(String.Format("{0}/Temp/" + auftragsnummer, web.Url)); //Angabe des Namens der Bibliothek, in der die Begründungen gespeichert werden, und des Dateinamens der Begründungen //string begruendungbibliothek = "Begruendungen"; string begruendungbibliothek = "Begründungen"; string begruendungdateiname = auftragsnummer + "_InterneBegruendung.pdf"; string auftragdesturl = tempbibliothek.RootFolder.SubFolders[auftragsnummer].Url + "/" + auftragsnummer + ".pdf"; string begruendungdesturl = tempbibliothek.RootFolder.SubFolders[auftragsnummer].Url + "/" + begruendungdateiname; //Angabe des Namens der Bibliothek der Auftragsformulare und deren Dateinamen string formularbibliothek = "Auftragsformular"; string formulardateiname = auftragsnummer + ".xml"; string xanhang = "my:Anhang"; string xweitergabe = "my:Weitergabe"; //Angeben des Pfades zum Ablegen des zusammengefassten PDFs für den Druck für die VW //Bei Pfadangaben mit Backslashes muss entweder vor dem Anführungszeichen ein @ geschrieben werden ODER doppelte Backslashes verwendet werden (z.B. "C:\\ipm-int\\Bestellungen\\") string pdfpath = @"C:\Bestellung\PrintTemp\PDF\"; //Angeben des Druckers über den gedruckt wird; hier ist einfach der Freigabename anzugeben //string printer = @"\\ipm-int\Bestellungen\"; string printer = @"\\printserver\A-3-31-SW-Buero\"; //Definieren einer vorerst leeren String-Variable string davorsigniert = ""; //Wenn die Spalte "SignaturePreviousSigned" nicht leer ist, wird der enthaltene Wert der Variable "davorsigniert" zugwiesen if (_currentItem["SignaturePreviousSigned"] != null) { davorsigniert = _currentItem["SignaturePreviousSigned"].ToString(); } //Auslesen der Empfänger-Adressen aus den SharePoint-Gruppen string rcptFaxPostalOrders = ""; string spGroupRcptFaxPostalFiles = "Empfänger der Fax - und Postbestellungen"; foreach (SPUser user in web.SiteGroups[spGroupRcptFaxPostalFiles].Users) { rcptFaxPostalOrders += user.Email.ToString() + ";"; } Console.WriteLine(rcptFaxPostalOrders); //Die Drittlands-Mail inkl. Anlagen soll seit 13.02.2019 laut Verwaltung nur noch an das Einkaufs-Postfach gehen. string rcptThirdCountryOrders = ""; string spGroupRcptCustomFiles = "Empfänger der Drittlandsbestellungen"; foreach (SPUser user in web.SiteGroups[spGroupRcptCustomFiles].Users) { rcptThirdCountryOrders += user.Email.ToString() + ";"; } string rcptBbc = ""; string spGroupRcptBbc = "E-Mails der Ereignisempfänger (BBC) "; foreach (SPUser user in web.SiteGroups[spGroupRcptBbc].Users) { rcptBbc += user.Email.ToString() + ";"; } //Erstellen der byte-Arrays für die PDF-Dateien die durch Mergen/Zusammenfügen erstellt werden; eins für den Empfang zum faxen, das andere für den Ausdruck für die Verwaltung byte[] mergeresultbyte = new byte[0]; byte[] mergeresultbytevw = new byte[0]; //Festlegen des Dateinamens unter dem das PDF für den Ausdurck für die Verwaltung im lokalen Dateisystem abgespeichert wird. string mergefilenamevw = auftragsnummer + "_vw.pdf"; //Laden des gerade signierten PDFs; zuerst wird es als SharePoint-Datei (SPFile) geladen, dann als Byte-Array. SPFile auftragszettel = _currentItem.File; byte[] auftragszettelbyte = auftragszettel.OpenBinary(); //Initialisieren eines PDFReaders (von itextsharp) mit Verweis auf das eben geöffnete Byte-Array; der PDF-Reader kann PDF-Dateien nur lesen using (PdfReader pdfreader = new PdfReader(auftragszettelbyte)) { //Laden der vorhandenen PDF-Formularfelder AcroFields fields = pdfreader.AcroFields; //Speichern der Namen aller signierten Felder in disem PDF in Liste "signatures". Die zu signierenden Felder heißen "Projektleiter" und "Verwaltung"; demnach kann dieser Wert leer, "Projektleiter" oder "ProjektleiterVerwaltung" sein List <string> signatures = pdfreader.AcroFields.GetSignatureNames(); //Die gerade gespeicherten Feldnamen werden mit Komma getrennt (wenn mehrere vorhanden sind) und in eine neue String-Variable gespeichert. Somit wird aus "ProjektleiterVerwaltung" > "Projektleiter,Verwaltung" string signiert = string.Join(",", signatures.ToArray()); //Anhand dieser IF-Abfrage wird geprüft ob der Auftag überhaupt signiert wurde. Es wird der Spaltenwert ("SignaturePreviousSigned") mit den im PDF tatsächlich signierten Feldern verglichen if (signiert != davorsigniert) { //Starten des SharePoint-Designer-Workflows "Unterschriftenlauf" mit Parameter "signedfields". Mit diesem Parameter werden die Namen der Signierten Felder direkt als String mitgegeben. SPWorkflowAssociationCollection associationCollection = _currentItem.ParentList.WorkflowAssociations; foreach (SPWorkflowAssociation association in associationCollection) { if (association.Name == "Unterschriftenlauf") { association.AutoStartChange = true; association.AutoStartCreate = false; association.AssociationData = "<Data><Signiert>" + signiert + "</Signiert></Data>"; //web.Site.WorkflowManager.StartWorkflow(_currentItem, association, association.AssociationData); site.WorkflowManager.StartWorkflow(_currentItem, association, association.AssociationData); } } //Der SharePoint-Designer-Workflow geht nun alle vorhandenen Fälle durch und versendet die nötigen Benachrichtigungen und ändert das Status-Feld des Auftrags //Im Code muss nun nur noch der Fall beachtet werden, wenn Projektleiter und Verwaltung unterschrieben haben //In diesem Fall muss ein PDF für die Verwaltung und eins (nur wenn nötig) für den Empfang erstellt werden. Letzteres wird benötigt, um dem Empfang dieses per Mail zu versenden, damit es gefaxt oder per Post versendet werden kann. //Das ist nur nötig, wenn die Bestellmethode NICHT "online" oder "Abholung" ist //Zu aller erst wird überprüft ob der beschriebene Fall eintritt: Die Variable "signiert" enthält alle Namen der Signaturfelder aus dem PDf, die unterschrieben sind. if ((signiert == "Projektleiter,Verwaltung") || (signiert == "Verwaltung,Projektleiter")) { string tempuploadurl = helper.TempOrdner(tempbibliothek, tempfolder, auftragsnummer); //Laden des Auftragsformulars byte[] xmlfile = helper.GetFileFromWeb(formularbibliothek, formulardateiname, web); Stream xmlmemstr = new MemoryStream(xmlfile); XmlDocument xmldoc = new XmlDocument(); xmldoc.Load(xmlmemstr); XPathNavigator root = xmldoc.CreateNavigator(); XmlNamespaceManager nsmgr = new XmlNamespaceManager(new NameTable()); //Angeben des NameSpaces: Die Werte werden direkt aus einem beispiel Formular entnommen (stehen am Anfang der XML-Datei) nsmgr.AddNamespace("my", "http://schemas.microsoft.com/office/infopath/2003/myXSD/2016-04-22T15:49:19"); helper.AnlagenHochladen(root.Select("/my:meineFelder/my:Anlagen/my:Anlage", nsmgr), xanhang, xweitergabe, nsmgr, DocID, tempfolder, tempuploadurl, web); //Hochladen der Begründung und des aktuellen Auftragzettels in den Temp-Ordner //Begründung if (root.SelectSingleNode("/my:meineFelder/my:Begruendung", nsmgr).ToString() != "") { byte[] fattenedBegruendung = helper.flattenPdfForm(helper.GetFileFromWeb(begruendungbibliothek, begruendungdateiname, web)); SPFile copybegruendung = tempfolder.Files.Add(begruendungdesturl, fattenedBegruendung, true); SPListItem copybegruendungitem = copybegruendung.Item; copybegruendungitem["Auftragsnummer"] = auftragsnummer; //Übernehmen der Änderung. Durch "SystemUpdate" wird diese Änderung nicht in SharePoint selbst dokumentiert (in Spalten "geändert von" und "geändert"). copybegruendungitem.SystemUpdate(); } //Auftragszettel byte[] flattendAuftrag = helper.flattenPdfForm(auftragszettelbyte); SPFile copyauftrag = tempfolder.Files.Add(auftragdesturl, flattendAuftrag, true); //Setzen des Spaltenwerts "Weitergabe" auf true, da der Lieferant den Auftragszettel immer erhalten muss. Wäre dieser WErt auf "false" würde Auftragszettel beim PDf für Fax oder Post fehlen. Der Wert ist in der SharePoint-Bibliothek "Temp" standardmäßig auf "false" gesetzt. SPListItem copyauftragitem = copyauftrag.Item; copyauftragitem["Weitergabe"] = true; copyauftragitem.SystemUpdate(); //diese IF-Abfrage ist nur reine Sicherheitsmaßnahme: Es wird geprüft ob im Temp-Ordner auch Dateien vorhanden sind. Da der Auftrag direkt davor in diesen Ordner kopiert wird sollte diese Bedingung immer zutreffen. if (tempfolder.ItemCount > 0) { //Zusammenführen aller PDF-Dokumente für die Verwaltung //SPListItemCollection ist eine Sammlung von SharePoint-Elementen. Welche Elemente ausgewählt werden, ist in der Helper-Methode (ganz unten) nach zu lesen. Mit dem Übergabewert "false" wird bewirkt, dass nicht beachtet wird, welche Anlagen zur Weitergabe an den Lieferanten gewählt sind - es wird alles einfach gedruckt. SPListItemCollection pdfcollection = helper.CollectPDF(tempfolder, tempbibliothek, false); //Die zuvor erstellte Sammlung von Elementen ("pdfcollection") wird nun in einer weiteren Helper-Methode verwendet, um die PDf-Dateien zu kombinieren. Mit dem Übergabewert "false" wird bewirkt, dass die AEB's NICHT mit ausgedruckt werden. mergeresultbytevw = helper.Merge(pdfcollection, false, auftragsnummer); //Zusammenführen der PDf-Dokumente für den Lieferanten //Gemerged wird nur, wenn über Fax oder Post bestellt wird, damit die Zentrale eine Datei hat, die sie versenden/ausdrucken können. Wenn "online" oder per "Abholung" bestellt wird, oder wenn es sich um ein Drittland handelt, findet dieser Vorgang nicht statt. if ((bestellmethode.Contains("online") == false) && (bestellmethode.Contains("Abholung") == false) && Drittland == false) { //Sammeln der Elemente; diesmal mit dem Übergabewert "true", damit nur Anlagen berücksichtigt werden, die auch zur Weitergabe für den Lieferanten ausgewählt sind pdfcollection = helper.CollectPDF(tempfolder, tempbibliothek, true); //Zusammenführen der gerade gesammelten Elemente; diesmal mit dem Übergabewert "true", damit die AEBs erhalten bleiben mergeresultbyte = helper.Merge(pdfcollection, true, auftragsnummer); //Kopieren des PDF in einen lokalen Ordner auf dem SharePoint-Server File.WriteAllBytes(pdfpath + auftragsnummer + "_Kopie.pdf", mergeresultbyte); string faxnummer = "<br>"; if ((bestellmethode == "Fax") && (_currentItem["Fax"] != null)) { faxnummer = "Fax: " + _currentItem["Fax"].ToString() + "<br><br>"; } string zentraletext = "<div style='font-family: Frutiger LT COM 45 Light; font-size: 11pt;'>Guten Tag,<br><br>" + "der Auftrag #" + auftragsnummer + " wurde vom Projektleiter und der Verwaltung signiert. Als Bestellmethode wurde " + bestellmethode + " gewählt. " + "Der Auftrag ist im Anhang enthalten. Hierbei handelt es sich nur um eine Kopie für den Lieferanten.<br><br>" + "Name Besteller: " + helper.Anzeigename(_currentItem, "Profil:NameBesteller") + "<br>" + "Projektleiter: " + helper.Anzeigename(_currentItem, "Profil:Projektleiter") + "<br>" + "Firma: " + _currentItem["Firma"].ToString() + "<br>" + faxnummer + "Dies ist eine automatische Benachrichtigung. Bitte antworten Sie nicht auf diese E-Mail."; helper.Mail("Bestellung", rcptFaxPostalOrders, rcptBbc, "Auftrag #" + auftragsnummer + " - Signaturvorgang abgeschlossen", zentraletext, mergeresultbyte, auftragsnummer, site); } } else { mergeresultbytevw = auftragszettelbyte; //Auch Dieser Else-Block ist nur reine Sicherheitsmaßnahme. } //Kopieren des PDFs für VW in einen lokalen Ordner (siehe Variable "printpath") auf dem SharePoint-Server. File.WriteAllBytes(pdfpath + mergefilenamevw, mergeresultbytevw); //Hochladen des PDFs für VW in Ordner "Print" in Bibliothek "Temp" //desturl = tempbibliothek.RootFolder.SubFolders["Merged"].Url + "/" + mergefilenamevw; //SPFile uploadmerged = tempfolder.Files.Add(desturl, File.ReadAllBytes(pdfpath + mergefilenamevw), true); //Schicken der PDF-Datei für VW an den Drucker (siehe Variable "printer") if (Drittland == false) { File.Copy(pdfpath + mergefilenamevw, printer + mergefilenamevw); } else { string drittlandtext = "<div style='font-family: Frutiger LT COM 45 Light; font-size: 11pt;'>Guten Tag,<br><br>" + "der Signaturvorang für den Auftrag (#" + auftragsnummer + ") wurde abgeschlossen. Da es sich um eine Bestellung aus dem <b>Drittland</b> handelt, wird der Auftrag <b>ohne automatischen Ausdruck</b> an den Einkauf weitergeleitet. " + "Der Auftrag ist im Anhang enthalten.<br><br>" + "Name Besteller: " + helper.Anzeigename(_currentItem, "Profil:NameBesteller") + "<br>" + "Projektleiter: " + helper.Anzeigename(_currentItem, "Profil:Projektleiter") + "<br>" + "Firma: " + _currentItem["Firma"].ToString() + "<br>" + "Bestellmethode: " + bestellmethode + "<br><br>" + "Dies ist eine automatische Benachrichtigung. Bitte antworten Sie nicht auf diese E-Mail."; helper.Mail("Bestellung", rcptThirdCountryOrders, rcptBbc, "Drittland: Auftrag #" + auftragsnummer + " - Signaturvorgang abgeschlossen", drittlandtext, mergeresultbytevw, auftragsnummer, site); } //Löschen des PDFs für VW aus lokalem Ordner - NICHT MEHR NÖTIG, da der Temp-Ordner wöchentlich aufgeräumt wird. Deßhalb können beide Dateien aus Troubleshooting-Gründen aufgehoben werden. //File.Delete(pdfpath + mergefilenamevw); //Löschen des temporären Ordners in Bibliothek "Temp" tempfolder.Delete(); } } } } } }); } }
public static List<Model.Entities.NewsItem> GetNewsByCategory(SPList list, int itemCount, string pagingInfo, out SPListItemCollection collection) { CAMLListQuery<NewsItem> query = new CAMLListQuery<NewsItem>(list); string caml = string.Empty; caml = Camlex.Query() .Where(x => x[SPBuiltInFieldId._ModerationStatus] == (DataTypes.ModStat)"0") .OrderBy(x => x[SPBuiltInFieldId.Created] as Camlex.Desc) .ToString(); return query.ExecuteListQuery(caml, itemCount, pagingInfo, out collection); }
protected override IEnumerable ExecuteSelect(DataSourceSelectArguments selectArgs) { // only continue if a membership provider has been configured if (!Utils.IsProviderConfigured()) { return(null); } // get site details SPSite site = SPContext.Current.Site; string provider = Utils.GetMembershipProvider(site); if (provider == null) { return(null); } SPWeb web = site.RootWeb; string yes = LocalizedString.GetString("FBAPackFeatures", "Yes"); string no = LocalizedString.GetString("FBAPackFeatures", "No"); string cacheKey = String.Format("Visigo.SharePoint.FormsBasedAuthentication.FBAUsersView.{0}", provider); Dictionary <string, SPListItem> spUsers = _cache.Get(cacheKey) as Dictionary <string, SPListItem>; //Reload site user info list or grab from cache if (_owner.ResetCache || spUsers == null) { spUsers = new Dictionary <string, SPListItem>(); // we only display users that have been added to SharePoint // we use the localized name, safe for non-English SharePoint servers SPList list = web.SiteUserInfoList; //web.Lists[SPUtility.GetLocalizedString("$Resources:userinfo_schema_listtitle", "core", web.Language)]; // create query list SPQuery query = new SPQuery(); query.Query = string.Format( "<Where>" + "<And>" + "<Eq><FieldRef Name='ContentType' /><Value Type='Text'>Person</Value></Eq>" + "<Contains><FieldRef Name='Name' /><Value Type='Text'>{0}</Value></Contains>" + "</And>" + "</Where>", provider); query.ViewFields = "<FieldRef Name='Name' /><FieldRef Name='LinkTitle' /><FieldRef Name='Email' /><FieldRef Name='Modified' /><FieldRef Name='Created' />"; query.RowLimit = 100000; //Convert SPListItemCollection to dictionary for fast lookup try { SPListItemCollection userList = list.GetItems(query); if (userList != null) { foreach (SPListItem item in userList) { string username = item["Name"] as string; string decodedName = Utils.DecodeUsername(username); if (username != decodedName) { spUsers.Add(decodedName, item); } } } } catch (Exception ex) { Utils.LogError(ex); return(null); } _cache.Add(cacheKey, spUsers, null, DateTime.UtcNow.AddMinutes(1.0), System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.Normal, null); } //Create a datatable for returning the results DataTable users = new DataTable(); users.Columns.Add("Title"); users.Columns.Add("Name"); users.Columns.Add("Email"); users.Columns.Add("Modified", typeof(DateTime)); users.Columns.Add("Created", typeof(DateTime)); users.Columns.Add("Active"); users.Columns.Add("Locked"); users.Columns.Add("LastLogin", typeof(DateTime)); users.Columns.Add("IsInSharePoint"); int totalRecords = 0; int spUsersCount = spUsers.Count; int spUsersFound = 0; users.BeginLoadData(); //Add all membership users to the datatable foreach (MembershipUser memberuser in Utils.BaseMembershipProvider(site).GetAllUsers(0, 100000, out totalRecords)) { string title = null; string email = memberuser.Email; DateTime?modified = null; DateTime?created = null; string isInSharepoint = no; SPListItem spUser = null; //See if there is a matching sharepoint user - if so grab the values if (spUsersFound < spUsersCount) { if (spUsers.TryGetValue(memberuser.UserName.ToLower(), out spUser)) { spUsersFound++; title = spUser["Title"] as string; created = spUser["Created"] as DateTime?; modified = spUser["Modified"] as DateTime?; isInSharepoint = yes; //Make sure the SharePoint email field has a value before copying it over string spEmail = spUser["EMail"] as string; if (!String.IsNullOrEmpty(spEmail)) { email = spEmail; } } } //Add the matched up membership + sharepoint data to the datatable users.LoadDataRow(new object[] { title, memberuser.UserName, email, modified, created, memberuser.IsApproved ? yes : no, memberuser.IsLockedOut ? yes : no, memberuser.LastLoginDate, isInSharepoint }, false); } users.EndLoadData(); // sort if a sort expression available DataView dataView = new DataView(users); if (selectArgs.SortExpression != String.Empty) { dataView.Sort = selectArgs.SortExpression; } //Filter the data if a filter is provided if (_owner.SearchText.Length > 0) { dataView.RowFilter = string.Format("Name LIKE '%{0}%' OR Email LIKE '%{0}%' OR Title LIKE '%{0}%'", _owner.SearchText); } else { dataView.RowFilter = ""; } // return as a DataList return((IEnumerable)dataView); }
public static DataTable GetDataTable(SPListItemCollection items, IEnumerable<string> fields) { var dt = new DataTable(); Hashtable hashtable = null; foreach (SPListItem item in items) { if (hashtable == null) { hashtable = new Hashtable(); foreach (var fieldName in fields) { var field = item.Fields.GetFieldByInternalName(fieldName); hashtable.Add(fieldName, field); dt.Columns.Add(fieldName, typeof (object)); } } var row = dt.NewRow(); foreach (var fieldName in fields) { var field = (SPField) hashtable[fieldName]; var value = item[field.Title]; if (value != null) { switch (field.Type) { case SPFieldType.User: if (value is SPFieldUserValueCollection) { var users = (SPFieldUserValueCollection) value; row[fieldName] = string.Join(";#", users.Select( user => user.LookupId + ";#" + user.LookupValue). ToArray()); } else { row[fieldName] = value; } break; case SPFieldType.Calculated: var calculatedField = (SPFieldCalculated) field; var split = value.ToString().Split(new[] {";#"}, StringSplitOptions.None); try { switch (calculatedField.OutputType) { case SPFieldType.Number: case SPFieldType.Currency: row[fieldName] = Convert.ToDouble(split[1], CultureInfo.InvariantCulture); break; case SPFieldType.DateTime: row[fieldName] = Convert.ToDateTime(split[1], CultureInfo.InvariantCulture); break; case SPFieldType.Boolean: row[fieldName] = split[1] == "1"; break; default: row[fieldName] = split[1]; break; } } catch (FormatException) { row[fieldName] = DBNull.Value; } break; default: row[fieldName] = value; break; } } } dt.Rows.Add(row); } return dt; }
/// <summary> /// Initializes a new instance of the <see cref="RecordDocument"/> class. /// </summary> /// <param name="enabledLibraries">The enabled libraries.</param> /// <param name="dropOffListItem">The file sp list item.</param> /// <param name="routingRules">The routing rules.</param> public RecordDocument(List <SPList> enabledLibraries, SPListItem dropOffListItem, SPListItemCollection routingRules) { if (enabledLibraries == null) { throw new ArgumentNullException("enabledLibraries"); } if (dropOffListItem == null) { throw new ArgumentNullException("dropOffListItem"); } if (routingRules == null) { throw new ArgumentNullException("routingRules"); } UnifiedLoggingServer.LogMedium("--- " + this.GetType().Name + " 3@ ---"); UnifiedLoggingServer.LogMedium("-@1:" + enabledLibraries.Count); UnifiedLoggingServer.LogMedium("-@2:" + dropOffListItem.Name); UnifiedLoggingServer.LogMedium("-@3:" + routingRules.Count); this.ListItem = dropOffListItem; this.File = dropOffListItem.File; this.FieldCollection = dropOffListItem.Fields; this.ContentType = dropOffListItem.ContentType; this.ParentContentType = this.GetParentContentType(dropOffListItem.ContentType); this.HasRoutingRule = this.ValidateRoutingRule(routingRules); this.HasParentRoutingRule = this.ScreenForParentRoutingRule(routingRules); this.RoutingRule = this.GetRoutingRule(routingRules); this.ParentRoutingRule = this.GetParentRoutingRule(routingRules); this.HasLibrary = this.ValidateLibrary(enabledLibraries); this.HasParentLibrary = this.ValidateParentLibrary(enabledLibraries); this.CandidateLibrary = this.ScreenForLibrary(enabledLibraries); this.ParentCandidateLibrary = this.ScreenForParentLibrary(enabledLibraries); this.ScreenMetadata(dropOffListItem.Properties); }
private void fillDropDowns() { try { using (SPSite spSite = new SPSite(SPContext.Current.Web.Url)) { using (SPWeb spWeb = spSite.OpenWeb()) { SPList spList = spWeb.Lists["Department"]; SPQuery spQuery = new SPQuery(); spQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ID' />"; spQuery.ViewFieldsOnly = true; spQuery.Query = "<OrderBy><FieldRef Name='Title' /></OrderBy>"; DataTable dt = spList.GetItems(spQuery).GetDataTable(); DataView dv = new DataView(dt); dt = dv.ToTable(true, "Title"); this.ddlAuditorDepartment.DataSource = dt; this.ddlAuditorDepartment.DataTextField = "Title"; this.ddlAuditorDepartment.DataValueField = "Title"; this.ddlAuditorDepartment.DataBind(); this.ddlAuditorDepartment.Items.Insert(0, new ListItem("Please Select", "0")); spList = spWeb.Lists["Section"]; SPListItemCollection listtItemCollec = spList.GetItems(spQuery); this.ddlAuditorSection.DataSource = listtItemCollec; this.ddlAuditorSection.DataTextField = "Title"; this.ddlAuditorSection.DataValueField = "ID"; this.ddlAuditorSection.DataBind(); this.ddlAuditorSection.Items.Insert(0, new ListItem("Please Select", "0")); spList = spWeb.Lists["Area"]; listtItemCollec = null; listtItemCollec = spList.GetItems(spQuery); this.ddlAreaToBeAudited_WI.DataSource = listtItemCollec; this.ddlAreaToBeAudited_WI.DataTextField = "Title"; this.ddlAreaToBeAudited_WI.DataValueField = "ID"; this.ddlAreaToBeAudited_WI.DataBind(); this.ddlAreaToBeAudited_WI.Items.Insert(0, new ListItem("Please Select", "0")); this.ddlAreaToBeAudited_WII.DataSource = listtItemCollec; this.ddlAreaToBeAudited_WII.DataTextField = "Title"; this.ddlAreaToBeAudited_WII.DataValueField = "ID"; this.ddlAreaToBeAudited_WII.DataBind(); this.ddlAreaToBeAudited_WII.Items.Insert(0, new ListItem("Please Select", "0")); this.ddlAreaToBeAudited_WIII.DataSource = listtItemCollec; this.ddlAreaToBeAudited_WIII.DataTextField = "Title"; this.ddlAreaToBeAudited_WIII.DataValueField = "ID"; this.ddlAreaToBeAudited_WIII.DataBind(); this.ddlAreaToBeAudited_WIII.Items.Insert(0, new ListItem("Please Select", "0")); this.ddlAreaToBeAudited_WIV.DataSource = listtItemCollec; this.ddlAreaToBeAudited_WIV.DataTextField = "Title"; this.ddlAreaToBeAudited_WIV.DataValueField = "ID"; this.ddlAreaToBeAudited_WIV.DataBind(); this.ddlAreaToBeAudited_WIV.Items.Insert(0, new ListItem("Please Select", "0")); } } } catch (Exception ex) { SPDiagnosticsService.Local.WriteTrace(0, new SPDiagnosticsCategory("SL.FG.FFL-Add)", TraceSeverity.Unexpected, EventSeverity.Error), TraceSeverity.Unexpected, ex.Message, ex.StackTrace); message_div.InnerHtml = "Something went wrong!!! Please contact the administrator at email address - [email protected]."; } }
///// <summary> ///// Fills the data table. ///// </summary> ///// <param name="web">The web.</param> ///// <param name="list">The list.</param> //private void BuildDataTable(SPWeb web, SPList list) //{ // Debug.Assert(list != null); // try // { // DataTable dataTable = _dataSet.Tables[0]; // // CAML Query // SPListItemCollection listItems = GetListItems(list); // if (listItems != null) // { // foreach (SPListItem item in listItems) // { // if (MaxRecordsNotExceed()) // { // break; // } // try // { // #region Fields // foreach (string fieldName in _fieldsList) // { // try // { // if (item.Fields.ContainsField(fieldName)) // { // EnsureTableColumn(item.Fields.GetField(fieldName), dataTable); // } // } // catch (ArgumentException ex) // { // SendError(new SPSErrorArgs("BuildDataTable", // "Field not found. " // + fieldName, // ex)); // } // } // #endregion // } // catch (ArgumentOutOfRangeException ex) // { // SendError(new SPSErrorArgs("BuildDataTable", // GetResourceString("SPS_Err_AddData"), // ex)); // } // } // } // } // catch (SPException ex) // { // SendError(new SPSErrorArgs("*BuildDataTable", GetResourceString("SPS_Err_AddData"), ex)); // } // catch (Exception ex) // { // SendError(new SPSErrorArgs("**Fill-Data", GetResourceString("SPS_Err_AddData"), ex)); // } //} ///// <summary> ///// Adds the field data. ///// </summary> ///// <param name="field">The field.</param> ///// <param name="table">The table.</param> //private static void EnsureTableColumn(SPField field, DataTable table) //{ // if (!table.Columns.Contains(field.InternalName)) // { // if (field.FieldValueType != null // && field.FieldValueType.BaseType.ToString() == "System") // { // table.Columns.Add(field.InternalName, field.FieldValueType); // } // else // { // table.Columns.Add(field.InternalName, typeof(string)); // } // } //} /// <summary> /// Fills the data table. /// </summary> /// <param name="web">The web.</param> /// <param name="list">The list.</param> private void FillDataTable(SPWeb web, SPList list) { Debug.Assert(list != null); try { DataTable table = _dataSet.Tables[0]; // CAML Query SPListItemCollection items = GetListItems(list); if (items != null) { foreach (SPListItem item in items) { if (MaxRecordsNotExceed()) { DataRow dataRow = table.NewRow(); dataRow["_RowNumber"] = _rowNumber++; if (_includeListData) { AddListData(dataRow, web, list, item); } try { #region Fields foreach (string fieldName in _fieldsList) { AddFieldData(dataRow, item, fieldName); } #endregion } catch (ArgumentOutOfRangeException ex) { SendError(new SPSErrorArgs(ex.TargetSite.Name, GetResourceString("SPS_Err_AddData"), ex)); } table.Rows.Add(dataRow); _recordCounter++; } } } else { Debug.WriteLine("No results in " + list.Title); } } catch (SPException ex) { SendError(new SPSErrorArgs(ex.TargetSite.Name, GetResourceString("SPS_Err_AddFieldContent"), ex)); } catch (Exception ex) { SendError(new SPSErrorArgs(ex.TargetSite.Name, GetResourceString("SPS_Err_AddFieldContent"), ex)); } }
/// <summary> /// /// </summary> /// <param name="items"></param> /// <param name="sapo"></param> /// <returns></returns> public static DataTable GetTableWithCorrectUrl(SPListItemCollection items, bool sapo) { var dataTable = items.GetDataTable(); if (items != null && items.Count > 0) { string imagepath = string.Empty; ImageFieldValue imageIcon; for (int i = 0; i < items.Count; i++) { imagepath = Convert.ToString(items[i][FieldsName.NewsRecord.English.ThumbnailImage]); imageIcon = items[i][FieldsName.NewsRecord.English.PublishingPageImage] as ImageFieldValue; dataTable.Rows[i][FieldsName.Title] = GetTextForSapo(Convert.ToString(items[i][FieldsName.Title]), 55); if (imageIcon != null) dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imageIcon.ImageUrl; else { if (imagepath.Length > 2) dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath.Trim().Substring(0, imagepath.Length - 2); else { dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath; } } } } return dataTable; }
/// <summary> /// 获取指定年度个人业绩汇总记录 /// </summary> /// <param name="year">年度</param> /// <param name="IdCard">工号</param> /// <returns></returns> private void GetPersonalPerformanceByYear(string IdCard, string year) { SPSecurity.RunWithElevatedPrivileges(delegate() { try { string siteUrl = SPContext.Current.Site.Url; if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["pfWebUrl"])) { siteUrl = ConfigurationManager.AppSettings["pfWebUrl"]; } using (SPSite spSite = new SPSite(siteUrl)) //找到网站集 { using (SPWeb spWeb = spSite.OpenWeb()) { SPList spList = spWeb.Lists.TryGetList(ConfigurationManager.AppSettings["ListName"]); if (spList != null) { lbTitle.Text = year + " 年度个人业绩汇总"; SPQuery qry = new SPQuery { RowLimit = 1, Query = @"<Where><And><Eq><FieldRef Name='IDCard' /><Value Type='Text'>" + IdCard + "</Value></Eq><Eq><FieldRef Name='Year' /><Value Type='Text'>" + year + "</Value></Eq></And></Where>" }; SPListItemCollection items = spList.GetItems(qry); if (items.Count > 0) { SPListItem item = items[0]; hdId.Value = item.ID.ToString(); hdIdCard.Value = item["工号"].ToString(); DisplayControl(); divPerformance.Visible = true; //校对状态 string flag = item["Flag"].ToString(); checkFlag(flag); #region 个人资料 lbName.Text = item["姓名"].ToString(); lbIDCard.Text = item["工号"].ToString(); lbProf.Text = item["职称"].ToString(); lbDept.Text = item["所属部门"].ToString(); #endregion #region 教学课时 #region 本科教学课时 lbgwjh_bk.Text = item["本科公外计划总学时"].ToString(); lbzyjh_bk.Text = item["本科专业计划总学时"].ToString(); lbgwzs_bk.Text = item["本科公外折算总学时"].ToString(); lbzyzs_bk.Text = item["本科专业折算总学时"].ToString(); lbzdshsj.Text = item["本科指导社会实践"].ToString(); lbzdbylw_bk.Text = item["本科指导毕业论文"].ToString(); lbxkk_bk.Text = item["本科新开课"].ToString(); #endregion #region 研究生教学课时 lbgwjh_yjs.Text = item["研究生公外计划总学时"].ToString(); lbzyjh_yjs.Text = item["研究生专业计划总学时"].ToString(); lbgwzs_yjs.Text = item["研究生公外折算总学时"].ToString(); lbzyzs_yjs.Text = item["研究生专业折算总学时"].ToString(); lbzdyjs.Text = item["指导研究生"].ToString(); lbzdbylw_yjs.Text = item["研究生指导毕业论文"].ToString(); lbyjspj.Text = item["研究生批卷"].ToString(); #endregion #region 课时汇总 lbgwjh.Text = item["公外计划总学时"].ToString(); lbzyjh.Text = item["专业计划总学时"].ToString(); lbjhw.Text = item["计划外总学时"].ToString(); #endregion #endregion #region 业绩点 lbjxlx.Text = item["教学立项"].ToString(); lbjc.Text = item["教材"].ToString(); lblw.Text = item["论文"].ToString(); lbyz.Text = item["译著"].ToString(); lbzz.Text = item["专著"].ToString(); lbkylx.Text = item["科研立项"].ToString(); lbkycg.Text = item["科研成果"].ToString(); lbjxhj.Text = item["教学获奖"].ToString(); lbjxjs.Text = item["教学竞赛"].ToString(); lbxzzw.Text = item["行政职务"].ToString(); lbxkjs.Text = item["学科建设"].ToString(); lbrcyj.Text = item["人才引进"].ToString(); lbxsjz.Text = item["学术兼职"].ToString(); lbjiafen.Text = item["加分"].ToString(); lbjianfen.Text = item["减分"].ToString(); #endregion //金额的字段 if (isCheck(ConfigurationManager.AppSettings["fscMoneyDate"])) { lbOne.Text = "¥ " + item["人头费"].ToString(); lbkszje.Text = "¥ " + item["课时总金额"]; lbyjdz.Text = "¥ " + item["业绩点总金额"]; lbqueqin.Text = "¥ " + item["缺勤"]; lbzjbt.Text = "¥ " + item["助教补贴"]; lbyjdhjz.Text = "¥ " + item["业绩点合计总金额"]; lbzong.Text = "¥ " + item["总金额(人头+课时+业绩点合计)"]; lbyhb.Text = "¥ " + item["月核拨"]; } divErr.InnerHtml = ""; btnPrint.Visible = true; } else { divPerformance.Visible = false; divErr.InnerHtml = year + " 年度尚未有工号 <b>" + IdCard + "</b> 的业绩汇总记录!"; btnPrint.Visible = false; } } else { divErr.InnerHtml = "尚未公布业绩汇总记录,请以后再试!"; btnPrint.Visible = false; } } } } catch (Exception ex) { divErr.Visible = true; divErr.InnerHtml = ex.ToString(); btnPrint.Visible = false; } }); }
private bool ValidateChangeReserve(SPItemEventProperties properties) { if (properties.AfterProperties["Sala_x0020_reserva"] == null) { properties.ErrorMessage = "Acción no válida. Debe modificar la reserva mediante el formulario de reserva."; properties.Status = SPEventReceiverStatus.CancelWithError; return(false); } if (properties.Web.CurrentUser.ID != properties.Web.Site.SystemAccount.ID && !properties.Web.Groups.GetByName("Propietarios Reserva de Salas").ContainsCurrentUser&& properties.ListItem["Author"].ToString().Remove(properties.ListItem["Author"].ToString().IndexOf(';')) != properties.CurrentUserId.ToString()) { properties.ErrorMessage = "No tiene permitido modificar una reserva realizada por otro usuario.<br/><br/>" + "Para regresar al formulario presione el botón <b>ATRAS</b> de su explorador."; properties.Status = SPEventReceiverStatus.CancelWithError; return(false); } int theRoom = Convert.ToInt32(properties.AfterProperties["Sala_x0020_reserva"]); DateTime startDate = TimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(properties.AfterProperties["EventDate"])); DateTime finishDate = TimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(properties.AfterProperties["EndDate"])); //DateTime todayDate = DateTime.Now.Date; SPWeb theSite = properties.OpenWeb(); SPList theList = theSite.Lists[SOURCE_LIST]; SPQuery query = new SPQuery(); query.Query = string.Format("<Where><And>" + "<DateRangesOverlap><FieldRef Name='EventDate' /><FieldRef Name='EndDate' /><FieldRef Name='RecurrenceID' /><Value Type='DateTime'><Now /></Value></DateRangesOverlap>" + "<Eq><FieldRef Name='Sala_x0020_reserva' /><Value Type='Text'>{0}</Value></Eq>" + "</And></Where>", theSite.Lists[ROOM_LIST].GetItemById(theRoom).Title); query.ExpandRecurrence = true; SPListItemCollection listItems = theList.GetItems(query); foreach (SPListItem listItem in listItems) { if (listItem.ID != properties.ListItem.ID) { //if (Convert.ToDateTime(listItem["Hora de finalización"]).Date >= todayDate) //{ //if (listItem["Sala reserva"].ToString().Remove(listItem["Sala reserva"].ToString().IndexOf(';')) == theRoom) //{ if (!((startDate < Convert.ToDateTime(listItem["Hora de inicio"]) && finishDate <= Convert.ToDateTime(listItem["Hora de inicio"])) || (startDate >= Convert.ToDateTime(listItem["Hora de finalización"]) && finishDate > Convert.ToDateTime(listItem["Hora de finalización"])))) { string message = string.Format( "La reserva que usted trata de realizar coincide con " + "<b>\"{0}\"</b> de <b>{1}</b> a <b>{2}</b> reservada por <i>{3}</i>.<br/><br/>" + "Para regresar al formulario presione el botón <b>ATRAS</b> de su explorador.", listItem.Title, Convert.ToDateTime(listItem["Hora de inicio"]), Convert.ToDateTime(listItem["Hora de finalización"]), listItem["Usuario reserva"].ToString().Substring(listItem["Usuario reserva"].ToString().IndexOf('#') + 1)); properties.ErrorMessage = message; properties.Status = SPEventReceiverStatus.CancelWithError; return(false); } //} //} } } return(true); }
private void Load_cNagrody() { using (SPSite site = new SPSite(workflowProperties.SiteId)) { using (SPWeb web = site.AllWebs[workflowProperties.WebId]) { try { SPList lstNagrody = web.Lists[_LST_NAGRODY]; StringBuilder sb = new StringBuilder(@"<OrderBy><FieldRef Name=""ID"" Ascending=""FALSE"" /></OrderBy><Where><Eq><FieldRef Name=""Zg_x0142_oszenie_x002e_ID"" /><Value Type=""Number"">___ZgloszenieID___</Value></Eq></Where>"); sb.Replace("___ZgloszenieID___", intZgloszenieID.ToString()); string camlQuery = sb.ToString(); SPQuery query = new SPQuery(); query.Query = camlQuery; cNagrody = lstNagrody.GetItems(query); } catch (Exception) { } } } }
protected void Page_Load(object sender, EventArgs e) { SqlConnection cn = null; SPSecurity.RunWithElevatedPrivileges(delegate() { cn = new SqlConnection(EPMLiveCore.CoreFunctions.getConnectionString(SPContext.Current.Site.WebApplication.Id)); cn.Open(); }); if (cn != null) { SPWeb web = SPContext.Current.Web; string resUrl = EPMLiveCore.CoreFunctions.getConfigSetting(web, "EPMLiveResourceURL", true, false); if (resUrl != "") { SPWeb resWeb = null; try { if (resUrl.ToLower() != web.Url.ToLower()) { using (SPSite tempSite = new SPSite(resUrl)) { resWeb = tempSite.OpenWeb(); if (resWeb.Url.ToLower() != resUrl.ToLower()) { resWeb = null; } } } else { resWeb = web; } } catch { } if (resWeb != null) { switch (Request["Report"]) { case "1": try { SPList list = resWeb.Lists["Resources"]; SPQuery query = new SPQuery(); query.Query = "<Where><Eq><FieldRef Name='TimesheetManager'/><Value Type='User'><UserID/></Value></Eq></Where>"; SPListItemCollection lic = list.GetItems(query); foreach (SPListItem li in lic) { string username = ""; string name = ""; try { string user = li["SharePointAccount"].ToString(); SPFieldUserValue uv = new SPFieldUserValue(resWeb, user); username = uv.User.LoginName; name = uv.User.Name; } catch { } if (username != "") { SqlCommand cmd = new SqlCommand("spTSData", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@siteuid", web.Site.ID); cmd.Parameters.AddWithValue("@username", username); cmd.Parameters.AddWithValue("@start", Request["start"]); cmd.Parameters.AddWithValue("@end", Request["end"]); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dttemp = new DataTable(); da.Fill(dttemp); dtRollup.Merge(dttemp, false, MissingSchemaAction.Add); } } } catch { } break; case "2": try { SPList list = resWeb.Lists["Resources"]; SPQuery query = new SPQuery(); query.Query = "<Where><Eq><FieldRef Name='SharePointAccount'/><Value Type='User'><UserID/></Value></Eq></Where>"; SPListItemCollection lic = list.GetItems(query); if (lic.Count > 0) { try { if (lic[0]["TimesheetAdministrator"].ToString().ToLower() == "true") { foreach (SPListItem li in list.Items) { string username = ""; string name = ""; try { string user = li["SharePointAccount"].ToString(); SPFieldUserValue uv = new SPFieldUserValue(resWeb, user); username = uv.User.LoginName; name = uv.User.Name; } catch { } if (username != "") { SqlCommand cmd = new SqlCommand("spTSData", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@siteuid", web.Site.ID); cmd.Parameters.AddWithValue("@username", username); cmd.Parameters.AddWithValue("@start", Request["start"]); cmd.Parameters.AddWithValue("@end", Request["end"]); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dttemp = new DataTable(); da.Fill(dttemp); dtRollup.Merge(dttemp, false, MissingSchemaAction.Add); } } } } catch { } } } catch { } break; case "3": try { SPList list = resWeb.Lists["Resources"]; SPQuery query = new SPQuery(); query.Query = "<Where><Eq><FieldRef Name='SharePointAccount'/><Value Type='User'><UserID/></Value></Eq></Where>"; SPListItemCollection lic = list.GetItems(query); if (lic.Count > 0) { try { foreach (SPListItem li in lic) { string username = ""; string name = ""; try { string user = li["SharePointAccount"].ToString(); SPFieldUserValue uv = new SPFieldUserValue(resWeb, user); username = uv.User.LoginName; name = uv.User.Name; } catch { } if (username != "") { SqlCommand cmd = new SqlCommand("spTSData", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@siteuid", web.Site.ID); cmd.Parameters.AddWithValue("@username", username); cmd.Parameters.AddWithValue("@start", Request["start"]); cmd.Parameters.AddWithValue("@end", Request["end"]); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dttemp = new DataTable(); da.Fill(dttemp); dtRollup.Merge(dttemp, false, MissingSchemaAction.Add); } } } catch { } } } catch { } break; } ; if (resWeb.ID != SPContext.Current.Web.ID) { resWeb.Close(); } } } cn.Close(); listGrid.DataSource = dtRollup; listGrid.DataBind(); } }
private string GenTable(SPListItemCollection coll) { StringBuilder op = new StringBuilder(); op.Append("<table class=\"alttb\">"); op.Append(" <tr class=\"tr2\">"); op.Append(" <td>ID</td>"); op.Append(" <td>Applicant</td>"); op.Append(" <td>Department</td>"); op.Append(" <td>LeaveType</td>"); op.Append(" <td>DateFrom</td>"); op.Append(" <td>DateTo</td>"); op.Append(" <td>LeaveDays</td>"); op.Append(" <td>Status</td>"); op.Append(" </tr>"); foreach (SPListItem item in coll) { string[] uv = (item["WorkFlowNumber"] + "").Split(','); if (uv.Length > 1) { op.Append(" <tr>"); op.Append(" <td><a href=\"" + uv[0] + "\" target=\"_blank\">" + uv[1] + "</a></td>"); op.Append(" <td>" + item["Applicant"] + "</td>"); op.Append(" <td>" + item["Department"] + "</td>"); op.Append(" <td>" + item["LeaveType"] + "</td>"); op.Append(" <td>" + DateTime.Parse(item["DateFrom"] + "").ToShortDateString() + "</td>"); op.Append(" <td>" + DateTime.Parse(item["DateTo"] + "").ToShortDateString() + "</td>"); op.Append(" <td>" + item["LeaveDays"] + "</td>"); op.Append(" <td>" + item["Status"] + "</td>"); op.Append(" </tr>"); } } op.Append("</table>"); return op.ToString(); }
static void Main(string[] args) { if (args.Length != 4) { Console.WriteLine( string.Format( "Exempel:\n{0} {1} {2} {3} {4}", System.AppDomain.CurrentDomain.FriendlyName, "http://localhost:51001", "/sv/projekt", "ShowProjectTab", "true/1/\"string\"" ) ); return; } string siteUrl = args[0]; string dir = args[1]; string fieldName = args[2]; //"ShowProjectTab"; string value = args[3]; int type = 0; bool parsedBool = false; int parsedInt = 0; if (value.StartsWith("\"") && value.EndsWith("\"")) { type = 3; // String value = value.Trim('"'); } else if (bool.TryParse(value, out parsedBool)) { type = 1; // Bool } else if (int.TryParse(value, out parsedInt)) { type = 2; // int } else { throw new Exception("Only designed to take arguments in the form of integers and booleans."); } Console.WriteLine("Starting..."); using (SPSite spsite = new SPSite(siteUrl)) // http://localhost:51001 { using (SPWeb staffWeb = spsite.OpenWeb(dir)) // site.OpenWeb(stafWebId)) { PublishingWeb pweb = PublishingWeb.GetPublishingWeb(staffWeb); SPList staffSitePagesList = pweb.PagesList; SPListItemCollection col = staffSitePagesList.Items; //SPField field = (staffSitePagesList).Fields.GetField(fieldName); Console.WriteLine("Found " + col.Count + " items."); int n = 1; foreach (SPListItem item in col) { try { Console.WriteLine(n++ + " / " + col.Count + ": " + item.Url); //if ((bool)item[fieldName]) continue; if (type == 1) { if ((bool)item[fieldName] != parsedBool) { item[fieldName] = parsedBool; item.SystemUpdate(false); } } else if (type == 2) { if ((int)item[fieldName] != parsedInt) { item[fieldName] = parsedInt; item.SystemUpdate(false); } } else if (type == 3) { if (item[fieldName] == null || item[fieldName].Equals(value) == false) { item[fieldName] = value; item.SystemUpdate(false); } } else { throw new Exception("Only designed to take arguments in the form of integers and booleans."); } } catch (Exception exception) { Console.WriteLine(exception.Message); } } } } Console.WriteLine("Program ended normally."); }
private void IntiDeleteEvent(DayRenderEventArgs e) { try { SPList list = base.GetCurrentSPList(); SPQuery q = new SPQuery(); string sq = @"<Where> <And> <And> <Or> <Or> <And> <Leq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{0}</Value> </Leq> <And> <Geq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{1}</Value> </Geq> <Leq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{2}</Value> </Leq> </And> </And> <And> <Geq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{3}</Value> </Geq> <Leq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{4}</Value> </Leq> </And> </Or> <Or> <And> <And> <Geq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{5}</Value> </Geq> <Leq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{6}</Value> </Leq> </And> <Geq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{7}</Value> </Geq> </And> <And> <Leq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{8}</Value> </Leq> <Geq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{9}</Value> </Geq> </And> </Or> </Or> <Neq> <FieldRef Name='fRecurrence'/><Value Type='Boolean'>0</Value> </Neq> </And> <IsNotNull> <FieldRef Name='MasterSeriesItemID'/></Value> </IsNotNull> </And> </Where>"; DateTime firstDay = DateTime.Parse(e.Day.Date.Year + "-" + e.Day.Date.Month + "-1"); DateTime endDay = firstDay.AddMonths(1); q.Query = String.Format(sq, firstDay, firstDay, endDay, firstDay, endDay, firstDay, endDay, endDay, firstDay, endDay); _DeleteEventItems = list.GetItems(q); } catch (Exception ex) { base.RegisterError(ex); } }
private void StatisticList(SPUser logUser) { string[] lstName = ListName.Split(';'); SPQuery oQuery; SPList sList; int[] itmCounts = new int[4]; SPSecurity.RunWithElevatedPrivileges(delegate() { using (SPSite site = new SPSite(SPContext.Current.Site.ID)) { using (SPWeb web = site.AllWebs[SPContext.Current.Web.ID]) { //设置sharepoint时间格式 SPTimeZone timeZone = web.RegionalSettings.TimeZone; foreach (string mList in lstName) { try { sList = web.Lists.TryGetList(mList); oQuery = new SPQuery(); oQuery.ViewAttributes = "Scope='RecursiveAll'"; oQuery.Query = "<Where><Eq><FieldRef Name='Author'/><Value Type='Text'>" + logUser.Name + "</Value></Eq></Where>"; SPListItemCollection lstItems = sList.GetItems(oQuery); itmCounts[0] = lstItems.Count; //个人 itmCounts[1] = sList.ItemCount; //全部 oQuery = new SPQuery(); DateTime currentDate = DateTime.Now; DateTime qDate = currentDate.AddDays(-1); DateTime cDate = timeZone.LocalTimeToUTC(currentDate); string dt = SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(cDate.ToString())); oQuery.ViewAttributes = "Scope='RecursiveAll'"; oQuery.Query = "<Where><And><Eq><FieldRef Name='Author'/><Value Type='Text'>" + logUser.Name + "</Value></Eq><Geq><FieldRef Name='Created'/><Value Type='DateTime'>" + dt + "</Value></Geq><And/></Where>"; lstItems = sList.GetItems(oQuery); itmCounts[2] = lstItems.Count;//当日更新 qDate = currentDate.AddDays(-7); cDate = timeZone.LocalTimeToUTC(currentDate); dt = SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(cDate.ToString())); oQuery.ViewAttributes = "Scope='RecursiveAll'"; oQuery.Query = "<Where><And><Eq><FieldRef Name='Author'/><Value Type='Text'>" + logUser.Name + "</Value></Eq><Geq><FieldRef Name='Created'/><Value Type='DateTime'>" + dt + "</Value></Geq><And/></Where>"; lstItems = sList.GetItems(oQuery); itmCounts[3] = lstItems.Count;//本周更新 this.Controls.Add(new LiteralControl(mList + "<br/>")); this.Controls.Add(new LiteralControl("个人总数 : " + itmCounts[0].ToString() + "<br/>")); this.Controls.Add(new LiteralControl("团队总数 : " + itmCounts[1].ToString() + "<br/>")); this.Controls.Add(new LiteralControl("当日更新 : " + itmCounts[2].ToString() + "<br/>")); this.Controls.Add(new LiteralControl("本周总数 : " + itmCounts[3].ToString() + "<br/>")); } catch { } } } } } ); }
/// <summary> /// Called by the ASP.NET page framework to notify server controls that use composition-based implementation to create any child controls they contain in preparation for posting back or rendering. /// </summary> protected override void CreateChildControls() { base.CreateChildControls(); _panel = new Panel(); _panel.CssClass = "cp-AnnouncementsWebPart"; SPWeb web = SPContext.Current.Web; web.Lists.ListsForCurrentUser = true; // set up table header Literal header = new Literal(); header.Text = string.Format("<h3 class=\"caption\"><img src=\"/images/captions/announcements.gif\" width=\"118\" height=\"10\" alt=\"{0}\" class=\"caption\" /></h3><div class=\"cp-boundingBox cp-table cp-Announcements\">", Properties.Resources.Caption); _panel.Controls.Add(header); try { SPList list = web.Lists["Announcements"]; // filter for posted, unexpired items SPQuery query = new SPQuery(); query.ViewFields = @"<FieldRef Name=""Title"" /><FieldRef Name=""Posted"" /><FieldRef Name=""ElectionCycles"" />"; query.ViewAttributes = @"Scope=""Recursive"""; query.Query = _queryCaml; SPListItemCollection items = list.GetItems(query); bool hasItems = false; if (items.Count > 0) { // see if any announcements apply to current election cycle Regex re = new Regex(string.Format(_ElectionCyclesExpressionFormat, CPProfile.ElectionCycle)); string listRootUrl = list.RootFolder.ServerRelativeUrl; foreach (SPListItem item in items) { // only process approved items if (item.ModerationInformation.Status != SPModerationStatusType.Approved) { continue; } string ec = item["ElectionCycles"] as string; if (string.IsNullOrEmpty(ec) || re.IsMatch(ec)) { // applies to current election cycle, so display it Panel p = new Panel(); // show indicator if new if (string.IsNullOrEmpty(_category) && (DateTime.Today.AddDays(-NewItemCutoffDays).CompareTo((DateTime)item["Posted"]) < 0)) { Image newStatusImage = new Image(); newStatusImage.ImageUrl = "/images/new.gif"; newStatusImage.AlternateText = "New"; newStatusImage.CssClass = "newAlert"; p.Controls.Add(newStatusImage); } HyperLink link = new HyperLink(); link.ToolTip = link.Text = item["Title"] as string; link.NavigateUrl = string.Format("{0}?ID={1}&RootFolder={2}", _DisplayFormUrl, item.ID, HttpContext.Current.Server.UrlEncode(listRootUrl)); p.Controls.Add(link); _panel.Controls.Add(p); if (!hasItems) { hasItems = true; } } } } if (!hasItems) { Literal emptyText = new Literal(); emptyText.Text = "There are currently no announcements available."; _panel.Controls.Clear(); _panel.Controls.Add(header); _panel.Controls.Add(emptyText); } } catch (SPException) { Literal emptyText = new Literal(); emptyText.Text = "Error occurred retrieving announcement data."; _panel.Controls.Add(emptyText); } // set up table footer Literal footer = new Literal(); footer.Text = "</div>"; _panel.Controls.Add(footer); this.Controls.Add(_panel); }
private MailMessage BuidMailMessage(ClubCloud_Vereniging vereniging) { MailMessage message = null; XDocument xmlInputData = new XDocument(new XElement("Properties")); XElement elements = new XElement("Elements", new object[] { new XElement("HEADER"), new XElement("FEATURED_AREA"), new XElement("FULL_WIDTH_COLUMN"), new XElement("INTRO"), new XElement("HALF_COLUMN_FEATURES"), new XElement("HALF_COLUMN_TOP_IMAGE"), new XElement("ONE_THIRD_TWO_THIRD_COLUMN_LEFT_IMAGE"), new XElement("TWO_THIRD_ONE_THIRD_COLUMN_RIGHT_IMAGE"), new XElement("CENTRECOURT"), new XElement("BOTTOM_CALL_TO_ACTION"), new XElement("FOOTER") }); xmlInputData.Root.Add(elements); xmlInputData.Root.Add(vereniging.ToXElement <ClubCloud_Vereniging>()); EmailTracking track = new EmailTracking { CampaignName = "Introductie", CampaignSource = "Nieuwsbrief", CampagneMedium = "email", ClientId = vereniging.Id, RecipientId = vereniging.Nummer, TrackingId = "UA-9934149-20", CampagneContent = "Introductie", CampagneTerm = "Introductie" }; xmlInputData.Root.Add(track.ToXElement <EmailTracking>()); XElement content = new XElement("Content", new XElement("Subject", string.Format("ClubCloud : De slimme keuze voor {0}", vereniging.Naam))); xmlInputData.Root.Add(content); SPSecurity.RunWithElevatedPrivileges(delegate() { try { using (SPSite currentSite = new SPSite(SPContext.Current.Site.ID, SPUrlZone.Internet)) { using (SPWeb web = currentSite.OpenWeb(SPContext.Current.Web.ID)) { SPDocumentLibrary SiteAssets = null; SPDocumentLibrary SitePages = null; SPFolder Templates = null; SPFolder Online = null; SPFile Aanmelden = null; SPFile Webversion = null; SPItem WebversionItem = null; XmlReader template = null; SPList assets = web.Lists.TryGetList("SiteAssets"); if (assets == null) { assets = web.Lists.TryGetList("Siteactiva"); } if (assets != null) { SiteAssets = (SPDocumentLibrary)assets; } if (SiteAssets != null) { Templates = SiteAssets.RootFolder.SubFolders["Templates"]; } SPList pages = web.Lists.TryGetList("SitePages"); if (pages == null) { pages = web.Lists.TryGetList("Sitepagina's"); } SPQuery query = new SPQuery(); query.Query = string.Format("<Where><Eq><FieldRef Name=\"Title\" /><Value Type=\"Text\">ClubCloud : De slimme keuze voor {0}</Value></Eq></Where>", vereniging.Naam); query.RowLimit = 1; query.ViewFields = @"<FieldRef Name=""Title"" />"; query.ViewAttributes = @"Scope=""Recursive"""; SPListItemCollection items = pages.GetItems(query); if (items.Count > 0) { throw new SPDuplicateObjectException("Club already mailed", new Exception("Club already mailed")); } if (pages != null) { SitePages = (SPDocumentLibrary)pages; } if (SitePages != null) { Online = SitePages.RootFolder.SubFolders["Online"]; } if (Templates != null && Templates.Exists) { Aanmelden = Templates.Files["template.xsl"]; } if (Aanmelden != null && Aanmelden.Exists) { template = XmlReader.Create(Aanmelden.OpenBinaryStream()); } if (template == null) { throw new FileNotFoundException("Template not Found", Aanmelden.Url); } string body = GenerateEmailBody(template, xmlInputData); web.AllowUnsafeUpdates = true; if (Online != null && Online.Exists) { Webversion = Online.Files.Add(Guid.NewGuid() + ".aspx", System.Text.Encoding.UTF8.GetBytes(body), true); WebversionItem = pages.GetItemByUniqueId(Webversion.UniqueId); WebversionItem["Title"] = string.Format("ClubCloud : De slimme keuze voor {0}", vereniging.Naam); WebversionItem.Update(); } if (Webversion != null && Webversion.Exists) { XElement online = new XElement("Online", new object[] { new XElement("WebVersion", string.Format("{0}/{1}", currentSite.Url, Webversion.Url)) }); xmlInputData.Root.Add(online); } if (Aanmelden != null && Aanmelden.Exists) { template = XmlReader.Create(Aanmelden.OpenBinaryStream()); } body = GenerateEmailBody(template, xmlInputData); web.AllowUnsafeUpdates = false; message = Email.CreateMailMessage(body); } } } catch (Exception ex) { Logger.WriteLog(Logger.Category.Unexpected, ex.Source, ex.Message); message = null; } }); return(message); }
public virtual SPGENRepositoryDataItem GetDataItem(SPListItemCollection itemCollection, int itemId, string[] fieldNames, SPGENEntityFileOperationArguments fileOperationParams) { var dataItem = new SPGENRepositoryDataItem(fieldNames); ConvertToDataItem(itemCollection.GetItemById(itemId), dataItem, fileOperationParams); return dataItem; }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { try { SPListItemCollection spColDepartment = GetCollectionList("Config_DepartmentList"); if (spColDepartment != null) { foreach (SPListItem dtr in spColDepartment) { if (dtr["Permission"] != null) { if (!string.IsNullOrEmpty(dtr["Permission"].ToString())) { if (!IsExistUser(dtr, SPContext.Current.Web.CurrentUser.LoginName.ToLower())) { continue; } } } System.Web.UI.HtmlControls.HtmlGenericControl createDiv = new System.Web.UI.HtmlControls.HtmlGenericControl("DIV"); createDiv.ID = System.Guid.NewGuid().ToString(); createDiv.Style.Add(HtmlTextWriterStyle.TextAlign, "center"); createDiv.Style.Add(HtmlTextWriterStyle.Height, "190px"); createDiv.Style.Add(HtmlTextWriterStyle.Display, "block"); createDiv.Attributes.Add("style", "float: left;"); createDiv.Attributes.Add("class", "itemDepartment"); System.Web.UI.HtmlControls.HtmlGenericControl createInnerDiv = new System.Web.UI.HtmlControls.HtmlGenericControl("DIV"); createInnerDiv.ID = System.Guid.NewGuid().ToString(); createInnerDiv.Attributes.Add("style", "border-top: 2px solid #078E3C;"); createInnerDiv.Style.Add(HtmlTextWriterStyle.Width, "85%"); createInnerDiv.Style.Add(HtmlTextWriterStyle.Height, "160px"); createInnerDiv.Style.Add(HtmlTextWriterStyle.TextAlign, "center"); Image img = new Image(); img.ID = System.Guid.NewGuid().ToString(); img.Height = 140; img.Width = Unit.Percentage(100); img.ImageUrl = dtr["ThumbNail_Img"] != null ? dtr["ThumbNail_Img"].ToString() : string.Empty; HyperLink hpl = new HyperLink(); hpl.Text = dtr["Title"] != null ? dtr["Title"].ToString() : string.Empty; hpl.NavigateUrl = dtr["TargetURL"] != null ? dtr["TargetURL"].ToString() : "#"; hpl.CssClass = "DepartmentLink"; createInnerDiv.Controls.Add(img); createInnerDiv.Controls.Add(new LiteralControl("<br />")); createInnerDiv.Controls.Add(hpl); createInnerDiv.Controls.Add(new LiteralControl("<br />")); createDiv.Controls.Add(createInnerDiv); panelRow.Controls.Add(createDiv); } } } catch (Exception ex) { Response.Write(ex.ToString()); } } }
public virtual SPGENRepositoryDataItemCollection GetDataItems(SPListItemCollection listItemCollection, string[] fieldNames, SPGENEntityFileOperationArguments fileOperationParams) { return new SPGENRepositoryDataItemCollection(listItemCollection, fieldNames, fileOperationParams); }
protected void Export(SPListItemCollection coll) { string tmpPath = "/tmpfiles/LeaveRecord/"; string strPath = Server.MapPath(tmpPath); DirectoryInfo dinfo = new DirectoryInfo(strPath); if (!dinfo.Exists) { Directory.CreateDirectory(strPath); } string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls"; string strFilePath = strPath + "/" + fileName; GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8"); GemBox.Spreadsheet.ExcelFile objExcelFile = new ExcelFile(); GemBox.Spreadsheet.ExcelWorksheet sheet1 = objExcelFile.Worksheets.Add("sheet1"); int headIdx = 6; int bodyIdx = headIdx + 1; sheet1.Rows[0].InsertEmpty(bodyIdx + coll.Count); sheet1.Cells[0, 0].Value = "Applicant:"; sheet1.Cells[0, 1].Value = txtfApplicant.Text; sheet1.Cells[1, 0].Value = "LeaveType:"; sheet1.Cells[1, 1].Value = ddlLeaveType.SelectedItem.Text; sheet1.Cells[2, 0].Value = "LeaveDays:"; sheet1.Cells[2, 1].Value = txtfLeaveDays.Text; sheet1.Cells[3, 0].Value = "DateRange:"; sheet1.Cells[3, 1].Value = txtfDateFrom.Text + " - " + txtfDateTo.Text; sheet1.Cells[4, 0].Value = "Department:"; if (string.IsNullOrEmpty(ddlDepartments.SelectedValue)) { sheet1.Cells[4, 1].Value = ddlDepartments.SelectedItem.Text;//hidAssoDepts.Value; } else { sheet1.Cells[4, 1].Value = ddlDepartments.SelectedItem.Text; } sheet1.Cells[5, 0].Value = "Reports to:"; if (chkIsReportToMe.Checked) { sheet1.Cells[5, 1].Value = SPContext.Current.Web.CurrentUser.Name; } sheet1.Cells[headIdx, 0].Value = "WorkFlowNumber"; sheet1.Cells[headIdx, 1].Value = "Applicant"; sheet1.Cells[headIdx, 2].Value = "Department"; sheet1.Cells[headIdx, 3].Value = "LeaveType"; sheet1.Cells[headIdx, 4].Value = "DateFrom"; sheet1.Cells[headIdx, 5].Value = "DateTo"; sheet1.Cells[headIdx, 6].Value = "LeaveDays"; sheet1.Cells[headIdx, 7].Value = "Status"; if (coll.Count > 0) { int i = bodyIdx; foreach (SPListItem item in coll) { string[] uv = (item["WorkFlowNumber"] + "").Split(','); sheet1.Cells[i, 0].Value = uv[1]; sheet1.Cells[i, 1].Value = item["Applicant"] + ""; sheet1.Cells[i, 2].Value = item["Department"] + ""; sheet1.Cells[i, 3].Value = item["LeaveType"] + ""; sheet1.Cells[i, 4].Value = item["DateFrom"] + ""; sheet1.Cells[i, 5].Value = item["DateTo"] + ""; sheet1.Cells[i, 6].Value = item["LeaveDays"] + ""; sheet1.Cells[i, 7].Value = item["Status"] + ""; i += 1; } } objExcelFile.SaveXls(strFilePath); //lblTmpXlsUrl.Text = "<a href=\"" + tmpPath + fileName + "\" target=\"_blank\">" + fileName + "</a>"; lblTmpXlsUrl.Text = "<script type=\"text/javascript\">popexcel('" + tmpPath + fileName + "');</script>"; }
public virtual SPGENRepositoryDataItemCollection GetListItems(SPListItemCollection listItemCollection, string[] fieldNames, bool includeFiles) { throw new NotSupportedException(); }
private void InitData(DayRenderEventArgs e) { if (_Items != null) return; SPList list = base.GetCurrentSPList(); SPQuery q = new SPQuery(); string sq = @"<Where> <Or> <And> <And> <Geq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{0}</Value> </Geq> <Leq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{1}</Value> </Leq> </And> <Eq> <FieldRef Name='fRecurrence'/><Value Type='Boolean'>0</Value> </Eq> </And> <And> <And> <Or> <Or> <And> <Leq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{2}</Value> </Leq> <And> <Geq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{3}</Value> </Geq> <Leq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{4}</Value> </Leq> </And> </And> <And> <Geq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{5}</Value> </Geq> <Leq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{6}</Value> </Leq> </And> </Or> <Or> <And> <And> <Geq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{7}</Value> </Geq> <Leq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{8}</Value> </Leq> </And> <Geq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{9}</Value> </Geq> </And> <And> <Leq> <FieldRef Name='EventDate'/><Value Type='DateTime'>{10}</Value> </Leq> <Geq> <FieldRef Name='EndDate'/><Value Type='DateTime'>{11}</Value> </Geq> </And> </Or> </Or> <Neq> <FieldRef Name='fRecurrence'/><Value Type='Boolean'>0</Value> </Neq> </And> <IsNull> <FieldRef Name='MasterSeriesItemID'/></Value> </IsNull> </And> </Or> </Where>"; DateTime firstDay = DateTime.Parse(e.Day.Date.Year + "-" + e.Day.Date.Month + "-1"); DateTime endDay = firstDay.AddMonths(1); q.Query = String.Format(sq, firstDay, endDay, firstDay, firstDay, endDay, firstDay, endDay, firstDay, endDay, endDay, firstDay, endDay); _Items = list.GetItems(q); //StreamWriter sw = new StreamWriter(@"c:\b.xml",false, Encoding.UTF8); //sw.WriteLine(_Items.Xml); //sw.Flush(); //sw.Close(); //StreamWriter sw2 = new StreamWriter(@"c:\c.xml", false, Encoding.UTF8); //sw2.WriteLine(list.Items.Xml); //sw2.Flush(); //sw2.Close(); //DataTable dt = _Items.GetDataTable(); //dt.WriteXml(@"c:\a.xml"); _ViewUrl = list.DefaultViewUrl; }
/// <summary> /// Get Store information from stores list based in the Store ID from the URL /// Sets the hidden fields so the information is available in the client side /// </summary> private bool GetStoreInfo() { bool infoAvailable = false; try { // get list from site and dispose objects using (SPSite site = new SPSite(SPContext.Current.Site.ID)) { using (SPWeb web = site.RootWeb) { // get Samples list SPList list = web.Lists.TryGetList(STORESLIST); if (list != null) { // get Store Identifier from URL string storeNumber = SPContext.Current.Web.ServerRelativeUrl.Trim('/'); // query the SPList using SPQuery as it seems to be the method with the best performance // and we will be potencially dealing with large volumes of items in the list // Build a query SPQuery query = new SPQuery(); // filter by relevant fields query.Query = string.Concat( "<Where>", string.Format("<Eq><FieldRef Name='" + FILTERBY3 + "'/><Value Type='Number'>{0}</Value></Eq>", storeNumber), "</Where>"); // get only 1 store - query.RowLimit = 1; // get items SPListItemCollection items = list.GetItems(query); if (items.Count > 0) { hiddenStoreName.Value = items[0][COLUMN7FIELD].ToString(); hiddenStoreNumber.Value = storeNumber; hiddenStoreEmail.Value = items[0][COLUMN9FIELD].ToString(); // set infoAvailable flag to true infoAvailable = true; } else { infoAvailable = false; // display error message DisplayMessage(MESSAGENOSTOREINFO); } } else { infoAvailable = false; // display error message DisplayMessage("Stores List is not available"); } } } } catch (Exception ex) { // log exception Ridgian.SP.Utilities.TraceLog.WriteException(ex); infoAvailable = false; // display error message DisplayMessage(MESSAGEERROR); } return(infoAvailable); }
private DataTable GetDataSource(SPListItemCollection items) { DataTable reportDT = new DataTable(); SPWeb currWeb = SPContext.Current.Web; DataTable detailsDT = currWeb.Lists[WorkflowListName.TravelDetails2].Items.GetDataTable(); TRReportItem trReportItem = new TRReportItem(); CommonUtil.logInfo(items.GetDataTable().AsEnumerable().Count().ToString()); CommonUtil.logInfo(detailsDT.AsEnumerable().Count().ToString()); var leftJoin = from parent in items.GetDataTable().AsEnumerable() join child in detailsDT.AsEnumerable() on parent[trReportItem.Title].AsString() equals child[trReportItem.Title].AsString() into Joined from child in Joined.DefaultIfEmpty() select new TRReportItem { Title = parent != null ? parent[trReportItem.Title].AsString() : string.Empty, ChineseName = parent != null ? parent[trReportItem.ChineseName].AsString() : string.Empty, Department = parent != null ? parent[trReportItem.Department].AsString() : string.Empty, CostCenter = child != null ? child[trReportItem.CostCenter].AsString() : string.Empty, TravelDateFrom = child != null ? child[trReportItem.TravelDateFrom].AsString() : string.Empty, TravelDateTo = child != null ? child[trReportItem.TravelDateTo].AsString() : string.Empty, TravelLocationFrom = child != null ? child[trReportItem.TravelLocationFrom].AsString() : string.Empty, TravelLocationTo = child != null ? child[trReportItem.TravelLocationTo].AsString() : string.Empty }; if (leftJoin.Any()) { reportDT = leftJoin.AsDataTable(); } return reportDT; }
public void execute(SPSite site, SPWeb web, string data) { try { Guid intlistid = new Guid(base.key); API.Integration.IntegrationCore core = new API.Integration.IntegrationCore(site.ID, web.ID); Hashtable hshParms = new Hashtable(); hshParms.Add("intlistid", intlistid); DateTime dtStarted = DateTime.Now; DataSet DS = core.GetDataSet("SELECT * FROM INT_LISTS where INT_LIST_ID=@intlistid", hshParms); DataRow drIntegration = DS.Tables[0].Rows[0]; Hashtable hshProperties = null; DataSet dsColumns = null; DataTable dtCols = null; DataTable dtItem = null; DataSet dsUserFields = null; DataTable dtUserFields = null; Hashtable hshUserMap = null; DataTable dtRet = null; DataSet dsItem = null; try { hshProperties = core.GetProperties(intlistid); dsColumns = core.GetDataSet("SELECT * FROM INT_COLUMNS where INT_LIST_ID=@intlistid", hshParms); dtCols = dsColumns.Tables[0]; dtItem = new DataTable(); dtItem.Columns.Add("ID"); foreach (DataRow drCol in dtCols.Rows) { dtItem.Columns.Add(drCol["IntegrationColumn"].ToString()); } DateTime dtLastSynch = new DateTime(1900, 1, 1); try { dtLastSynch = DateTime.Parse(drIntegration["LASTSYNCH"].ToString()); } catch { } if (drIntegration["TIMEINCOMING"].ToString() == "True") { dtItem = core.PullData(dtItem, intlistid, new Guid(drIntegration["LIST_ID"].ToString()), dtLastSynch); base.totalCount = dtItem.Rows.Count + 1; int count = 0; bool allowAdd = false; try { allowAdd = bool.Parse(hshProperties["AllowAddList"].ToString()); } catch { } SPList list = web.Lists[new Guid(drIntegration["LIST_ID"].ToString())]; GridGanttSettings settings = new GridGanttSettings(list); bool bBuildTeamSec = settings.BuildTeamSecurity; dsUserFields = core.GetDataSet(list, "", intlistid); dtUserFields = dsUserFields.Tables[1]; if (dtUserFields.Select("Type='1'").Length > 0 || bBuildTeamSec) { hshUserMap = core.GetUserMap(drIntegration["INT_LIST_ID"].ToString(), true); } bool bSPCol = false; try { if (hshProperties["SPColumn"].ToString() != "") { bSPCol = true; } } catch { } dtRet = new DataTable(); dtRet.Columns.Add("ID"); if (bSPCol) { dtRet.Columns.Add(hshProperties["SPColumn"].ToString()); } foreach (DataRow dr in dtItem.Rows) { int spid = core.iProcessItemRow(dr, list, dtCols, hshProperties, drIntegration["INT_COLID"].ToString(), intlistid, new Guid(drIntegration["MODULE_ID"].ToString()), allowAdd, dtUserFields, hshUserMap); try { if (spid != 0 && bSPCol) { dtRet.Rows.Add(new string[] { dr["ID"].ToString(), spid.ToString() }); } } catch { } base.updateProgress(count++); } if (bSPCol) { core.PostIntegrationUpdateToExternal(dtRet, intlistid, list.ID); } core.LogMessage(intlistid.ToString(), drIntegration["LIST_ID"].ToString(), "Timer: Imported " + dtItem.Rows.Count + " Items", 1); } else if (drIntegration["TIMEOUTGOING"].ToString() == "True") { SPList list = web.Lists[new Guid(drIntegration["LIST_ID"].ToString())]; core.OpenConnection(); dsItem = core.GetDataSet(list, "", intlistid); SPQuery query = new SPQuery(); query.Query = "<Where><Gt><FieldRef Name=\"Modified\" /><Value IncludeTimeValue=\"TRUE\" Type=\"DateTime\">" + Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtLastSynch) + "</Value></Gt></Where>"; SPListItemCollection lic = list.GetItems(query); base.totalCount = lic.Count * 2; int count = 0; foreach (SPListItem li in lic) { core.ProcessItem(dsItem, li, list); updateProgress(count++); } core.PostIntegration(dsItem.Tables[0].Copy(), dsItem.Tables[1], dsItem.Tables[2], list); core.LogMessage(intlistid.ToString(), drIntegration["LIST_ID"].ToString(), "Timer: Exported " + lic.Count + " Items", 1); } hshParms.Add("lastsynch", dtStarted.ToString()); core.ExecuteQuery("UPDATE INT_LISTS set lastsynch=@lastsynch where INT_LIST_ID=@intlistid", hshParms, true); core.CloseConnection(true); } catch (Exception ex) { core.LogMessage(intlistid.ToString(), drIntegration["LIST_ID"].ToString(), "Timer: " + ex.Message, 3); sErrors += "General Error: " + ex.Message; bErrors = true; } finally { hshParms = null; hshUserMap = null; hshProperties = null; if (DS != null) { DS.Dispose(); } if (dsColumns != null) { dsColumns.Dispose(); } if (dtCols != null) { dtCols.Dispose(); } if (dtItem != null) { dtItem.Dispose(); } if (dsUserFields != null) { dsUserFields.Dispose(); } if (dtUserFields != null) { dtUserFields.Dispose(); } if (dtRet != null) { dtRet.Dispose(); } if (dsItem != null) { dsItem.Dispose(); } } } catch (Exception ex) { sErrors += "General Error: " + ex.Message; bErrors = true; } finally { if (web != null) { web.Dispose(); } if (site != null) { site.Dispose(); } data = null; } }
private void StartNotify(SPListItemCollection items) { string sIndex = string.Empty; if (String.IsNullOrEmpty(ToEmail)) return; SmtpClient sendmail = new SmtpClient(SmtpServer, smtpPort); if (!String.IsNullOrEmpty(SmtpUser) && !String.IsNullOrEmpty(SmtpPass)) sendmail.Credentials = new System.Net.NetworkCredential(SmtpUser, SmtpPass); System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage(FromEmail, ToEmail); msg.Subject = "[eDoc] Document Expiration Notify"; msg.Priority = MailPriority.High; msg.IsBodyHtml = true; //msg.Priority = "High"; StringBuilder sb = new StringBuilder(); msg.Body = string.Format("<a href='{0}' title='{1}'>{2}</><br/><br/>Following document has been expired : <br/><br/>", listUrl, listUrl, listUrl); sb.Append("<a href='{0}'>{1}</a> is expiring at {2} and to edit index click <a href='{3}' title='edit index'>here</a>. <br/>"); foreach (SPListItem item in items) { //msg.Subject += item["Title"]; sIndex = listUrl + "/Forms/EditForm.aspx?ID=" + item.ID.ToString(); msg.Body += String.Format(sb.ToString(), item.Web.Url + "/" + item.Url, item["Title"] , item["Period_x0020_End_x0020_Date"].ToString() , sIndex ); CreateFile(msg.Body); } msg.Body += "Please verify! <br><br>Document Admin"; try { sendmail.Send(msg); } catch { } }
void QueryWorkflows() { //Finance string department = "FI"; SPListItemCollection col = GetWorkflowCenterConfigByDept(department); if (col.Count > 0) { FITab.Visible = true; foreach (SPListItem item in col) { string relativePath = item["Relative Path"] == null ? string.Empty : item["Relative Path"].ToString(); string workflowName = item["Workflow Name"] == null ? string.Empty : item["Workflow Name"].ToString(); string remainingTasks = string.Empty; if (!string.IsNullOrEmpty(relativePath)) { remainingTasks = GetRemainingTasks(relativePath); } FIHtml += string.Format(formatHtml, relativePath + "/SitePages/Home.aspx", workflowName, relativePath + "/WorkflowTasks/MyItems.aspx", remainingTasks); } } //Admin department = "AD"; col = GetWorkflowCenterConfigByDept(department); if (col.Count > 0) { AdminTab.Visible = true; foreach (SPListItem item in col) { string relativePath = item["Relative Path"] == null ? string.Empty : item["Relative Path"].ToString(); string workflowName = item["Workflow Name"] == null ? string.Empty : item["Workflow Name"].ToString(); string remainingTasks = string.Empty; if (!string.IsNullOrEmpty(relativePath)) { remainingTasks = GetRemainingTasks(relativePath); } AdminHtml += string.Format(formatHtml, relativePath + "/SitePages/Home.aspx", workflowName, relativePath + "/WorkflowTasks/MyItems.aspx", remainingTasks); } } //HR department = "HR"; col = GetWorkflowCenterConfigByDept(department); if (col.Count > 0) { HRTab.Visible = true; foreach (SPListItem item in col) { string relativePath = item["Relative Path"] == null ? string.Empty : item["Relative Path"].ToString(); string workflowName = item["Workflow Name"] == null ? string.Empty : item["Workflow Name"].ToString(); string remainingTasks = string.Empty; if (!string.IsNullOrEmpty(relativePath)) { remainingTasks = GetRemainingTasks(relativePath); } HRHtml += string.Format(formatHtml, relativePath + "/SitePages/Home.aspx", workflowName, relativePath + "/WorkflowTasks/MyItems.aspx", remainingTasks); } } //SCM department = "SCM"; col = GetWorkflowCenterConfigByDept(department); if (col.Count > 0) { SCMTab.Visible = true; foreach (SPListItem item in col) { string relativePath = item["Relative Path"] == null ? string.Empty : item["Relative Path"].ToString(); string workflowName = item["Workflow Name"] == null ? string.Empty : item["Workflow Name"].ToString(); string remainingTasks = string.Empty; if (!string.IsNullOrEmpty(relativePath)) { remainingTasks = GetRemainingTasks(relativePath); } SCMHtml += string.Format(formatHtml, relativePath + "/SitePages/Home.aspx", workflowName, relativePath + "/WorkflowTasks/MyItems.aspx", remainingTasks); } } //MO department = "MO"; col = GetWorkflowCenterConfigByDept(department); if (col.Count > 0) { MOTab.Visible = true; foreach (SPListItem item in col) { string relativePath = item["Relative Path"] == null ? string.Empty : item["Relative Path"].ToString(); string workflowName = item["Workflow Name"] == null ? string.Empty : item["Workflow Name"].ToString(); string remainingTasks = string.Empty; if (!string.IsNullOrEmpty(relativePath)) { remainingTasks = GetRemainingTasks(relativePath); } MOHtml += string.Format(formatHtml, relativePath + "/SitePages/Home.aspx", workflowName, relativePath + "/WorkflowTasks/MyItems.aspx", remainingTasks); } } }
public void Reset() { _query = null; _items = null; }
public string GetLatestMessageSubject(DateTime beforetime, out string nextBeforeTime, bool publicmessage) { using (new SPMonitoredScope("Weixin.GetLatestMessageSubject", 5000)) { Guid siteid = SPContext.Current.Site.ID; Guid webid = SPContext.Current.Web.ID; //Guid siteid = new Guid("f424bb38-822a-4d31-b61d-49207f22b510"); //Guid webid = new Guid("341722fa-3840-417d-8ed9-3f8aa7a735dc"); StringBuilder ret = new StringBuilder(); using (SPSite site = new SPSite(siteid, SPFBAUser.usertoken)) //using (SPSite site = new SPSite(siteid)) { using (SPWeb web = site.OpenWeb(webid)) { //SPList currentMessageList = web.GetList("/sites/test/lists/Team%20Discussion/"); SPList currentMessageList = web.GetList(publicmessage ? PublicMessageListUrl : PrivateMessageListUrl); SPQuery qry = new SPQuery(); qry.RowLimit = 5; qry.Query = "<OrderBy Override=\"TRUE\"><FieldRef Name=\"Modified\" Ascending=\"FALSE\" /></OrderBy><Where>" + "<Lt><FieldRef Name='Created'/><Value IncludeTimeValue='TRUE' Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(beforetime) + "</Value></Lt></Where>"; qry.ViewFields = @"<FieldRef Name=""Author"" /><FieldRef Name=""ItemChildCount"" /><FieldRef Name=""Modified"" /><FieldRef Name=""Title"" />"; try { SPListItemCollection result = currentMessageList.GetItems(qry); if (result != null && result.Count > 0) { string lastTime = string.Empty; foreach (SPListItem item in result) { string author = item["Author"] == null ? string.Empty : item["Author"].ToString().Trim(); SPFieldUserValue au = author.Equals(string.Empty) ? null : (item.Fields.GetFieldByInternalName("Author").GetFieldValue(item["Author"].ToString()) as SPFieldUserValue); string u = au == null ? string.Empty : (au.User == null ? string.Empty : au.User.Name); ret.AppendLine(string.Concat("主题:", item["Title"])); ret.AppendLine(string.Concat("作者:", u)); ret.AppendLine(string.Concat("修改时间", item["Modified"].ToString())); ret.AppendLine(string.Concat("回复数:", item["ItemChildCount"])); ret.AppendLine(); lastTime = item["Modified"].ToString(); } nextBeforeTime = lastTime; return(ret.ToString()); } else { nextBeforeTime = string.Empty; return("已无更早留言"); } } catch (Exception ex) { nextBeforeTime = string.Empty; return(ex.Message); } } } } }
/// <summary> /// Get the list of items in the list based on the view /// </summary> private void QueryList() { _items = _list.GetItems(_view); }
protected void Page_Load(object sender, EventArgs e) { string buscar = string.Empty; ocultarDiv(); Boolean ban = false; LogicaNegocio Ln = new LogicaNegocio(); Permisos permiso = new Permisos(); string PermisoConfigurado = string.Empty; SPWeb app2 = SPContext.Current.Web; ValidarPermisos validar = new ValidarPermisos(); DataTable dt = new DataTable("dt"); validar.NombreUsuario = util.ObtenerValor(app2.CurrentUser.Name); validar.Pagina = pagina; validar.Etapa = ""; dt = permiso.ListarPerfil(validar); if (dt.Rows.Count > 0) { ListaEdicion = ConsultaListaEdicionUsuario(); dtUsuarioRiesgo = ConsultarUsuarioRiesgo(); if (!Page.IsPostBack) { cb_estados.Attributes["onChange"] = "Dialogo();"; cb_etapa.Attributes["onChange"] = "Dialogo();"; cb_subetapa.Attributes["onChange"] = "Dialogo();"; ViewState["USER"] = util.ObtenerValor(app2.CurrentUser.Name); ViewState["CARGO"] = dt.Rows[0]["descCargo"].ToString(); ViewState["AREA"] = dt.Rows[0]["Etapa"].ToString(); //"Riesgo"; string val = Page.Request.QueryString["Area"] as string; buscar = Page.Request.QueryString["IdEmpresa"] as string + "#" + Page.Request.QueryString["IdOperacion"] as string; ban = true; if (!string.IsNullOrEmpty(ViewState["CARGO"].ToString()) && ban == true) { Ln.CargarLista(ref cb_estados, Constantes.LISTAESTADO.ESTADO); Ln.CargarLista(ref cb_etapa, Constantes.LISTAETAPA.ETAPAS); Ln.CargarLista(ref cb_subetapa, Constantes.LISTASUBETAPA.SUBETAPA); ViewState["ESTADO"] = ""; ViewState["SUBETAPA"] = ""; ViewState["ETAPA"] = ""; ViewState["OPCIONESPERMISOS"] = null; if (buscar != "#") { CargarGrid(ViewState["ETAPA"].ToString(), ViewState["SUBETAPA"].ToString(), ViewState["ESTADO"].ToString(), buscar, pageSize, pageNro); } else { CargarGrid(ViewState["ETAPA"].ToString(), ViewState["SUBETAPA"].ToString(), ViewState["ESTADO"].ToString(), txtBuscar.Text.ToString(), pageSize, pageNro); } } } else { if (buscar != "" && buscar != "#") { CargarGrid(ViewState["ETAPA"].ToString(), ViewState["SUBETAPA"].ToString(), ViewState["ESTADO"].ToString(), buscar, pageSize, pageNro); } else { CargarGrid(ViewState["ETAPA"].ToString(), ViewState["SUBETAPA"].ToString(), ViewState["ESTADO"].ToString(), txtBuscar.Text.ToString(), pageSize, pageNro); } } } else { dvFormulario.Style.Add("display", "none"); dvWarning1.Style.Add("display", "block"); lbWarning1.Text = "Usuario sin permisos configurados"; } }
/// <summary> /// Method executes query on calendar list and fetch all the items /// </summary> private void FetchEvents() { if (!string.IsNullOrEmpty(_listName)) { SPQuery query = new SPQuery(); query.CalendarDate = _calendar.VisibleDate; query.ExpandRecurrence = true; query.Query = "<Where>" + "<DateRangesOverlap>" + "<FieldRef Name=\"" + EventDateField + "\"></FieldRef>" + "<FieldRef Name=\"" + EndDateField + "\"></FieldRef>" + //"<FieldRef Name=\"RecurrenceID\"></FieldRef>" + "<Value Type=\"DateTime\"><Month/></Value>" + "</DateRangesOverlap></Where>"; events = SPContext.Current.Web.Lists[ListName].GetItems(query); } }
public Hashtable CheckOOBBreakdown(SPUserCodeWorkflowContext context, double breakdownValue, string breakdownType, string breakdownsListName, string annualBreakdownsListName, double annualPercent) { string res = string.Empty; string debugInfo = string.Empty; string s = string.Empty; Hashtable results = new Hashtable(); int oobYear = DateTime.Now.Year; bool success = true; string breakdownColumnName = OOBColumnIdentifier.GetColumnInternalNameByBreakdownType(breakdownType); if (breakdownValue != 0) { try { using (SPSite site = new SPSite(context.CurrentWebUrl)) { using (SPWeb web = site.OpenWeb()) { double sumBefore = 0; double sumAfter = 0; double percent = 0.05; double budget = 0; double percentTotal = annualPercent / 100; double budgetTotal = 0; SPList oobList = web.Lists[breakdownsListName]; if (oobList != null) { SPQuery oobQuery = new SPQuery(); oobQuery.Query = ""; /* * oobQuery.ViewXml = "<View>" + * "<ViewFields>" + * "<FieldRef Name='" + breakdownColumnName + "'/>" + * "</ViewFields>" + * "<Query>" + * "<Where><Eq><FieldRef Name='OOBAppro0'/><Value Type='WorkflowStatus'>16</Value></Eq></Where>" + * "</Query>" + * "</View>"; * */ oobQuery.ViewXml = "<View>" + "<ViewFields>" + "<FieldRef Name='" + breakdownColumnName + "'/>" + "</ViewFields>" + "<Query>" + "<Where>" + "<And>" + "<Eq>" + "<FieldRef Name='OOBAppro0' />" + "<Value Type='WorkflowStatus'>16</Value>" + "</Eq>" + "<And>" + "<Eq>" + "<FieldRef Name='Date_x0020_of_x0020_request' />" + "<Value IncludeTimeValue='FALSE' Type='DateTime'>" + oobYear.ToString() + "-01-01</Value>" + "</Eq>" + "<Eq>" + "<FieldRef Name='Date_x0020_of_x0020_request' />" + "<Value IncludeTimeValue='FALSE' Type='DateTime'>" + oobYear.ToString() + "-12-31</Value>" + "</Eq>" + "</And>" + "</And>" + "</Where>" + "</Query>" + "</View>"; SPListItemCollection items = oobList.GetItems(oobQuery); if (items != null) { //debugInfo += string.Format("Items({0}) count: {1}{2}", breakdownColumnName, items.Count, Environment.NewLine); s += "1"; foreach (SPListItem item in items) { sumBefore += item[breakdownColumnName] == null ? 0 : Convert.ToDouble(item[breakdownColumnName]); } } sumAfter = sumBefore + breakdownValue; //debugInfo += string.Format("Sum before: {0} Sum after: {1}{2}", sumBefore, sumAfter, Environment.NewLine); s += "2"; } else { res = "List(" + breakdownsListName + ") not found."; success = false; //debugInfo += "List(" + breakdownsListName + ") not found."; s += "3"; } SPList breakdownList = web.Lists[annualBreakdownsListName]; var breakdownQuery = new SPQuery(); breakdownQuery.ViewXml = "<View>" + "<Query>" + "</Query>" + "</View>"; SPListItemCollection breakdowns = breakdownList.GetItems(breakdownQuery); if (breakdowns != null) { //debugInfo += string.Format("Items(breakdowns types) count: {0}{1}", breakdowns.Count, Environment.NewLine); s += "4"; foreach (SPListItem breakdown in breakdowns) { s += "5"; string fl = (string)breakdown["Expense_x0020_Estimate_x0020_Bre"]; //debugInfo += fl; //debugInfo += "!"+new SPFieldLookupValue(fl).LookupValue+"!"; var flv = new SPFieldLookupValue(fl).LookupValue; s += "6"; if (breakdownType == flv) { percent = breakdown["Board_x0020_Major_x0020_Action_x"] == null ? 0 : Convert.ToDouble(breakdown["Board_x0020_Major_x0020_Action_x"]); budget = breakdown["Budget"] == null ? 0 : Convert.ToDouble(breakdown["Budget"]); //debugInfo += string.Format("Breakdown({0}) Percent: {1} Budget: {2}{3}", breakdownType, percent, budget, Environment.NewLine); s += "7"; } budgetTotal += breakdown["Budget"] == null ? 0 : Convert.ToDouble(breakdown["Budget"]); } //debugInfo += string.Format("Breakdown({0}) BudgetTotal: {1}{2}", breakdownType, budgetTotal, Environment.NewLine); s += "8"; } else { res = "List(" + annualBreakdownsListName + ") not found."; success = false; //debugInfo += "List(" + annualBreakdownsListName + ") not found."; s += "9"; } if (string.IsNullOrEmpty(res)) { if (Math.Round(sumAfter, 2, MidpointRounding.ToEven) > Math.Round(percent * budget, 2, MidpointRounding.ToEven) || Math.Round(sumAfter, 2, MidpointRounding.ToEven) > Math.Round(percentTotal * budgetTotal, 2, MidpointRounding.ToEven)) { res = "Exceed"; } else { res = "OK"; } //debugInfo += string.Format("Percent/Percent Total: {0}/{1}, {2}", percent, percentTotal, Environment.NewLine); //debugInfo += string.Format("Treshhold1: {0}, {1}", Math.Round(percent * budget, 2, MidpointRounding.ToEven), Environment.NewLine); //debugInfo += string.Format("Treshhold2: {0}, {1}", Math.Round(percentTotal * budgetTotal, 2, MidpointRounding.ToEven), Environment.NewLine); //debugInfo += res; s += "10"; } } } } catch (Exception e) { results = new Hashtable(); results["result"] = s + e.ToString() + debugInfo; results["success"] = false; //results["debugInfo"] = string.Format("{0} - {1}",debugInfo,e.Message); return(results); } } else { res = "OK-Zero";//no breakdown requested } results["result"] = res; //results["debugInfo"] = debugInfo; results["success"] = success; return(results); }
/// <summary> /// Get and return table with correct url /// </summary> /// <param name="items"></param> /// <returns></returns> public static DataTable GetTableWithCorrectUrlHotNews(SPListItemCollection items) { var dataTable = items.GetDataTable(); if (items != null && items.Count > 0) { string imagepath = string.Empty; ImageFieldValue imageIcon; SPFieldUrlValue advLink; for (int i = 0; i < items.Count; i++) { imageIcon = items[i][FieldsName.NewsRecord.English.PublishingPageImage] as ImageFieldValue; if (imageIcon != null) { dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imageIcon.ImageUrl; } else { if (imagepath.Length > 2) dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath.Trim().Substring(0, imagepath.Length - 2); else { dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath; } } if (items[i].Fields.ContainsField(FieldsName.NewsRecord.English.LinkAdv)) { advLink = new SPFieldUrlValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.LinkAdv])); dataTable.Rows[i][FieldsName.NewsRecord.English.LinkAdv] = advLink.Url; } } } return dataTable; }
private void SelectBatch_ExecuteCode(object sender, EventArgs e) { //Generate Batch Name string batchName = String.Format("B{0:yyMMdd}", DateTime.Today); using (SPSite site = new SPSite(workflowProperties.SiteId)) { using (SPWeb web = site.AllWebs[workflowProperties.WebId]) { SPList list = web.Lists["Rejestr Batchów"]; StringBuilder sb = new StringBuilder(@"<OrderBy><FieldRef Name=""ID"" /></OrderBy><Where><Eq><FieldRef Name=""BatchName"" /><Value Type=""Text"">___BatchName___</Value></Eq></Where>"); sb.Replace("___BatchName___", batchName); string camlQuery = sb.ToString(); SPQuery query = new SPQuery(); query.Query = camlQuery; int batchID; SPListItemCollection items = list.GetItems(query); if (items.Count > 0) { batchID = items[0].ID; } else { SPListItem nitem = list.AddItem(); nitem["BatchName"] = batchName; nitem.Update(); batchID = nitem.ID; try { //create Batch Library web.Lists.Add(batchName, "", SPListTemplateType.PictureLibrary); web.Update(); //set visible new Batch library //SPList createdList = web.Lists[batchName]; //createdList.OnQuickLaunch = true; //createdList.Update(); } catch (Exception) { } } try { //update Batch.ID in current item SPItem item = workflowProperties.Item; //copy Scan to Batch Library SPList s_list = web.Lists["Skany"]; SPListItem s_item = s_list.GetItemById(Convert.ToInt32(item["Skan.ID"])); string tokenName = s_item["NameOrTitle"].ToString(); item["Batch.ID"] = batchID; item["Token zgłoszenia"] = tokenName; item.Update(); byte[] picFile = null; SPFolder Sourcelibrary = s_item.Web.Folders["Skany"]; picFile = s_item.File.OpenBinary(); if (picFile != null && picFile.Length > 0) { try { SPFolder DestLibrary = s_item.Web.Folders[batchName]; //batchName s_item.Web.AllowUnsafeUpdates = true; DestLibrary.Files.Add(System.IO.Path.GetFileName(s_item.File.Name), picFile); } catch (Exception) { } } } catch (Exception) { } } } }
/// <summary> /// Get and return table with correct url /// </summary> /// <param name="categoryListName">categoryListName</param> /// <param name="items"></param> /// <returns></returns> public static DataTable GetTableWithCorrectUrl(string categoryListName, SPListItemCollection items) { var dataTable = items.GetDataTable(); if (!dataTable.Columns.Contains(FieldsName.CategoryId)) { dataTable.Columns.Add(FieldsName.CategoryId, Type.GetType("System.String")); } if (!dataTable.Columns.Contains(FieldsName.ArticleStartDateTemp)) { dataTable.Columns.Add(FieldsName.ArticleStartDateTemp, Type.GetType("System.String")); } if (items != null && items.Count > 0) { string imagepath = string.Empty; ImageFieldValue imageIcon; SPFieldUrlValue advLink; DateTime time = new DateTime(); for (int i = 0; i < items.Count; i++) { imagepath = Convert.ToString(items[i][FieldsName.NewsRecord.English.ThumbnailImage]); imageIcon = items[i][FieldsName.NewsRecord.English.PublishingPageImage] as ImageFieldValue; if (imageIcon != null) { dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = GetThumbnailImagePath(imageIcon.ImageUrl); } else { if (imagepath.Length > 2) dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath.Trim().Substring(0, imagepath.Length - 2); else { dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath; } } if (items[i].Fields.ContainsField(FieldsName.NewsRecord.English.LinkAdv)) { advLink = new SPFieldUrlValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.LinkAdv])); dataTable.Rows[i][FieldsName.NewsRecord.English.LinkAdv] = advLink.Url; } if (!string.IsNullOrEmpty(Convert.ToString(items[i][FieldsName.NewsRecord.English.CategoryName]))) { SPFieldLookupValue catLK = new SPFieldLookupValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.CategoryName])); dataTable.Rows[i][FieldsName.CategoryId] = catLK.LookupId; } time = Convert.ToDateTime(dataTable.Rows[i][FieldsName.ArticleStartDates]); dataTable.Rows[i][FieldsName.ArticleStartDateTemp] = string.Format("Ngày {0}/{1}/{2}", time.Day, time.Month, time.Year); } } return dataTable; }
private bool ValidateNewReserve(SPItemEventProperties properties) { SPWeb theSite = properties.OpenWeb(); SPList theList = theSite.Lists[SOURCE_LIST]; List <CalendarEvent> newCalendarEvents = new List <CalendarEvent>(); CalendarEvent theNewCalendarEvent = new CalendarEvent(); theNewCalendarEvent.EventDate = TimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(properties.AfterProperties["EventDate"])); theNewCalendarEvent.EndDate = TimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(properties.AfterProperties["EndDate"])); theNewCalendarEvent.IsRecurrent = Convert.ToInt16(properties.AfterProperties["fRecurrence"]); theNewCalendarEvent.IsAllDayEvent = Convert.ToInt16(properties.AfterProperties["fAllDayEvent"]); theNewCalendarEvent.RecurrenceData = (properties.AfterProperties["RecurrenceData"] == null) ? string.Empty : properties.AfterProperties["RecurrenceData"].ToString(); theNewCalendarEvent.EventType = properties.AfterProperties["EventType"].ToString(); theNewCalendarEvent.RoomReserved = Convert.ToInt32(properties.AfterProperties["Sala_x0020_reserva"]); SPQuery query = new SPQuery(); query.Query = string.Format("<Where><And>" + "<DateRangesOverlap><FieldRef Name='EventDate' /><FieldRef Name='EndDate' /><FieldRef Name='RecurrenceID' /><Value Type='DateTime'><Now /></Value></DateRangesOverlap>" + "<Eq><FieldRef Name='Sala_x0020_reserva' /><Value Type='Text'>{0}</Value></Eq>" + "</And></Where>", theSite.Lists[ROOM_LIST].GetItemById(theNewCalendarEvent.RoomReserved).Title); query.ExpandRecurrence = true; SPListItemCollection currentCalendarEvents = theList.GetItems(query); if (theNewCalendarEvent.IsRecurrent == -1) { newCalendarEvents = RecurrenceEventsGenerator.GetAllRecurrenceEvents(theNewCalendarEvent); } else { newCalendarEvents.Add(theNewCalendarEvent); } foreach (SPListItem currentCalendarEvent in currentCalendarEvents) { foreach (CalendarEvent newCalendarEvent in newCalendarEvents) { if (!((newCalendarEvent.EventDate < Convert.ToDateTime(currentCalendarEvent["Hora de inicio"]) && newCalendarEvent.EndDate <= Convert.ToDateTime(currentCalendarEvent["Hora de inicio"])) || (newCalendarEvent.EventDate >= Convert.ToDateTime(currentCalendarEvent["Hora de finalización"]) && newCalendarEvent.EndDate > Convert.ToDateTime(currentCalendarEvent["Hora de finalización"])))) { string message = string.Format( "La reserva que usted trata de realizar coincide con " + "<b>\"{0}\"</b> de <b>{1}</b> a <b>{2}</b> reservada por <i>{3}</i>.<br/><br/>" + "Para regresar al formulario presione el botón <b>ATRAS</b> de su explorador.", currentCalendarEvent.Title, Convert.ToDateTime(currentCalendarEvent["Hora de inicio"]), Convert.ToDateTime(currentCalendarEvent["Hora de finalización"]), currentCalendarEvent["Usuario reserva"].ToString().Substring(currentCalendarEvent["Usuario reserva"].ToString().IndexOf('#') + 1)); properties.ErrorMessage = message; properties.Status = SPEventReceiverStatus.CancelWithError; return(false); } } } return(true); }