public static List <TravelVoucher> GetAll_TravelVoucherFromSharePoint(string siteUrl, string listName, string DaysDifference) { List <TravelVoucher> _retList = new List <TravelVoucher>(); try { using (MSC.ClientContext context = CustomSharePointUtility.GetContext(siteUrl)) { if (context != null) { MSC.List list = context.Web.Lists.GetByTitle(listName); MSC.ListItemCollectionPosition itemPosition = null; while (true) { var dataDateValue = DateTime.Now.AddDays(-Convert.ToInt32(DaysDifference)); MSC.CamlQuery camlQuery = new MSC.CamlQuery(); camlQuery.ListItemCollectionPosition = itemPosition; camlQuery.ViewXml = @"<View> <Query> <Where> <And> <Eq> <FieldRef Name='StatusCode'/> <Value Type='Text'>P</Value> </Eq> <Geq><FieldRef Name='Modified'/><Value Type='DateTime'>" + dataDateValue.ToString("o") + "</Value></Geq>"; camlQuery.ViewXml += @"</And> </Where> </Query> < RowLimit >4000</RowLimit> <ViewFields> <FieldRef Name='ID'/> <FieldRef Name='ExpVoucherNo'/> <FieldRef Name='CreatorName'/> <FieldRef Name='CreatorDepartment'/> <FieldRef Name='CreatorLocation'/> <FieldRef Name='Destination'/> <FieldRef Name='VisitDate'/> <FieldRef Name='StatusCode'/> <FieldRef Name='StatusName'/> <FieldRef Name='AssignUser'/> <FieldRef Name='CreationDate'/> <FieldRef Name='AssignDate'/> <FieldRef Name='FunctionalHead'/> <FieldRef Name='SequenceNo'/> <FieldRef Name='EmployeeName'/> <FieldRef Name='EmployeeNumber'/> <FieldRef Name='Designation'/> <FieldRef Name='ActionTaken'/> <FieldRef Name='CompanyCode'/> <FieldRef Name='DivisionName'/> <FieldRef Name='Modified'/> <FieldRef Name='TravelType'/> <FieldRef Name='CurrentApprove'/> </ViewFields></View>"; //////camlQuery.ViewXml = @"<View> ////// <Query> ////// <Where> ////// <And> ////// <Eq> ////// <FieldRef Name='StatusCode'/> ////// <Value Type='Text'>P</Value> ////// </Eq> ////// <Geq><FieldRef Name='Modified'/><Value Type='DateTime'>" + dataDateValue.ToString("o") + "</Value></Geq>"; ////// camlQuery.ViewXml += @"</And> ////// </Where> ////// </Query> ////// <RowLimit>4000</RowLimit> ////// <ViewFields> ////// <FieldRef Name='ID'/> ////// <FieldRef Name='ExpVoucherNo'/> ////// <FieldRef Name='CreatorName'/> ////// <FieldRef Name='CreatorDepartment'/> ////// <FieldRef Name='CreatorLocation'/> ////// <FieldRef Name='Destination'/> ////// <FieldRef Name='VisitDate'/> ////// <FieldRef Name='StatusCode'/> ////// <FieldRef Name='StatusName'/> ////// <FieldRef Name='AssignUser'/> ////// <FieldRef Name='CreationDate'/> ////// <FieldRef Name='AssignDate'/> ////// <FieldRef Name='FunctionalHead'/> ////// <FieldRef Name='SequenceNo'/> ////// <FieldRef Name='EmployeeName'/> ////// <FieldRef Name='EmployeeNumber'/> ////// <FieldRef Name='Designation'/> ////// <FieldRef Name='ActionTaken'/> ////// <FieldRef Name='CompanyCode'/> ////// <FieldRef Name='DivisionName'/> ////// <FieldRef Name='Modified'/> ////// <FieldRef Name='TravelType'/> ////// <FieldRef Name='CurrentApprove'/> ////// </ViewFields></View>"; MSC.ListItemCollection Items = list.GetItems(camlQuery); context.Load(Items); context.ExecuteQuery(); itemPosition = Items.ListItemCollectionPosition; foreach (MSC.ListItem item in Items) { _retList.Add(new TravelVoucher { Id = Convert.ToInt32(item["ID"]), ExpVoucherNo = Convert.ToString(item["ExpVoucherNo"]).Trim(), CreatorName = Convert.ToString((item["CreatorName"] as Microsoft.SharePoint.Client.FieldUserValue).LookupValue), CreatorDepartment = Convert.ToString(item["CreatorDepartment"]).Trim(), CreatorLocation = Convert.ToString(item["CreatorLocation"]).Trim(), Destination = Convert.ToString(item["Destination"]).Trim(), VisitDate = Convert.ToString(item["VisitDate"]).Trim(), StatusCode = Convert.ToString(item["StatusCode"]).Trim(), StatusName = Convert.ToString(item["StatusName"]), AssignUser = item["AssignUser"] == null ? "" : Convert.ToString((item["AssignUser"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId), FunctionalHead = item["FunctionalHead"] == null ? "" : Convert.ToString(item["FunctionalHead"]).Trim(), CreationDate = Convert.ToString(item["CreationDate"]).Trim(), AssignDate = Convert.ToString(item["AssignDate"]).Trim(), SequenceNo = Convert.ToString(item["SequenceNo"]).Trim(), EmployeeName = Convert.ToString(item["EmployeeName"]).Trim(), EmployeeNumber = Convert.ToString(item["EmployeeNumber"]).Trim(), Designation = Convert.ToString(item["Designation"]).Trim(), ActionTaken = Convert.ToString(item["ActionTaken"]).Trim(), CompanyCode = Convert.ToString(item["CompanyCode"]), DivisionName = Convert.ToString(item["DivisionName"]), Modified = Convert.ToString(item["Modified"]), TravelType = Convert.ToString(item["TravelType"]), CurrentApprove = Convert.ToString((item["CurrentApprove"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId), }); } if (itemPosition == null) { break; // TODO: might not be correct. Was : Exit While } } } } } catch (Exception ex) { CustomSharePointUtility.WriteLog("Error in GetAll_EmployeeDetailsFromSharePoint()" + " Error:" + ex.Message); } return(_retList); }
//public static void sample() //{ // List<TravelVoucher> data = new List<TravelVoucher>(); // List<Mailing> mail=EmailData(data, "", ""); //} public static bool EmailData(List <TravelVoucher> updationList, string siteUrl, string listName) { bool retValue = false; try { using (MSC.ClientContext context = CustomSharePointUtility.GetContext(siteUrl)) { //List<Mailing> varx = new List<Mailing>(); MSC.List list = context.Web.Lists.GetByTitle(listName); for (var i = 0; i < updationList.Count; i = i + 50) { var updateList = updationList.Skip(i).Take(50).ToList(); if (updateList != null && updateList.Count > 0) { foreach (var updateItem in updateList) { MSC.ListItem listItem = null; MSC.ListItemCreationInformation itemCreateInfo = new MSC.ListItemCreationInformation(); listItem = list.AddItem(itemCreateInfo); var obj = new Object(); //Mailing data = new Mailing(); //var _From = ""; var _To = ""; //var _Cc = ""; var _Body = ""; var _Subject = ""; if (updateItem.SequenceNo == "1") { _To = updateItem.FunctionalHead; } else { _To = updateItem.AssignUser; } _Subject = "Gentle Reminder"; // + updateItem.ExpVoucherNo + " Travel Voucher Approval is Pending _Body += "Dear User, <br><br>This is to inform you that below request is pending for your Approval."; _Body += "<br><b>Workflow Name :</b> Travel Voucher "; _Body += "<br><b>Voucher No :</b> " + updateItem.ExpVoucherNo; _Body += "<br><b>Date of Creation :</b> " + updateItem.CreationDate; _Body += "<br><b>Employee : </b> " + updateItem.CreatorName; _Body += "<br><b>Designation :</b> " + updateItem.Designation; _Body += "<br><b>Department :</b> " + updateItem.CreatorDepartment; _Body += "<br><b>Location :</b> " + updateItem.CreatorLocation; _Body += "<br><b>Status :</b> " + updateItem.StatusName; _Body += "<br><h3>Kindly provide your approval</h3>"; _Body += "<br><h3>For Approval Please Click in the below link</h3>"; if (updateItem.SequenceNo == "1") { _Body += "<br><a href=\"https://aparindltd.sharepoint.com/TravelVoucher/SitePages/PendingFunctionalHead.aspx\">View Link</a>"; } else if (updateItem.SequenceNo == "5") { _Body += "<br><a href=\"https://aparindltd.sharepoint.com/TravelVoucher/SitePages/PendingTravelDesk.aspx\">View Link</a>"; } else if (updateItem.SequenceNo == "2") { _Body += "<br><a href=\"https://aparindltd.sharepoint.com/TravelVoucher/SitePages/Pending%20Request.aspx\">View Link</a>"; } else if (updateItem.SequenceNo == "3") { _Body += "<br><a href=\"https://aparindltd.sharepoint.com/TravelVoucher/SitePages/PendingRequestAccounts.aspx\">View Link</a>"; } listItem["ToUser"] = _To; listItem["MailSubject"] = _Subject; listItem["MailBody"] = _Body; // listItem.Update(); } try { context.ExecuteQuery(); retValue = true; } catch (Exception ex) { CustomSharePointUtility.WriteLog(string.Format("Error in InsertUpdate_EmployeeMaster ( context.ExecuteQuery();): Error ({0}) ", ex.Message)); return(false); //continue; } } } } } catch (Exception ex) { CustomSharePointUtility.WriteLog(string.Format("Error in InsertUpdate_EmployeeMaster: Error ({0}) ", ex.Message)); } return(retValue); }