public static string BuildQuery(string[] values, string ColumnName, string ConditionOrAnd, string compareOp, string DataType = "Text", string AdditionalColumn = "", string AdditionalColumnDataType = "Text")
{
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);
}
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 string GenerateCamlQry(ReportItems rptParam)
{
try
{
var Roles = rptParam.actionOwnerRole.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var Owners = rptParam.actionOwner.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var ProjectNames = rptParam.actionCategory.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var ProjectTypes = rptParam.actiontype.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var Regions = rptParam.region.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var Customers = rptParam.customer.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var GreenBooks = rptParam.greenbook.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
//Added newly(6-9-22)
var HighLowfilter = rptParam.highLowfilter.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var priority = rptParam.Priority.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
// 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;
//for (int inx = 0; inx < totalQry.Count; inx++)
//{
// if (!string.IsNullOrEmpty(totalQry.ElementAt(inx)))
// {
// firstNonEmty += 1;
// if (firstNonEmty == 1)
// {
// QryBuilder.Append("<And>");
// QryBuilder.Append(totalQry.ElementAt(inx));
// }
// if (inx == totalQry.Count - 1)
// {
// QryBuilder.Append(totalQry.ElementAt(inx));
// QryBuilder.Append("</And>");
// }
// QryBuilder.Insert(0, "<And>");
// QryBuilder.Append(totalQry.ElementAt(inx));
// QryBuilder.Append("</And>");
// }
//}
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));
}
}
#region forCAMLEx.Client
//if (GreenBooks.Count() > 0)
//{
// totalQry.Append("<And>");
//}
//if (Customers.Count() > 0)
//{
// totalQry.Append("<And>");
//}
//if (Regions.Count() > 0)
//{
// totalQry.Append("<And>");
//}
//if (ProjectTypes.Count() > 0)
//{
// totalQry.Append("<And>");
//}
//if (ProjectNames.Count() > 0)
//{
// totalQry.Append("<And>");
//}
//totalQry.Append("<And>");
//if (Roles.Count() > 0 || Owners.Count() >0)
//{
// totalQry.Append("<And>");
//}
//totalQry.Add(BuildQuery(Roles, "ActionOwner_x0020_Role", "OR", "CONTAINS"));
//totalQry.Add(BuildQuery(Owners, "ProjectOwner", "OR", "CONTAINS"));
//if (Roles.Count() > 0 && Owners.Count() > 0)
//{
// totalQry.Append("</And>");
//}
//totalQry.Add(BuildQuery(ProjectNames, "ProjectName", "OR", "CONTAINS"));
//if (ProjectNames.Count() > 0)
//{
// totalQry.Append("</And>");
//}
//totalQry.Add(BuildQuery(ProjectTypes, "ProjectType", "OR", "CONTAINS"));
//if (ProjectTypes.Count() > 0)
//{
// totalQry.Append("</And>");
//}
//totalQry.Add(BuildQuery(Regions, "Region", "OR", "CONTAINS"));
//if (Regions.Count() > 0)
//{
// totalQry.Append("</And>");
//}
//totalQry.Add(BuildQuery(Customers, "Customer_x0020_Short_x0020_Name", "OR", "CONTAINS","Note"));
//if (Customers.Count() > 0)
//{
// totalQry.Append("</And>");
//}
//totalQry.Add(BuildQuery(GreenBooks, "GreenBook", "OR", "CONTAINS", "Note"));
//if (GreenBooks.Count() > 0)
//{
// totalQry.Append("</And>");
//}
//totalQry.Add(BuildQuery(new string[] { "Open" }, "Status", "OR", "EQ"));
// totalQry.Append("</And>");
//var RoleExpr = ExpressionsHelper.CombineOr(roleConditions);
//
//var OwnersConditions = new List<Expression<Func<Microsoft.SharePoint.Client.ListItem, bool>>>();
//foreach (var item in Owners)
//{
// OwnersConditions.Add(x => ((string)x["ProjectOwner"]).Contains(item));
//}
//var OwnerExpr = ExpressionsHelper.CombineOr(OwnersConditions);
/////////
//
//var ProjNameConditions = new List<Expression<Func<Microsoft.SharePoint.Client.ListItem, bool>>>();
//foreach (var item in ProjectNames)
//{
// ProjNameConditions.Add(x => ((string)x["ProjectName"]).Contains(item));
//}
//var ProjNameExpr = ExpressionsHelper.CombineOr(ProjNameConditions);
////////
/////////
//
//var ProjectTypeConditions = new List<Expression<Func<Microsoft.SharePoint.Client.ListItem, bool>>>();
//foreach (var item in ProjectTypes)
//{
// ProjectTypeConditions.Add(x => ((string)x["ProjectType"]).Contains(item));
//}
//var ProjectTypeExpr = ExpressionsHelper.CombineOr(ProjectTypeConditions);
////////
/////////
//
//var RegionConditions = new List<Expression<Func<Microsoft.SharePoint.Client.ListItem, bool>>>();
//foreach (var item in Regions)
//{
// RegionConditions.Add(x => ((string)x["Region"]).Contains(item));
//}
//var RegionExpr = ExpressionsHelper.CombineOr(RegionConditions);
////////
/////////
//var Customers = rptParam.customer.Split(new char[','], StringSplitOptions.RemoveEmptyEntries);
//var CustomerConditions = new List<Expression<Func<Microsoft.SharePoint.Client.ListItem, bool>>>();
//foreach (var item in Customers)
//{
// CustomerConditions.Add(x => ((DataTypes.Note)x["Customer_x0020_Short_x0020_Name"]).Contains(item));
//}
//var CustomerExpr = ExpressionsHelper.CombineOr(CustomerConditions);
////////
/////////
//var GreenBooks = rptParam.greenbook.Split(new char[','], StringSplitOptions.RemoveEmptyEntries);
//var GreenBookConditions = new List<Expression<Func<Microsoft.SharePoint.Client.ListItem, bool>>>();
//foreach (var item in GreenBooks)
//{
// GreenBookConditions.Add(x => ((DataTypes.Note)x["GreenBook"]).Contains(item));
//}
//var GreenBookExpr = ExpressionsHelper.CombineOr(GreenBookConditions);
////////
//var StatusConditions = new List<Expression<Func<Microsoft.SharePoint.Client.ListItem, bool>>>();
//StatusConditions.Add(x => (string)x["Status"] == "Open");
//var StatusExpr = ExpressionsHelper.CombineAnd(StatusConditions);
//var expressions = new List<Expression<Func<Microsoft.SharePoint.Client.ListItem, bool>>>();
//expressions.Add(RoleExpr);
//expressions.Add(OwnerExpr);
//expressions.Add(ProjNameExpr);
//expressions.Add(ProjectTypeExpr);
//expressions.Add(RegionExpr);
//expressions.Add(CustomerExpr);
//expressions.Add(GreenBookExpr);
//expressions.Add(StatusExpr);
//var qry = CamlexNET.Camlex.Query().WhereAll(expressions).ToCamlQuery();
#endregion
//return string.Format(OuterQry, QryBuilder.ToString());
return string.Format(OuterQry, qry);
}
catch (Exception ex)
{
throw ex;
}
}
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();
}
No comments:
Post a Comment