public static List <ExpenseVoucher> GetAll_TravelVoucherFromSharePoint(string siteUrl, string listName, string DaysDifference)
{
List <ExpenseVoucher> _retList = new List <ExpenseVoucher>();
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;
// Temp
//////camlQuery.ViewXml = @"<View>
////// <Query>
////// <Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>6118</Value></Eq></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='FunctionalHead'/>
////// <FieldRef Name='SequenceNo'/>
////// <FieldRef Name='CreationDate'/>
////// <FieldRef Name='AssignDate'/>
////// <FieldRef Name='TotalCost'/>
////// <FieldRef Name='EmployeeName'/>
////// <FieldRef Name='EmployeeNumber'/>
////// <FieldRef Name='Designation'/>
////// <FieldRef Name='ActionTaken'/>
////// <FieldRef Name='CompanyCode'/>
////// <FieldRef Name='DivisionName'/>
////// <FieldRef Name='Modified'/>
////// <FieldRef Name='CurrentApprove'/>
////// <FieldRef Name='AccountApprover'/>
////// <FieldRef Name='AccountUserApproved'/>
////// </ViewFields></View>";
camlQuery.ViewXml = @"<View>
<Query>
<Where>
<And>
<Eq>
<FieldRef Name='StatusCode'/>
<Value Type='Text'>P</Value>
</Eq>
<Leq><FieldRef Name='Modified'/><Value Type='DateTime'>" + dataDateValue.ToString("o") + "</Value></Leq>";
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='FunctionalHead'/>
<FieldRef Name='SequenceNo'/>
<FieldRef Name='CreationDate'/>
<FieldRef Name='AssignDate'/>
<FieldRef Name='TotalCost'/>
<FieldRef Name='EmployeeName'/>
<FieldRef Name='EmployeeNumber'/>
<FieldRef Name='Designation'/>
<FieldRef Name='ActionTaken'/>
<FieldRef Name='CompanyCode'/>
<FieldRef Name='DivisionName'/>
<FieldRef Name='Modified'/>
<FieldRef Name='CurrentApprove'/>
<FieldRef Name='AccountApprover'/>
<FieldRef Name='AccountUserApproved' />
</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 ExpenseVoucher
{
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"]),
CurrentApprove = item["CurrentApprove"] == null ? "" : Convert.ToString((item["CurrentApprove"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId),
AccountApprover = item["AccountApprover"] == null ? "" : Convert.ToString((item["AccountApprover"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
AccountUserApproved = item["AccountUserApproved"] == null ? "" : Convert.ToString(item["AccountUserApproved"])
// AccountUserApproved = Convert.ToString(item["AccountUserApproved"])
});
}
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 <ExpenseVoucher> 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++)
{
var updateList = updationList.Skip(i).Take(1).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")
{
if (updateItem.CurrentApprove == "")
{
break;
}
else
{
_To = updateItem.CurrentApprove;
}
}
else if (updateItem.SequenceNo == "3")
{
if (updateItem.AccountUserApproved == "")
{
break;
}
else
{
_To = updateItem.AccountUserApproved;
}
}
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> Expense 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;
if (updateItem.SequenceNo == "1")
{
_Body += "<br><b>Status :</b> Pending With Functional Head";
}
else if (updateItem.SequenceNo == "2")
{
_Body += "<br><b>Status :</b> Pending With Intenal Audit";
}
else if (updateItem.SequenceNo == "3")
{
_Body += "<br><b>Status :</b> Pending With Accounts";
}
_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/ExpenseVoucher/SitePages/PendingFunctionalHead.aspx\">View Link</a>";
}
else if (updateItem.SequenceNo == "2")
{
_Body += "<br><a href=\"https://aparindltd.sharepoint.com/ExpenseVoucher/SitePages/Pending%20Request.aspx\">View Link</a>";
}
else if (updateItem.SequenceNo == "3")
{
_Body += "<br><a href=\"https://aparindltd.sharepoint.com/ExpenseVoucher/SitePages/PendingRequestAccounts.aspx\">View Link</a>";
}
//data.MailTo = _From;
//data.MailTo = _To;
//data.MailCC = _Cc;
//data.MailSubject = _Subject;
//data.MailBody = _Body;
//varx.Add(data);
listItem["ToUser"] = _To;
listItem["SubjectDesc"] = _Subject;
listItem["BodyDesc"] = _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);
}