1.PNP framework
2. OfficeDevpnpcore (sharepoint online auth)
Wednesday, November 30, 2022
CAML build + report + mail
public class Reports
{
static string clientID = ConfigurationManager.AppSettings["ClientId"];
static string clientSecret = ConfigurationManager.AppSettings["ClientSecret"];
static string SiteUrl = ConfigurationManager.AppSettings["SiteURL"];
static string EmailHost = ConfigurationManager.AppSettings["EmailHost"];
public static List<ReportItems> FetchReportList()
{
List<ReportItems> reportslist = null;
try
{
System.Net.ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(delegate { return true; });
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls | System.Net.SecurityProtocolType.Tls11 | System.Net.SecurityProtocolType.Tls12;
using (var clientContext = GetModernClientContext(SiteUrl))//new ClientContext(SiteUrl))
{
reportslist = new List<ReportItems>();
List reportList = clientContext.Web.Lists.GetByTitle(Constants.LIST_NAME_REPORTROLE_MAPPING);
var query = CamlQuery.CreateAllItemsQuery();
ListItemCollection reportItems = reportList.GetItems(query);
clientContext.Load(reportItems);
clientContext.ExecuteQuery();
foreach (ListItem item in reportItems)
{
if (Convert.ToBoolean(item["Active"]))
{
ReportItems rptObj = new ReportItems();
rptObj.ListItemId = Convert.ToInt32(item["ID"]);
rptObj.emailSubject = Convert.ToString(item["Email_Subject"]);
rptObj.actionOwnerRole = GetCSVstringValues((string[])(item["Role"]));
rptObj.actionOwner = GetListFieldUserValues(((FieldUserValue[])item["ReportOwner"]));
rptObj.activityType = GetCSVstringValues((string[])(item["Activity_x0020_Type"]));
rptObj.bluebook = GetCSVstringValues((string[])(item["Bluebook"]));
rptObj.blackbook = GetCSVstringValues((string[])(item["Blackbook"]));
rptObj.greenbook = GetCSVstringValues((string[])(item["Greenbook"]));
rptObj.statusUpdateRole = GetCSVstringValues((string[])(item["Status_x0020_Update_x0020_by_x00"]));
rptObj.statusUpdateOwner = GetListFieldUserValues(((FieldUserValue[])item["Status_x0020_Update_x0020_by_x000"]));
rptObj.reportWindowText = Convert.ToString(item["Report_x0020_Window"]);
rptObj.reportWindowVal = GetReportWindowValue(Convert.ToString(item["Report_x0020_Window"]));
rptObj.highLowfilter = GetCSVstringValues((string[])(item["High_x002f_Low_x0020_light_x0020"]));
rptObj.Priority = Convert.ToString(item["Priority_x0020_High"]);
rptObj.includeActvtySmmry = Convert.ToString(item["Include_x0020_Summary"]);
rptObj.MailFrequency = Convert.ToString(item["Mail_Frequency"]);
rptObj.MailSendingDay = Convert.ToString(item["Mail_Sending_Day"]);
rptObj.IsReportActive = Convert.ToString(item["Active"]);
rptObj.Bi_WeeklySendDate = Convert.ToString(item["Bi_WeeklySendDate"]);
rptObj.Bi_MonthlySendDate = Convert.ToString(item["Bi_MonthlySendDate"]);
rptObj.reportRecipientsEmail = GetEmailForUsersForSend(((FieldUserValue[])item["Report_x0020_Recipient"]));
reportslist.Add(rptObj);
}
}
}
}
catch (Exception ex)
{
ErrorLog(ex.StackTrace, ex.Message, ex.Source, "WeeklyReport", Convert.ToString(ex.InnerException), "");
Console.WriteLine(ex.Message + "-----" + ex.InnerException);
Console.ReadLine();
}
return reportslist;
}
public static void GetAllDataAndSendReport()
{
Console.WriteLine("Processing Fetching Report List...");
List<ReportItems> reportslist = FetchReportList();
int i = 0;
foreach (ReportItems report in reportslist)
{
i++;
Console.WriteLine("Generating Caml Qryfor report and query on BD AT list..");
string query = GenerateCamlQry(report);
DataTable dtActions = GetActionsByCategory(query);
Console.WriteLine("Processing Data from BD AT list Report Email subject as :" + report.emailSubject + " + " + i + " for " + dtActions.Rows.Count + " records ...");
if (dtActions.Rows.Count > 0)
{
var reportHtml = ConsolidateReportFromListData(dtActions, report);
}
}
}
public static string GetListFieldUserValues(FieldUserValue[] values)
{
string fnlValue = string.Empty;
if (values != null)
{
foreach (FieldUserValue value in values)
{
try
{
fnlValue += value.LookupValue + ',';
}
catch (Exception ex)
{
continue;
}
}
if (!string.IsNullOrEmpty(fnlValue))
fnlValue = fnlValue.Substring(0, fnlValue.Length - 1);
}
return fnlValue;
}
public static string GetCSVstringValues(string[] values)
{
string fnlValue = string.Empty;
if (values != null)
{
foreach (string value in values)
{
fnlValue += value + ',';
}
if (!string.IsNullOrEmpty(fnlValue))
fnlValue = fnlValue.Substring(0, fnlValue.Length - 1);
}
return fnlValue;
}
public static string GetEmailForUsersForSend(FieldUserValue[] values)
{
string fnlValue = string.Empty;
if (values != null)
{
foreach (FieldUserValue value in values)
{
try
{
fnlValue += value.Email + ';';
}
catch (Exception ex)
{
continue;
}
}
if (!string.IsNullOrEmpty(fnlValue))
fnlValue = fnlValue.Substring(0, fnlValue.Length - 1);
}
return fnlValue;
}
//public static void GetReportUserEmail(FieldUserValue[] values)
//{
// if (values != null)
// {
// userInfoList = new List<UserInfo>();
// UserInfo userInfo = new UserInfo();
// foreach (FieldUserValue value in values)
// {
// try
// {
// userInfo.Name = value.LookupValue;
// userInfo.Email = value.Email;
// userInfoList.Add(userInfo);
// }
// catch (Exception ex)
// {
// continue;
// }
// }
// }
//}
public static string GenerateCamlQry(ReportItems rptParam)
{
try
{
var Roles = rptParam.actionOwnerRole != null ? rptParam.actionOwnerRole.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : null;
var Owners = rptParam.actionOwner != null ? rptParam.actionOwner.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : null;
var ProjectNames = rptParam.bluebook != null ? rptParam.bluebook.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : null;
var ProjectTypes = rptParam.actiontype != null ? rptParam.actiontype.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : null;
var Regions = rptParam.blackbook != null ? rptParam.blackbook.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : null;
var Customers = rptParam.customer != null ? rptParam.customer.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : null;
var GreenBooks = rptParam.greenbook != null ? rptParam.greenbook.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : null;
//Added newly(6-9-22)
var HighLowfilter = rptParam.highLowfilter != null ? rptParam.highLowfilter.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : null;
var priority = rptParam.Priority != null ? rptParam.Priority.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : null;
// var roleConditions = new List<Expression<Func<Microsoft.SharePoint.Client.ListItem, bool>>>();
string OuterQry = "<Where>{0}</Where>";
List<string> totalQry = new List<string>();
totalQry.Add(BuildQuery(Roles, "ActionOwner_x0020_Role", "OR", "CONTAINS"));
totalQry.Add(BuildQuery(Owners, "ProjectOwner", "OR", "CONTAINS", "Text", "Flagged_x0020_By"));
totalQry.Add(BuildQuery(ProjectNames, "ProjectName", "OR", "CONTAINS"));
totalQry.Add(BuildQuery(ProjectTypes, "ProjectType", "OR", "CONTAINS"));
totalQry.Add(BuildQuery(Regions, "Region", "OR", "CONTAINS"));
// commented this block as Adam request(9-Nov-22)
// totalQry.Add(BuildQuery(Regions, "ProjectName", "OR", "CONTAINS"));
totalQry.Add(BuildQuery(Customers, "Customer_x0020_Short_x0020_Name", "OR", "CONTAINS", "Note"));
totalQry.Add(BuildQuery(GreenBooks, "GreenBook", "OR", "CONTAINS", "Note"));
// newly added block
//string[] priority = { "High" };
//if (HighLowfilter.Contains("PriorityHigh"))
// totalQry.Add(BuildQuery(priority, "Priority", "OR", "CONTAINS"));
//var newHighLowfilter = HighLowfilter.Where(o => o != "PriorityHigh").ToArray();
// totalQry.Add(BuildQuery(newHighLowfilter, "EOB_Report", "OR", "CONTAINS")); // newly added
totalQry.Add(BuildQuery(priority, "Priority", "OR", "CONTAINS")); // newly added
totalQry.Add(BuildQuery(HighLowfilter, "EOB_Report", "OR", "CONTAINS")); // newly added
totalQry.Add(BuildQuery(new string[] { "Open" }, "Status", "OR", "EQ"));
totalQry = totalQry.Where(s => !string.IsNullOrEmpty(s)).Distinct().ToList();
StringBuilder QryBuilder = new StringBuilder();
int firstNonEmty = 0;
var TagCount = totalQry.Count;
var NANDTagStart = "<And>";
var NANDTagClose = "</And>";
var qry = "";
for (int inx = 1; inx <= TagCount; inx++)
{
if (TagCount >= 2)
{
if (inx > 2)
{
qry = (NANDTagStart + qry);
qry = qry + (totalQry.ElementAt(inx - 1) + NANDTagClose);
}
else if (inx == 2)
{
qry = qry + (totalQry.ElementAt(inx - 1) + NANDTagClose);
}
else if (inx == 1)
{
qry = qry + (NANDTagStart + totalQry.ElementAt(inx - 1));
}
}
else
{
qry = qry + (totalQry.ElementAt(inx - 1));
}
}
return string.Format(OuterQry, qry);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable GetActionsByCategory(string dyQuery)
{
DataTable dtActionsReviews = new DataTable();
try
{
using (ClientContext context = GetModernClientContext(SiteUrl))
// var spContext = SharePointContextProvider.Current.GetSharePointContext(httpcontext);
//using (ClientContext context = spContext.CreateUserClientContextForSPHost())
{
CamlQuery camlQuery = new CamlQuery();
string Query = @"<View><Query>";
Query += dyQuery;
Query += @"<OrderBy><FieldRef Name='EOB_Report' Ascending='True' /><FieldRef Name='Priority' Ascending='True' /><FieldRef Name='Due_x0020_Date' Ascending='False' /><FieldRef Name='Region' Ascending='True' /></OrderBy> </Query>
<ViewFields><FieldRef Name='ID' /><FieldRef Name='ProjectName' /><FieldRef Name='Project_category' /><FieldRef Name='ProjectType' /><FieldRef Name='Action_x0020_Title' />
<FieldRef Name='ActionType' /><FieldRef Name='ProjectOwner'/><FieldRef Name='Owner' /><FieldRef Name='Status' /><FieldRef Name='Status_x0020_Updates' />
<FieldRef Name='Title' /><FieldRef Name='Wafer_x0020_Size' /><FieldRef Name='Region' /><FieldRef Name='Customer_x0020_Short_x0020_Name' />
<FieldRef Name='Due_x0020_Date' /><FieldRef Name='EOB_Report' /><FieldRef Name='Priority'/><FieldRef Name='Impact1' />
<FieldRef Name='Incremental_x0020__x0024_' /><FieldRef Name='Incremental_x0020__x0024__x0020_' /><FieldRef Name='Incremental_x0020_Own_x0020_Valu' /><FieldRef Name='ActionOwner_x0020_Role' /><FieldRef Name='Summary' /> </ViewFields></View>";
camlQuery.ViewXml = Query;
Microsoft.SharePoint.Client.List lstProjects = context.Web.Lists.GetByTitle(Constants.LIST_NAME_MDPSBU);
Microsoft.SharePoint.Client.ListItemCollection listItems = lstProjects.GetItems(camlQuery);
context.Load(listItems);
context.ExecuteQuery();
if (listItems != null && listItems.Count > 0)
{
foreach (var field in listItems[0].FieldValues.Keys)
{
dtActionsReviews.Columns.Add(field);
}
dtActionsReviews.Columns.Add("Impact");
foreach (var item in listItems)
{
DataRow dr = dtActionsReviews.NewRow();
foreach (var obj in item.FieldValues)
{
if (obj.Value != null)
{
string key = obj.Key;
string type = obj.Value.GetType().FullName;
if (type == "Microsoft.SharePoint.Client.FieldLookupValue")
{
dr[obj.Key] = ((FieldLookupValue)obj.Value).LookupValue;
}
else if (type == "Microsoft.SharePoint.Client.FieldUserValue")
{
dr[obj.Key] = ((FieldUserValue)obj.Value).LookupValue;
}
else if (type == "Microsoft.SharePoint.Client.FieldUserValue[]")
{
FieldUserValue[] multValue = (FieldUserValue[])obj.Value;
foreach (FieldUserValue fieldUserValue in multValue)
{
dr[obj.Key] += (fieldUserValue).LookupValue;
}
}
else if (type == "System.DateTime")
{
if (obj.Value.ToString().Length > 0)
{
var date = obj.Value.ToString().Split(' ');
if (date[0].Length > 0)
{
//dr[obj.Key] = date[0];
dr[obj.Key] = DateTime.Parse(date[0]);
}
}
}
else
{
dr[obj.Key] = obj.Value;
}
}
else
{
dr[obj.Key] = null;
}
}
if (Convert.ToString(item["Impact1"]) == "Revenue")
{
dr["Impact"] = item["Incremental_x0020__x0024__x0020_"];
}
else if (Convert.ToString(item["Impact1"]) == "CSA Tool")
{
dr["Impact"] = item["Incremental_x0020__x0024_"];
}
else
{
dr["Impact"] = "";
}
dtActionsReviews.Rows.Add(dr);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return dtActionsReviews;
}
private static string ConsolidateReportFromListData(DataTable ProjectDetails, ReportItems myObj)
{
try
{
using (ClientContext context = GetModernClientContext(SiteUrl))
{
DataTable ProjectConfig = GetProjectConfig(context);
Dictionary<string, int> ProjectTypes = GetActionHeader(ProjectConfig);
Dictionary<string, Dictionary<string, int>> ActionTypes = GetActionTypes(ProjectConfig, ProjectTypes);
Dictionary<string, Dictionary<string, Dictionary<string, DataTable>>> ActionTracker = GetActionTracker(ProjectDetails, ActionTypes);
List<string> listActionId = ProjectDetails.Rows.OfType<DataRow>().Select(dr => dr.Field<string>("ID")).Distinct().ToList();
string inqry = BuildCAMLInQuery(listActionId, "ActionID", "Number");
DataTable dtStatus = GetAllActivityStatus(myObj, inqry, context);
//string MailBody = GetMailBody(ActionTracker, context, myObj);
string MailBody = string.Empty;
if (dtStatus.Rows.Count > 0)
{
MailBody = GetMailBody(ActionTracker, context, myObj, dtStatus);
SendReport(MailBody, context, myObj);
}
return MailBody;
}
}
catch (Exception ex)
{
throw ex;
}
}
private static DataTable GetProjectConfig(ClientContext clientContext)
{
DataTable ProjConfig = new DataTable();
try
{
#region Querying to get Project Config
#region Variables
CamlQuery camlQuery = new CamlQuery();
string Query = string.Empty;
#endregion
#region Direct Query
Query = @"<View><Query><Where><And>
<Or><Contains>
<FieldRef Name='ProjectCategory' /><Value Type='Text'>Bluebook</Value></Contains>
<Contains><FieldRef Name='ProjectCategory' /><Value Type='Text'>Global Programs</Value></Contains>
</Or>
<Eq><FieldRef Name='IsActive'/><Value Type='Number'>1</Value></Eq></And></Where>
<OrderBy><FieldRef Name='ProjectName' Ascending='TRUE' />
<FieldRef Name='ActionTypeOrder' Ascending='TRUE' /></OrderBy></Query>
<ViewFields><FieldRef Name='ProjectName' /><FieldRef Name='ProjectCategory' /><FieldRef Name='ActionType' />
<FieldRef Name='ProjectType' /><FieldRef Name='SubCategory' /><FieldRef Name='Priority' />
<FieldRef Name='ProCatePriority' /><FieldRef Name='ActionTypeOrder' /></ViewFields></View>";
#endregion
#region Executing Query to get Project Confg
camlQuery.ViewXml = Query;
Microsoft.SharePoint.Client.List lstProjectsConfig = clientContext.Web.Lists.GetByTitle(Constants.LIST_NAME_PROJTYPECONFIG);
Microsoft.SharePoint.Client.ListItemCollection listItems = lstProjectsConfig.GetItems(camlQuery);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
if (listItems != null && listItems.Count > 0)
{
// Create Datatable Columns
foreach (var field in listItems[0].FieldValues.Keys)
{
ProjConfig.Columns.Add(field);
}
// Adding items into Datatable
foreach (var item in listItems)
{
DataRow dr = ProjConfig.NewRow();
foreach (var obj in item.FieldValues)
{
if (obj.Value != null)
{
string key = obj.Key;
string type = obj.Value.GetType().FullName;
if (type == "Microsoft.SharePoint.Client.FieldLookupValue")
dr[obj.Key] = ((FieldLookupValue)obj.Value).LookupValue;
else if (type == "Microsoft.SharePoint.Client.FieldUserValue")
dr[obj.Key] = ((FieldUserValue)obj.Value).LookupValue;
else if (type == "Microsoft.SharePoint.Client.FieldUserValue[]")
{
FieldUserValue[] multValue = (FieldUserValue[])obj.Value;
foreach (FieldUserValue fieldUserValue in multValue)
{
dr[obj.Key] += (fieldUserValue).LookupValue;
}
}
else if (type == "System.DateTime")
{
if (obj.Value.ToString().Length > 0)
{
var date = obj.Value.ToString().Split(' ');
if (date[0].Length > 0)
{
dr[obj.Key] = date[0];
}
}
}
else
dr[obj.Key] = obj.Value;
}
else
dr[obj.Key] = null;
}
ProjConfig.Rows.Add(dr);
}
}
#endregion
#endregion
}
catch (Exception ex)
{
throw ex;
}
return ProjConfig;
}
private static Dictionary<string, int> GetActionHeader(DataTable Config)
{
DataTable DistinctProjectTypes = new DataTable();
DistinctProjectTypes.Columns.Add("ProjectType");
DistinctProjectTypes.Columns.Add("Priority");
DistinctProjectTypes = Config.DefaultView.ToTable(true, "ProjectType", "Priority");
DataView TmpView = new DataView(DistinctProjectTypes);
TmpView.RowFilter = "LEN(ISNULL(ProjectType,''))<>0";
//TmpView.RowStateFilter = DataViewRowState.ModifiedCurrent;
TmpView.Sort = "Priority ASC";
DistinctProjectTypes = TmpView.ToTable();
Dictionary<string, int> ProjectType = new Dictionary<string, int>();
try
{
ProjectType = DistinctProjectTypes.AsEnumerable().ToDictionary<DataRow, string, int>(row => row.Field<string>("ProjectType"),
row => Convert.ToInt32(row.Field<string>("Priority")));
}
catch (Exception ex)
{
throw ex;
}
return ProjectType;
}
private static Dictionary<string, Dictionary<string, int>> GetActionTypes(DataTable Config, Dictionary<string, int> ProjectTypes)
{
Dictionary<string, Dictionary<string, int>> ActionTypes = new Dictionary<string, Dictionary<string, int>>();
try
{
Dictionary<string, int> ActnTyps = new Dictionary<string, int>();
DataTable DistinctActionTypes = new DataTable();
DataTable FilteredRows = new DataTable();
DataView TmpView = new DataView();
string ProjectType = string.Empty;
foreach (KeyValuePair<string, int> myKYP in ProjectTypes)
{
ProjectType = Convert.ToString(myKYP.Key);
var dataRow = Config.AsEnumerable()
.Where(r => r.Field<string>("ProjectType") != null && r.Field<string>("ProjectType").Equals(ProjectType));
if (dataRow.Count<DataRow>() > 0)
FilteredRows = dataRow.CopyToDataTable<DataRow>();
DistinctActionTypes = FilteredRows.DefaultView.ToTable(true, "ActionType", "ActionTypeOrder");
TmpView = new DataView(DistinctActionTypes);
TmpView.Sort = "ActionTypeOrder ASC";
DistinctActionTypes = TmpView.ToTable();
ActnTyps = DistinctActionTypes.AsEnumerable().ToDictionary<DataRow, string, int>(row => row.Field<string>("ActionType"),
row => Convert.ToInt32(row.Field<string>("ActionTypeOrder")));
if (ActnTyps.Count > 0)
ActionTypes.Add(ProjectType, ActnTyps);
}
}
catch (Exception ex)
{
throw ex;
}
return ActionTypes;
}
private static Dictionary<string, Dictionary<string, Dictionary<string, DataTable>>> GetActionTracker(DataTable Requests, Dictionary<string, Dictionary<string, int>> ActionTypes)
{
Dictionary<string, Dictionary<string, DataTable>> ActionTracker = new Dictionary<string, Dictionary<string, DataTable>>();
Dictionary<string, Dictionary<string, Dictionary<string, DataTable>>> ActionTrackerFinal = new Dictionary<string, Dictionary<string, Dictionary<string, DataTable>>>();
try
{
Dictionary<string, DataTable> ActnTrkr = new Dictionary<string, DataTable>();
DataTable Tracker = new DataTable();
string HeadRow = string.Empty;
string H1 = string.Empty;
string H2 = string.Empty;
List<string> HighLightLowLight = new List<string>();
HighLightLowLight.Add("All");
DataTable dtRegion = GetRegion();
for (int i = 0; i < dtRegion.Rows.Count; i++)
{
HighLightLowLight.Add(dtRegion.Rows[i]["BLACK_BK_REGION"].ToString());
}
//HighLightLowLight.Add("AMC");
//HighLightLowLight.Add("AME");
//HighLightLowLight.Add("AMJ");
//HighLightLowLight.Add("AMK");
//HighLightLowLight.Add("AMNA");
//HighLightLowLight.Add("AMSEA");
//HighLightLowLight.Add("AMT");
HighLightLowLight.Add("--Select--");
string InnerTitle = string.Empty;
DataTable FilteredRows = new DataTable();
Dictionary<string, int> Actn = new Dictionary<string, int>();
foreach (KeyValuePair<string, Dictionary<string, int>> Head1 in ActionTypes)
{
ActionTracker = new Dictionary<string, Dictionary<string, DataTable>>();
H1 = Convert.ToString(Head1.Key);
Actn = Head1.Value;
foreach (string HlLl in HighLightLowLight)
{
ActnTrkr = new Dictionary<string, DataTable>();
//FilteredRows = new DataTable();
//FilteredRows = Requests.Clone();
foreach (KeyValuePair<string, int> Head2 in Actn)
{
H2 = Convert.ToString(Head2.Key);
InnerTitle = Convert.ToString(Head2.Key);// + "-" + HlLl;
var dataRow = Requests.AsEnumerable()
.Where(r => r.Field<string>("ProjectType") != null && r.Field<string>("ProjectType").Equals(H2)
&& r.Field<string>("Region") != null && r.Field<string>("Region").Equals(HlLl));
if (dataRow.Count<DataRow>() > 0)
{
FilteredRows = dataRow.CopyToDataTable<DataRow>();
ActnTrkr.Add(InnerTitle, FilteredRows);
#region Reference
//ActnTrkr.Add(H2, FilteredRows);
//if (dataRow.Any())
// FilteredRows = dataRow.CopyToDataTable<DataRow>();
//else
// FilteredRows = Requests.Clone();
//foreach (var row in dataRow)
// FilteredRows.ImportRow(row);
#endregion
}
}
if (HlLl.ToLower().Equals("All"))
HeadRow = H1 + " - " + HlLl + " (All)";
else
HeadRow = H1 + " - " + HlLl;
if (ActnTrkr.Count > 0)
ActionTracker.Add(HeadRow, ActnTrkr);
}
if (ActionTracker.Count > 0)
ActionTrackerFinal.Add(H1, ActionTracker);
}
}
catch (Exception ex)
{
throw ex;
}
return ActionTrackerFinal;
}
private static DataTable GetAllActivityStatus(ReportItems myObj, string AcvtyIdINQry, ClientContext myContext)
{
var statusRoles = myObj.statusUpdateRole.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var statusOwners = myObj.statusUpdateOwner.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var statusRoleQry = BuildQuery(statusRoles, "Creator_x0020_Role", "OR", "CONTAINS");
var statusOwnerQry = BuildQuery(statusOwners, "Author", "OR", "CONTAINS", "User");
Microsoft.SharePoint.Client.List mdpSBUStatusList = myContext.Web.Lists.GetByTitle(Constants.LIST_NAME_STATUSUPDATE);
CamlQuery camlQuery = new CamlQuery();
string currentDate = string.Empty;
string previousDate = string.Empty;
string statusAllQry = @"<View><Query><Where>";
if (myObj.reportWindowText != "All")
{
currentDate = DateTime.Now.ToString("yyyy-MM-dd");
previousDate = DateTime.Now.AddDays(Convert.ToDouble(myObj.reportWindowVal)).ToString("yyyy-MM-dd");
statusAllQry = statusAllQry + @"<And><And>";
if (!string.IsNullOrEmpty(statusRoleQry))
{
statusAllQry = statusAllQry + @"<And>";
if (!string.IsNullOrEmpty(statusOwnerQry))
{
statusAllQry = statusAllQry + @"<And>";
if (statusRoleQry.Contains("BD"))
{
statusAllQry = statusAllQry + statusRoleQry + AcvtyIdINQry + @"</And>";
statusAllQry = statusAllQry + "<Leq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + currentDate + "</Value></Leq></And>"
+ "<Geq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + previousDate + "</Value></Geq></And>" + statusOwnerQry + "</And></Where>";
}
else
{
statusAllQry = statusAllQry + "<Leq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + currentDate + "</Value></Leq>"
+ "<Geq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + previousDate + "</Value></Geq>"
+ "</And>"
+ AcvtyIdINQry + "</And> " + statusRoleQry + "</And>" + statusOwnerQry + "</And></Where>";
}
}
else
{
if (statusRoleQry.Contains("BD"))
{
statusAllQry = statusAllQry + statusRoleQry + AcvtyIdINQry + @"</And>";
statusAllQry = statusAllQry + "<Leq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + currentDate + "</Value></Leq></And>"
+ "<Geq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + previousDate + "</Value></Geq></And></Where>";
}
else
{
statusAllQry = statusAllQry + "<Leq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + currentDate + "</Value></Leq>"
+ "<Geq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + previousDate + "</Value></Geq>"
+ "</And>"
+ AcvtyIdINQry + "</And> " + statusRoleQry + "</And></Where>";
}
}
}
else
{
if (!string.IsNullOrEmpty(statusOwnerQry))
{
statusAllQry = statusAllQry + @"<And>";
statusAllQry = statusAllQry + "<Leq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + currentDate + "</Value></Leq>"
+ "<Geq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + previousDate + "</Value></Geq>"
+ "</And>"
+ AcvtyIdINQry + "</And>" + statusOwnerQry + "</And></Where>";
}
else
{
statusAllQry = statusAllQry + "<Leq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + currentDate + "</Value></Leq>"
+ "<Geq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + previousDate + "</Value></Geq>"
+ "</And>"
+ AcvtyIdINQry + "</And></Where>";
}
}
}
else
{
if (!string.IsNullOrEmpty(statusRoleQry))
{
if (statusRoleQry.Contains("BD"))
{
// as BD Role check for NULL values as Default value
statusAllQry = statusAllQry + @"<And>";
if (!string.IsNullOrEmpty(statusOwnerQry))
{
statusAllQry = statusAllQry + @"<And>";
statusAllQry = statusAllQry + statusRoleQry + AcvtyIdINQry + @"</And>" + statusOwnerQry + "</And></Where>";
}
else
{
statusAllQry = statusAllQry + statusRoleQry + AcvtyIdINQry + @"</And></Where>";
}
}
else
{
statusAllQry = statusAllQry + @"<And>";
if (!string.IsNullOrEmpty(statusOwnerQry))
{
statusAllQry = statusAllQry + @"<And>";
statusAllQry = statusAllQry + AcvtyIdINQry + statusRoleQry + "</And>" + statusOwnerQry + "</And></Where>";
}
else
{
statusAllQry = statusAllQry + AcvtyIdINQry + statusRoleQry + "</And></Where>";
}
}
}
else
{
if (!string.IsNullOrEmpty(statusOwnerQry))
{
statusAllQry = statusAllQry + @"<And>";
statusAllQry = statusAllQry + AcvtyIdINQry + statusOwnerQry + "</And></Where>";
}
else
{
statusAllQry = statusAllQry + AcvtyIdINQry + "</Where>";
}
}
}
statusAllQry = statusAllQry + "<OrderBy><FieldRef Name='ActionID' Ascending='TRUE' /><FieldRef Name='Created' Ascending='FALSE' /></OrderBy></Query></View>";
camlQuery.ViewXml = statusAllQry;
Microsoft.SharePoint.Client.ListItemCollection listItems = mdpSBUStatusList.GetItems(camlQuery);
myContext.Load(listItems);
myContext.ExecuteQuery();
DataTable StatusDetails = new DataTable();
if (listItems != null && listItems.Count > 0)
{
// Creating Datatable Columns
foreach (var field in listItems[0].FieldValues.Keys)
{
StatusDetails.Columns.Add(field);
}
// Adding Records into Datatable
foreach (var item in listItems)
{
DataRow dr = StatusDetails.NewRow();
foreach (var obj in item.FieldValues)
{
if (obj.Value != null)
{
string key = obj.Key;
string type = obj.Value.GetType().FullName;
if (type == "Microsoft.SharePoint.Client.FieldLookupValue")
dr[obj.Key] = ((FieldLookupValue)obj.Value).LookupValue;
else if (type == "Microsoft.SharePoint.Client.FieldUserValue")
dr[obj.Key] = ((FieldUserValue)obj.Value).LookupValue;
else if (type == "Microsoft.SharePoint.Client.FieldUserValue[]")
{
FieldUserValue[] multValue = (FieldUserValue[])obj.Value;
foreach (FieldUserValue fieldUserValue in multValue)
{
dr[obj.Key] += (fieldUserValue).LookupValue + ";";
}
}
else if (type == "System.DateTime")
{
if (obj.Value.ToString().Length > 0)
{
var date = obj.Value.ToString().Split(' ');
if (date[0].Length > 0)
{
dr[obj.Key] = date[0];
}
}
}
else
{
if (obj.Key.Equals("StatusUpdates"))
{
dr[obj.Key] = Regex.Replace(Convert.ToString(obj.Value), "<.*?>", string.Empty);
}
else
{
dr[obj.Key] = obj.Value;
}
}
}
else
dr[obj.Key] = null;
}
StatusDetails.Rows.Add(dr);
}
}
return StatusDetails;
}
private static string GetMailBody(Dictionary<string, Dictionary<string, Dictionary<string, DataTable>>> RptSource, ClientContext myContext, ReportItems myObj, DataTable StatusTbl)
{
#region Variables
string MailBody = string.Empty;
string Colspan = "7";
string SiteUrl = ConfigurationManager.AppSettings["SiteURL"];
int rptTotalRowPrinted = 0;
#endregion
try
{
#region HTML Content Start
StringBuilder CustomMailBody = new StringBuilder();
if (!myObj.viewReport)
{
CustomMailBody.AppendLine("<!DOCTYPE html>");
//CustomMailBody.AppendLine("<meta name='viewport' content='width=device-width, initial-scale=1.0'>");
CustomMailBody.AppendLine("<html>");
CustomMailBody.AppendLine("<head>");
}
CustomMailBody.AppendLine("<style>");
CustomMailBody.AppendLine("table{border-collapse: collapse;table-layout:fixed;font-size:13px;}");
// CustomMailBody.AppendLine("table, td, th{border: 1px solid #999;}");
CustomMailBody.AppendLine("td, th{overflow: hidden;text-align: left;}");
CustomMailBody.AppendLine("</style>");
if (!myObj.viewReport)
{
CustomMailBody.AppendLine("</head>");
CustomMailBody.AppendLine("<body style='font-family:calibri;font-size:14px;width:100%'>");
}
#endregion
#region Dynamic Contents
#region Variable Declaration
string ActionID = string.Empty;
string ActionOwnerRole = string.Empty;
string ActionTitle = string.Empty;
string ProjectType = string.Empty;
string Priority = string.Empty;
string StatusUpdate = string.Empty;
string ActionOwner = string.Empty;
string Region = string.Empty;
string Customer = string.Empty;
string EOB_Report = string.Empty;
string ActionSummary = string.Empty;
string DetailsURI = string.Empty;
string HistoryURI = string.Empty;
string Details = string.Empty;
string History = string.Empty;
string[] CustomerArray = { "" };
char[] SplitChar = { ',' };
string NewCustomer = string.Empty;
DetailsURI = SiteUrl + "SitePages/ActionNew.aspx";
HistoryURI = SiteUrl + "SitePages/History.aspx";
string OptionalHeader = string.Empty;
string H1 = string.Empty;
string H2 = string.Empty;
string Head1 = string.Empty;
string Head2 = string.Empty;
string[] tmpArray = { "" };
string tmp1 = string.Empty;
string tmp2 = string.Empty;
string t1Style = string.Empty;
string t2Style = string.Empty;
string[] StatusUpdateArray = { "" };
string tmpStatus = string.Empty;
string NewStatusUpdate = string.Empty;
char[] SplitCharsStatus = { '|' };
#endregion
Dictionary<string, Dictionary<string, DataTable>> Level1 = new Dictionary<string, Dictionary<string, DataTable>>();
Dictionary<string, DataTable> Level2 = new Dictionary<string, DataTable>();
DataTable Tracker = new DataTable();
if (!myObj.viewReport)
{
CustomMailBody.AppendLine("<table cellspacin=0 cellpadding=5 style='border:non' width=\"100%\" >");
CustomMailBody.AppendLine("<tr><td style='border:none;'><img src='https://spqaappweb.amat.com/BDAT_QA/Images/sat_logo.jpg' /></td><td align='right' style='border:none;'><img src='https://spqaappweb.amat.com/BDAT_QA/Images/AMATlogo_blue.jpg' width='150'/></td></tr>");
CustomMailBody.AppendLine("<tr><td colspan='2' style='border:none'><img src='https://spqaappweb.amat.com/BDAT_QA/Images/mail_banner2.png' height='80' width=\"100%\" style='width:100%;height:80px;' /></td></tr>");
CustomMailBody.AppendLine("<tr><td colspan='2' height:'5' style='border:none;'></td></tr>");
CustomMailBody.AppendLine("<tr><td colspan='2' align='right' style='border:none;'>");
//CustomMailBody.AppendLine("<table cellspacing=0 cellpadding=0 width=\"60%\" style='border:none'><tr><td style='border:none;padding:0 3px'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> OWNER </p><p style='padding - left: 10px;'> - " + myObj.actionOwner + " </p></td><td style='border:none;padding:0 3px'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> TYPE </p><p style='padding - left: 10px;'> - " + myObj.actiontype + " </p></td><td style='border:none;padding:0 3px'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> CATEGORY </p><p style='padding - left: 10px;'> - " + myObj.actionCategory + " </p></td><td style='border:none;padding:0 3px'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> REPORT WINDOW </p><p style='padding - left: 10px;'> - " + myObj.reportWindowText + " </p></td><td style='border:none;padding:0 3px'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> REPORT DATE </p><p style='padding - left: 10px;'> " + DateTime.Now.ToString("yyyy-MM-dd") + "</p></td></tr></table>");
CustomMailBody.AppendLine("<table cellspacing=0 cellpadding=0 style='border:none;width: 95%;'><tr><td style='border:none;padding:0 3px;width: 25%;'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> OWNER </p></td><td style='border:none;padding:0 3px;width: 20%;'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> TYPE </p></td><td style='border:none;padding:0 3px;width: 20%;'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> CATEGORY </p></td><td style='border:none;padding:0 3px;width: 20%;'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> REPORT WINDOW </p></td><td style='border:none;padding:0 3px;;width: 15%;'><p style='font-size:18px;font-weight:bold;color:#5c656f;margin:0;'> REPORT DATE </p></td></tr></table>");
CustomMailBody.AppendLine("<table cellspacing=0 cellpadding=0 style='border:none;width: 95%;'><tr><td style='border:none;padding:0 3px;word-break: normal;white-space: pre-wrap;width: 25%;text-align:left;'>" + myObj.actionOwner + "</td><td style='border:none;padding:0 3px;word-break: normal;white-space: pre-wrap;width: 20%;'>" + myObj.actiontype + "</td><td style='border:none;padding:0 3px;word-break: normal;white-space: pre-wrap;;width: 20%;'>" + myObj.bluebook + "</td><td style='border:none;padding:0 3px;word-break: normal;white-space: pre-wrap;;width: 20%;'>" + myObj.reportWindowText + "</td><td style='border:none;padding:0 3px;word-break: normal;white-space: pre-wrap;;width: 15%;'>" + DateTime.Now.ToString("yyyy-MM-dd") + "</td></tr></table>");
CustomMailBody.AppendLine("</td></tr><tr><td colspan='2' align='right' style='border-top:1px solid #ddd;'><div style='font-size:14px;float:right'><span style='font-weight:bold;vertical-align:middle;text-align:center;padding:0px 10px' >⚐ High Priority </span><span style='color:#228B22;font-weight:bold;vertical-align:middle;text-align:center;padding:0px 10px'> * <span style='color:#000;'>Highlight</span></span><span style='color:#a94442;font-weight:bold;vertical-align:middle;text-align:center;padding:0px 10px'> * <span style='color:#000;'>Lowlight</span></span></div></td></tr></table>");
}
else
{
CustomMailBody.AppendLine("<table cellspacin=0 cellpadding=5 style='border:non' width=\"100%\" >");
CustomMailBody.AppendLine("<tr><td style='border:none;'><img src='https://spqaappweb.amat.com/BDAT_QA/Images/sat_logo.jpg' /></td><td align='right' style='border:none;'><img src='https://spqaappweb.amat.com/BDAT_QA/Images/AMATlogo_blue.jpg' width='150'/></td></tr>");
CustomMailBody.AppendLine("<tr><td colspan='2' style='border:none'><img src='https://spqaappweb.amat.com/BDAT_QA/Images/mail_banner2.png' height='80' width=\"100%\" style='width:100%;height:80px;' /></td></tr>");
CustomMailBody.AppendLine("<tr><td colspan='2' height:'5' style='border:none;'><div style='font-size:14px;float:right'><span style='font-weight:bold;vertical-align:middle;text-align:center;padding:0px' >⚐High Priority</span><span style='color:#228B22;font-weight:bold;vertical-align:middle;text-align:center;padding:0px'> *<span style='color:#000;'>Highlight</span></span><span style='color:#a94442;font-weight:bold;vertical-align:middle;text-align:center;padding:0px'> *<span style='color:#000;'>Lowlight</span></span></div></td></tr></table>");
}
foreach (KeyValuePair<string, Dictionary<string, Dictionary<string, DataTable>>> kypOptional in RptSource)
{
OptionalHeader = Convert.ToString(kypOptional.Key).ToUpper();
Level1 = kypOptional.Value;
StringBuilder SectionHeader = new StringBuilder();
Int16 sectionHdrAddCount = 0;
int ActionTypeHdrCnt = 0;
string ActionTypeHeader = string.Empty;
foreach (KeyValuePair<string, Dictionary<string, DataTable>> kypL1 in Level1)
{
H1 = kypL1.Key;
Level2 = kypL1.Value;
tmpArray = H1.Split('-');
tmp1 = tmpArray[0].Trim();
tmp2 = tmpArray[1].Trim();
SectionHeader = new StringBuilder();
sectionHdrAddCount = 0;
ActionTypeHdrCnt = 0;
ActionTypeHeader = string.Empty;
SectionHeader.AppendLine("<h1 style='color:#333;font-size:24px;'>" + H1 + "</h1>"); // Section Header
SectionHeader.AppendLine("<table cellspacing=0 cellpadding=5 width=\"100%\">");
SectionHeader.AppendLine("<tr style='background-color:#388dc0;color:#EFEFEF;font-size:18px;font-weight:bold;'>");
SectionHeader.AppendLine("<th style='width:150px;table-layout:fixed;' >Customer</th>");
//CustomMailBody.AppendLine("<th style='width:100px;table-layout:fixed;'>Region</th>");
SectionHeader.AppendLine("<th style='width:150px;table-layout:fixed;' >Title</th>");
if (myObj.includeActvtySmmry.Equals("false"))
{
SectionHeader.AppendLine("<th style='width:100px;table-layout:fixed;' >Owner Role</th>");
SectionHeader.AppendLine("<th style='width:200px;table-layout:fixed;' >Owner</th>");
}
else
{
SectionHeader.AppendLine("<th style='width:100px;table-layout:fixed;' >Owner</th>");
SectionHeader.AppendLine("<th style='width:200px;table-layout:fixed;' >Summary</th>");
}
/// SectionHeader.AppendLine("<th style='width:100px;table-layout:fixed;'>Date</th>");
SectionHeader.AppendLine("<th style='width:500px;table-layout:fixed;' >Status Update</th>");
SectionHeader.AppendLine("<th style='width:80px;table-layout:fixed;' >Details</th>");
SectionHeader.AppendLine("<th style='width:80px;table-layout:fixed;' >History</th>");
SectionHeader.AppendLine("</tr>");
t1Style = "<span style='color:#686868;background-color:#e9f0f8;'>" + tmp1 + "<span>";
if (tmp2.ToLower() != "All")
t2Style = "<span style='color:#4599c3;background-color:#00FF00;' >" + tmp2 + "<span>";
//else if (tmp2.ToLower().Equals("AME"))
// t2Style = "<span style='color:#D50000;background-color:#e9f0f8;' >" + tmp2 + "<span>"; //#CC0000
else
{
tmp2 = "All";
t2Style = "<span style='color:#333;' >" + tmp2 + "<span>";
}
//Head1 = t1Style + " " + t2Style;
Head1 = t2Style;
//CustomMailBody.AppendLine("<tr><td colspan=" + Colspan + " style='background-color:#e9f0f8;font-size:20px;font-weight:bold;' >" + Head1 + "</td></tr>");
foreach (KeyValuePair<string, DataTable> kypL2 in Level2)
{
H2 = kypL2.Key;
Tracker = kypL2.Value;
tmpArray = H2.Split('-');
Head2 = tmpArray[0];// + " - " + tmp2;
if (sectionHdrAddCount >= 1)
{
SectionHeader = new StringBuilder();
sectionHdrAddCount = 0;
}
if (ActionTypeHdrCnt == 0 && !string.IsNullOrEmpty(ActionTypeHeader))
{
SectionHeader.Replace(ActionTypeHeader, "");
}
SectionHeader.AppendLine("<tr><td colspan=" + Colspan + " style='background-color:#5c656f;font-size:15px;font-weight:bold;color:#EFEFEF;'>" + Head2 + "</td></tr>");
ActionTypeHeader = "<tr><td colspan=" + Colspan + " style='background-color:#5c656f;font-size:15px;font-weight:bold;color:#EFEFEF;'>" + Head2 + "</td></tr>";
var rowCount = 0;
ActionTypeHdrCnt = 0;
foreach (DataRow dr in Tracker.Rows)
{
#region Misc Fields
//ProjectType = Convert.ToString(dr["ProjectType"]);
//Priority = Convert.ToString(dr["Priority"]);
#endregion
ActionID = Convert.ToString(dr["ID"]);
Customer = Convert.ToString(dr["Customer_x0020_Short_x0020_Name"]);
Region = Convert.ToString(dr["Region"]);
ActionTitle = Convert.ToString(dr["Action_x0020_Title"]);
ActionOwnerRole = string.IsNullOrEmpty(dr["ActionOwner_x0020_Role"].ToString()) ? "BD" : Convert.ToString(dr["ActionOwner_x0020_Role"]);
ActionOwner = Convert.ToString(dr["ProjectOwner"]);
StatusUpdate = Convert.ToString(dr["Status_x0020_Updates"]);
EOB_Report = Convert.ToString(dr["EOB_Report"]);
Priority = Convert.ToString(dr["Priority"]);
ActionSummary = Convert.ToString(dr["Summary"]);
Customer = (!string.IsNullOrEmpty(Customer)) ? Customer : " ";
Region = (!string.IsNullOrEmpty(Region)) ? Region : " ";
ActionTitle = (!string.IsNullOrEmpty(ActionTitle)) ? ActionTitle : " ";
StatusUpdate = (!string.IsNullOrEmpty(StatusUpdate)) ? StatusUpdate : " ";
NewCustomer = string.Empty;
CustomerArray = Customer.Split(SplitChar, StringSplitOptions.RemoveEmptyEntries);
foreach (string Cust in CustomerArray)
{
if (NewCustomer.Length > 0)
NewCustomer = NewCustomer + ", " + Cust;
else NewCustomer = Cust;
}
if (StatusUpdate.Contains("|"))
{
//StatusUpdateArray = StatusUpdate.Split(SplitCharsStatus, StringSplitOptions.RemoveEmptyEntries);
StatusUpdateArray = StatusUpdate.Split(SplitCharsStatus);
tmpStatus = Convert.ToString(StatusUpdateArray[2]);
if (tmpStatus.Length > 185)
NewStatusUpdate = tmpStatus.Substring(0, 180) + "...";
else
NewStatusUpdate = tmpStatus;
}
else NewStatusUpdate = StatusUpdate;
if (NewCustomer.Length > 35)
NewCustomer = NewCustomer.Substring(0, 35) + "...";
Details = string.Format("<a href='{0}?ActionID={1}' target='_blank'>Details</a>", DetailsURI, Convert.ToString(ActionID));
History = string.Format("<a href='{0}?ActionID={1}' target='_blank'>History</a>", HistoryURI, Convert.ToString(ActionID));
DataTable StatusAssciatedAction = new DataTable();
DataView TmpView = new DataView(StatusTbl);
TmpView.RowFilter = "ActionID=" + ActionID;
//TmpView.Sort = "Created DESC";
StatusAssciatedAction = TmpView.ToTable();
var CustomSubMailBody = new StringBuilder();
if (StatusAssciatedAction.Rows.Count > 0)
{
sectionHdrAddCount += 1;
ActionTypeHdrCnt += 1;
rptTotalRowPrinted += 1;
if (sectionHdrAddCount == 1)
{
CustomMailBody.Append(SectionHeader.ToString());
}
CustomSubMailBody.AppendLine("<table cellspacing=0 cellpadding=5 width=\"100%\" style='border-left-style: hidden;border-right-style: hidden;border-top-style: hidden;border-bottom-style: hidden'>");
foreach (DataRow item in StatusAssciatedAction.Rows)
{
if (item != null)
{
string date = Convert.ToDateTime(item["Created"]).ToString("MM/dd/yy");
string content = string.Empty;
if (!string.IsNullOrEmpty(Convert.ToString(item["StatusUpdates"])))
{
content = Convert.ToString(item["StatusUpdates"]);
}
string status = content;
string updaterole = string.Equals(item["Creator_x0020_Role"].ToString(), string.Empty) ? "BD" : item["Creator_x0020_Role"].ToString();
var updateUser = item["Author"].ToString();
CustomSubMailBody.AppendLine("<tr valign=middle>");
//CustomSubMailBody.AppendLine("<td style='word-break: break-word;width:15%;table-layout:fixed;' align=center>" + date + "</td>");
CustomSubMailBody.AppendLine("<td style='word-break: break-word;table-layout:fixed;width:75%' align=left><strong>" + date + " : </Strong>" + status + "</td>");
//CustomSubMailBody.AppendLine("<td style='word-break: break-word;table-layout:fixed;width:15%' align=left>" + updaterole + "</td>");
CustomSubMailBody.AppendLine("<td style='word-break: break-word;table-layout:fixed;width:25%' align=left><strong>" + updaterole + "</Strong> | " + updateUser + "</td>");
CustomSubMailBody.AppendLine("</tr>");
}
}
CustomSubMailBody.AppendLine("</table>");
}
//////////////////
if (rowCount++ % 2 == 0 && StatusAssciatedAction.Rows.Count > 0)
{
CustomMailBody.AppendLine("<tr valign=middle align=center style='border-bottom:1px solid #ccc !important;background-color:#e9f0f8;'>");
if (EOB_Report == "Highlight")
{
if (Priority.ToLower() == "high")
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + NewCustomer + "<div style='text-align:center;'><span style='font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;' >⚐</span><span style='color:#228B22;font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;'> * </span></div></td>");
}
else
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + NewCustomer + "<div style='text-align:center;'><span style='color:#228B22;font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;'> * </span></div></td>");
}
}
else if (EOB_Report == "Lowlight")
{
if (Priority.ToLower() == "high")
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + NewCustomer + "<div style='text-align:center;'><span style='font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;' >⚐</span><span style='color:#a94442;font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;'> * </span></div></td>");
}
else
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + NewCustomer + "<div style='text-align:center;'><span style='color:#a94442;font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;'> * </span></div></td>");
}
}
else
{
if (Priority.ToLower() == "high")
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + NewCustomer + "<div style='text-align:center;'><span style='font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;' >⚐</span></div></td>");
}
else
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + NewCustomer + "</td>");
}
}
//CustomMailBody.AppendLine("<td style='word-break: break-word;width:100px;table-layout:fixed;' >" + Region + "</td>");
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + ActionTitle + "</td>");
if (myObj.includeActvtySmmry.Equals("false"))
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:100px;table-layout:fixed;' >" + ActionOwnerRole + "</td>");
CustomMailBody.AppendLine("<td style='word-break: break-word;width:200px;table-layout:fixed;text-align:left;' >" + ActionOwner + "</td>");
}
else
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:100px;table-layout:fixed;text-align:left;' >" + ActionOwner + "</td>");
CustomMailBody.AppendLine("<td style='word-break: break-word;width:200px;table-layout:fixed;text-align:left;' >" + ActionSummary + "</td>");
}
CustomMailBody.AppendLine("<td style='word-break: break-word;width:500px;table-layout:fixed;' align='left' valign='top' >" + CustomSubMailBody + "</td>");
CustomMailBody.AppendLine("<td style='width:80px;table-layout:fixed;' align='center'>" + Details + "</td>");
CustomMailBody.AppendLine("<td style='width:80px;table-layout:fixed;' align='center'>" + History + "</td>");
}
else if (StatusAssciatedAction.Rows.Count > 0)
{
CustomMailBody.AppendLine("<tr valign=middle align=center style='border-bottom:1px solid #ccc !important;'>");
if (EOB_Report == "Highlight")
{
if (Priority.ToLower() == "high")
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + NewCustomer + "<div style='text-align:center;'><span style='font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;' >⚐</span><span style='color:#228B22;font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;'> * </span></div></td>");
}
else
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + NewCustomer + "<div style='text-align:center;'><span style='color:#228B22;font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;'> * </span></div></td>");
}
}
else if (EOB_Report == "Lowlight")
{
if (Priority.ToLower() == "high")
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:center;' >" + NewCustomer + "<div style='text-align:center;'><span style='font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;' >⚐</span><span style='color:#a94442;font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;'> * </span></div></td>");
}
else
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:center;' >" + NewCustomer + "<div style='text-align:center;'><span style='color:#a94442;font-size:20pt;font-weight:bold;vertical-align:middle;text-align:center;'> * </span></div></td>");
}
}
else
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + NewCustomer + "</td>");
}
//CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;' >" + NewCustomer + "</td>");
//CustomMailBody.AppendLine("<td style='word-break: break-word;width:100px;table-layout:fixed;' >" + Region + "</td>");
CustomMailBody.AppendLine("<td style='word-break: break-word;width:150px;table-layout:fixed;text-align:left;' >" + ActionTitle + "</td>");
if (myObj.includeActvtySmmry.Equals("false"))
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:100px;table-layout:fixed;' >" + ActionOwnerRole + "</td>");
CustomMailBody.AppendLine("<td style='word-break: break-word;width:200px;table-layout:fixed;text-align:left;' >" + ActionOwner + "</td>");
}
else
{
CustomMailBody.AppendLine("<td style='word-break: break-word;width:100px;table-layout:fixed;text-align:left;' >" + ActionOwner + "</td>");
CustomMailBody.AppendLine("<td style='word-break: break-word;width:200px;table-layout:fixed;text-align:left;' >" + ActionSummary + "</td>");
}
CustomMailBody.AppendLine("<td style='word-break: break-word;width:500px;table-layout:fixed;' align='left' valign='top' >" + CustomSubMailBody + "</td>");
CustomMailBody.AppendLine("<td style='width:80px;table-layout:fixed;' align='center'>" + Details + "</td>");
CustomMailBody.AppendLine("<td style='width:80px;table-layout:fixed;' align='center'>" + History + "</td>");
}
#region Misc Fields
//CustomMailBody.AppendLine("<td>" + ActionID + "</td>");
//CustomMailBody.AppendLine("<td>" + ProjectType + "</td>");
//CustomMailBody.AppendLine("<td>" + Priority + "</td>");
#endregion
CustomMailBody.AppendLine("</tr>");
}
}
CustomMailBody.AppendLine("</table>");
}
}
#endregion
#region End Tags
if (!myObj.viewReport)
{
CustomMailBody.AppendLine("</body>");
CustomMailBody.AppendLine("</html>");
}
#endregion
if (rptTotalRowPrinted >= 1)
{
MailBody = Convert.ToString(CustomMailBody);
}
}
catch (Exception ex)
{
throw ex;
}
return MailBody;
}
private static void SendReport(string MailBody, ClientContext myContext, ReportItems myObj)
{
try
{
#region Get Mail Propperties
string MailTemplate = "BDATReportTemplate";
string MailTo = myObj.reportRecipientsEmail;
string MailCC = string.Empty;
string MailSubject = myObj.emailSubject;
#endregion
#region Get Mail Properties (Subject, Body Template and Signature)
string MailBodyTemplate = string.Empty;
string MailSignature = string.Empty;
string NewMailBody = string.Empty;
CamlQuery myQuery = new CamlQuery();
Microsoft.SharePoint.Client.List lstTemplate = myContext.Web.Lists.GetByTitle(Constants.LIST_NAME_EmailTemplate);
myQuery.ViewXml = @"<View><Query><Where><Eq><FieldRef Name='Title'/><Value Type='Text'>" + MailTemplate + "</Value></Eq></Where></Query></View>";
Microsoft.SharePoint.Client.ListItemCollection MailProperties = lstTemplate.GetItems(myQuery);
myContext.Load(lstTemplate);
myContext.ExecuteQuery();
myContext.Load(MailProperties);
myContext.ExecuteQuery();
if (MailProperties.Count > 0)
{
// MailSubject = Convert.ToString(MailProperties[0]["Subject"]);
// MailSubject = myObj.reportTitle;
MailBodyTemplate = Convert.ToString(MailProperties[0]["Body"]);
MailSignature = Convert.ToString(MailProperties[0]["Signature"]);
}
if (MailBodyTemplate.Contains("#statustable"))
MailBodyTemplate = MailBodyTemplate.Replace("#statustable", Convert.ToString(MailBody));
if (MailBodyTemplate.Contains("#signature"))
MailBodyTemplate = MailBodyTemplate.Replace("#signature", MailSignature);
NewMailBody = MailBodyTemplate;
#endregion
#region Log and Send Mail on the basis of mail frequency set on list.(e.g weekly/monday, biweekly/tuesday etc)
SendMailByFrequencySetInList(MailTo, MailCC, MailSubject, NewMailBody, myContext, myObj);
// SendTaskEMail(MailTo, MailCC, MailSubject, NewMailBody, myContext, myObj);
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
public static void SendMailByFrequencySetInList(string MailTo, string MailCC, string MailSubject, string NewMailBody, ClientContext ctx, ReportItems rptObj)
{
string DayNameToday = DateTime.Now.DayOfWeek.ToString();
string mailFrequecy = rptObj.MailFrequency;
string mailSendingDay = rptObj.MailSendingDay;
List list = ctx.Web.Lists.GetByTitle(Constants.LIST_NAME_REPORTROLE_MAPPING);
ListItem item = list.GetItemById(rptObj.ListItemId);
if (!rptObj.emailSubject.Contains("Reset Highlight, Lowlight Values"))
{
// weekly
if (mailFrequecy == Constants.WEEKLY && mailSendingDay == DayNameToday)
{
//send mail
SendTaskEMail(MailTo, MailCC, MailSubject, NewMailBody, ctx, rptObj);
}
//Bi-weekly
if (mailFrequecy == Constants.BI_WEEKLY && mailSendingDay == DayNameToday)
{
if (string.IsNullOrEmpty(rptObj.Bi_WeeklySendDate))
{
item["Bi_WeeklySendDate"] = DateTime.Now.ToShortDateString();
item.Update();
ctx.ExecuteQuery();
//send mail
SendTaskEMail(MailTo, MailCC, MailSubject, NewMailBody, ctx, rptObj);
}
else
{
item["Bi_WeeklySendDate"] = null;
item.Update();
ctx.ExecuteQuery();
}
}
//Monthly
if (mailFrequecy == Constants.MONTHLY && mailSendingDay == DayNameToday)
{
if (string.IsNullOrEmpty(rptObj.Bi_MonthlySendDate))
{
//send mail
SendTaskEMail(MailTo, MailCC, MailSubject, NewMailBody, ctx, rptObj);
item["Bi_MonthlySendDate"] = DateTime.Now.ToShortDateString();
item.Update();
ctx.ExecuteQuery();
}
else
{
int currentMonth = DateTime.Now.Month;
DateTime dt = Convert.ToDateTime(rptObj.Bi_MonthlySendDate);
int monthCount = Convert.ToInt32(dt.Month);
if (monthCount != currentMonth && (monthCount + 1) == currentMonth)
{
//send mail
SendTaskEMail(MailTo, MailCC, MailSubject, NewMailBody, ctx, rptObj);
item["Bi_MonthlySendDate"] = DateTime.Now.ToShortDateString();
item.Update();
ctx.ExecuteQuery();
}
}
}
}
else
{
if (mailFrequecy == Constants.WEEKLY && mailSendingDay == DayNameToday)
{
// reset high low filter without mail
ResetHighLowFieldsEveryWeekonTuesday(ctx);
}
}
}
public static void SendTaskEMail(string to, string cc, string subject, string body, ClientContext context, ReportItems rptObj)
{
try
{
string EmailHost = ConfigurationManager.AppSettings["EmailHost"];
MailAddress fromAddress = new MailAddress("SATAdmin@amat.com");
MailMessage newEmail = new MailMessage();
if (to != null && to != "")
{
if (to.Contains(";"))
{
string[] tos = to.Split(';');
for (int i = 0; i < tos.Length; i++)
{
if (!string.IsNullOrEmpty(tos[i]))
newEmail.To.Add(new MailAddress(tos[i]));
}
}
else
{
newEmail.To.Add(new MailAddress(to));
}
}
else
{
newEmail.To.Add("SATAdmin@amat.com");
}
if (cc != string.Empty && cc != "" && cc != null)
{
if (cc.Contains(";"))
{
string[] ccs = cc.Split(';');
for (int i = 0; i < ccs.Length; i++)
{
if (!string.IsNullOrEmpty(ccs[i]))
newEmail.CC.Add(new MailAddress(ccs[i]));
}
}
else
{
newEmail.CC.Add(new MailAddress(cc));
//newEmail.Bcc.Add(new MailAddress(cc));
}
}
Console.WriteLine("Sending Mail..");
//newEmail.From = fromAddress;
//newEmail.Subject = subject;
//newEmail.Body = body;
//newEmail.IsBodyHtml = true;
//SmtpClient client = new SmtpClient();
//client.Host = EmailHost;
//client.Send(newEmail);
var emailp = new EmailProperties();
List<string> atAddress = new List<string>();
List<string> ccAddress = new List<string>();
foreach (string add in to.Split(';'))
atAddress.Add(add);
foreach (string add in cc.Split(';'))
ccAddress.Add(add);
emailp.To = atAddress;
emailp.CC = ccAddress;
emailp.From = "BIM-Support-GLOBAL-F@amat.com";
emailp.Body = body;
emailp.Subject = subject;
Utility.SendEmail(context, emailp);
context.ExecuteQuery();
//status = true;
}
catch (Exception ex)
{
throw ex;
}
}
public static string BuildQuery(string[] values, string ColumnName, string ConditionOrAnd, string compareOp, string DataType = "Text", string AdditionalColumn = "", string AdditionalColumnDataType = "Text")
{
string qry = string.Empty;
if (values != null)
{
var TagCount = values.Count();
List<string> strConditions = new List<string>();
string NANDTagStart = string.Empty, NANDTagClose = string.Empty;
string COMPARETag = string.Empty;
if (ConditionOrAnd.ToUpper() == "AND")
{
NANDTagStart = "<And>";
NANDTagClose = "</And>";
}
if (ConditionOrAnd.ToUpper() == "OR")
{
NANDTagStart = "<Or>";
NANDTagClose = "</Or>";
}
switch (compareOp.ToUpper())
{
case "EQ":
COMPARETag = "<Eq>{0}</Eq>";
break;
case "CONTAINS":
COMPARETag = "<Contains>{0}</Contains>";
break;
}
foreach (var item in values)
{
string colmnString = string.Empty;
//roleConditions.Add(x => ((string)x["ActionOwnr_x0020_Role"]).Contains(item));
switch (DataType.ToUpper())
{
case "NOTE":
colmnString = (@"<FieldRef Name='" + ColumnName + "' /> <Value Type='Note'>" + item + "</Value>");
break;
case "USER":
colmnString = (@"<FieldRef Name='" + ColumnName + "' /> <Value Type='User'>" + item + "</Value>");
break;
default:
colmnString = (@"<FieldRef Name='" + ColumnName + "' /> <Value Type='Text'>" + item + "</Value>");
break;
}
if ((ColumnName == "ActionOwner_x0020_Role" || ColumnName == "Creator_x0020_Role") && item == "BD")
{
colmnString = @"<IsNull><FieldRef Name='" + ColumnName + "' /></IsNull>";
strConditions.Add(colmnString);
colmnString = (@"<FieldRef Name='" + ColumnName + "' /> <Value Type='Text'>" + item + "</Value>");
}
if (!string.IsNullOrEmpty(AdditionalColumn))
{
strConditions.Add(string.Format(COMPARETag, colmnString));
switch (AdditionalColumnDataType.ToUpper())
{
case "NOTE":
colmnString = (@"<FieldRef Name='" + AdditionalColumn + "' /> <Value Type='Note'>" + item + "</Value>");
break;
default:
colmnString = (@"<FieldRef Name='" + AdditionalColumn + "' /> <Value Type='Text'>" + item + "</Value>");
break;
}
}
colmnString = string.Format(COMPARETag, colmnString);
strConditions.Add(colmnString);
}
qry = string.Empty;
TagCount = strConditions.Count;
for (int inx = 1; inx <= TagCount; inx++)
{
if (TagCount >= 2)
{
if (inx > 2)
{
qry = (NANDTagStart + qry);
qry = qry + (strConditions.ElementAt(inx - 1) + NANDTagClose);
}
else if (inx == 2)
{
qry = qry + (strConditions.ElementAt(inx - 1) + NANDTagClose);
}
else if (inx == 1)
{
qry = qry + (NANDTagStart + strConditions.ElementAt(inx - 1));
}
}
else
{
qry = qry + (strConditions.ElementAt(inx - 1));
}
}
}
return qry.ToString();
}
public static string BuildCAMLInQuery(List<string> values, string ColumnName, string DataType = "Text")
{
List<string> strConditions = new List<string>();
string NANDTagStart = string.Empty, NANDTagClose = string.Empty;
string COMPARETag = string.Empty;
NANDTagStart = "<Or>";
NANDTagClose = "</Or>";
COMPARETag = "<In>{0}</In>";
List<List<string>> ListofValueList = SplitList(values, 100).ToList();
var TagCount = ListofValueList.Count();
string colmnString = string.Empty;
foreach (var item in ListofValueList)
{
colmnString = @"<FieldRef Name='" + ColumnName + "' /><Values>";
foreach (var value in item)
{
colmnString += "<Value Type='" + DataType + "'>" + value + "</Value>";
}
colmnString += "</Values>";
//roleConditions.Add(x => ((string)x["ActionOwnr_x0020_Role"]).Contains(item));
colmnString = string.Format(COMPARETag, colmnString);
strConditions.Add(colmnString);
}
string qry = string.Empty;
TagCount = strConditions.Count;
for (int inx = 1; inx <= TagCount; inx++)
{
if (TagCount >= 2)
{
if (inx > 2)
{
qry = (NANDTagStart + qry);
qry = qry + (strConditions.ElementAt(inx - 1) + NANDTagClose);
}
else if (inx == 2)
{
qry = qry + (strConditions.ElementAt(inx - 1) + NANDTagClose);
}
else if (inx == 1)
{
qry = qry + (NANDTagStart + strConditions.ElementAt(inx - 1));
}
}
//else
//{
// if (TagCount > 1)
// {
// if (inx == 1)
// {
// qry.Append(NANDTagStart);
// }
// if (inx % 2 == 0)
// {
// qry.Append(strConditions.ElementAt(inx - 1) + NANDTagClose);
// }
// else if (inx % 2 == 1 && inx < TagCount)
// {
// qry.Append(NANDTagStart + strConditions.ElementAt(inx - 1));
// }
// else if(inx == TagCount)
// {
// qry.Append(strConditions.ElementAt(inx - 1) + NANDTagClose);
// }
// }
else
{
qry = qry + (strConditions.ElementAt(inx - 1));
}
}
return qry.ToString();
}
public static void ResetHighLowFieldsEveryWeekonTuesday(ClientContext clientContext)
{
string LastRun = string.Empty;
LastRun = DateTime.Now.ToString();
try
{
List lstActions = clientContext.Web.Lists.GetByTitle(Constants.LIST_NAME_MDPSBU);
string currentDate = DateTime.Now.ToString("yyyy-MM-dd");
string previousDate = DateTime.Now.AddDays(-10).ToString("yyyy-MM-dd");
CamlQuery camlQuery = new CamlQuery() { ViewXml = "<View><Query><Where><And><Leq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + currentDate + "</Value></Leq><Geq><FieldRef Name='Created' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + previousDate + "</Value></Geq></And></Where></Query></View>" };
//camlQuery.ViewXml = query;
ListItemCollection items = lstActions.GetItems(camlQuery);
clientContext.Load(items);
clientContext.ExecuteQuery();
if (items != null && items.Count > 0)
{
foreach (ListItem item in items)
{
item["EOB_Report"] = string.Empty;
item["LastRun"] = LastRun;
item.Update();
clientContext.ExecuteQuery();
}
}
//foreach (DataRow dr in dtResetActions.Rows)
//{
// string actionID = String.Empty;
// //if (Convert.ToString(dr["ID"]) != null && Convert.ToString(dr["ID"]) != "" && (Convert.ToString(dr["EOB_Report"]) == "Highlight" || Convert.ToString(dr["EOB_Report"]) == "Lowlight"))
// //{
// actionID = Convert.ToString(dr["ID"]);
// //}
// if (!string.IsNullOrEmpty(actionID))
// {
// Microsoft.SharePoint.Client.ListItem lstItem = lstActions.GetItemById(Convert.ToInt32(actionID));
// clientContext.Load(lstItem);
// clientContext.ExecuteQuery();
// if (lstItem != null)
// {
// lstItem["EOB_Report"] = "";
// //lstItem["Priority"] = "";
// lstItem["LastRun"] = LastRun;
// lstItem.Update();
// clientContext.ExecuteQuery();
// }
// }
//}
}
catch (Exception ex)
{
throw ex;
}
}
public static string GetReportWindowValue(string reportWindow)
{
string value = string.Empty;
switch (reportWindow)
{
case "1 Week":
value = "-7";
break;
case "2 Week":
value = "-14";
break;
case "3 Week":
value = "-21";
break;
case "1 Month":
value = "-30";
break;
case "2 Month":
value = "-60";
break;
case "3 Month":
value = "-90";
break;
case "4 Month":
value = "-120";
break;
case "5 Month":
value = "-150";
break;
case "6 Month":
value = "-180";
break;
}
return value;
}
public static IEnumerable<List<T>> SplitList<T>(List<T> source, int nSize = 500)
{
for (int i = 0; i < source.Count; i += nSize)
{
yield return source.GetRange(i, Math.Min(nSize, source.Count - i));
}
}
static ClientContext GetModernClientContext(string siteUrl)
{
//string realm = TokenHelper.GetRealmFromTargetUrl(new Uri(siteUrl));
//string accessToken = TokenHelper.GetAppOnlyAccessToken(TokenHelper.SharePointPrincipal, new Uri(siteUrl).Authority, realm).AccessToken;
//return TokenHelper.GetClientContextWithAccessToken(siteUrl, accessToken);
// ClientContext Context = new PnP.Framework.AuthenticationManager().GetACSAppOnlyContext(siteUrl, clientID, clientSecret);
return new OfficeDevPnP.Core.AuthenticationManager().GetAppOnlyAuthenticatedContext(siteUrl, clientID, clientSecret);
}
/// <summary>
/// Method to get Regions
/// </summary>
/// <returns></returns>
public static DataTable GetRegion()
{
var conString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConfigDBConnectionString"].ConnectionString;
DataTable dtRegion = new DataTable();
using (SqlConnection sqlConnection = new SqlConnection(conString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "MDP_SBU_GetRegion";
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
sqlDataAdapter.Fill(dtRegion);
}
}
}
return dtRegion;
}
public static void ErrorLog(string stackTrace, string message, string source, string methodName, string innerException, string createdBy)
{
var conString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConfigDBConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
SqlCommand cmd = new SqlCommand("MDPSBUErrorLog", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Source", source);
cmd.Parameters.AddWithValue("@Message", message);
cmd.Parameters.AddWithValue("@MethodName", methodName);
cmd.Parameters.AddWithValue("@StackTrace", stackTrace);
cmd.Parameters.AddWithValue("@InnerException", innerException);
cmd.Parameters.AddWithValue("@CreatedBy", createdBy);
cmd.ExecuteNonQuery();
//con.Close();
}
}
}
Subscribe to:
Posts (Atom)