Hi Team,
I am fetching list of records from account which are modified yesterday or certain date. But it throws Internal Server Error.
Below is my code snippet:
var selectFilters = new Filters()
{
FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.FilterGroup,
Items = new Dictionary
{
{
"FilterByCreatedOn",
// Value.
new Filter
{
FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
ComparisonType = FilterComparisonType.GreaterOrEqual,
LeftExpression = new BaseExpression ()
{
// Expression type - SchemaColumn.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to the column relative to the root schema.
ColumnPath = "ModifiedOn",
},
// Filter range final expression.
RightExpression= new BaseExpression ()
{
ExpressionType = EntitySchemaQueryExpressionType.Parameter,
Parameter = new Parameter ()
{
DataValueType = DataValueType.DateTime,
Value = "5/11/2018" or new DateTime(2018,05,11)
}
}
}
}
}
};
Like
Hi
If you remember, bpmonline itself uses DataService for connecting to server. So the better way to apply a correct filter is to look at a browser console. Just apply an advanced filter you want in the interface and then watch Network section in the browser console.
In c# you should use a standart TimeStamp:
const string startDate = @"""\""2000-12-31T09:41:59\"""""""""";
Peter Vdovukhin,
Thank you so much. It is really helpful for me. I have tried exactly like this but still getting Internal Server Error.
I do not understand, It was helpful or you still getting the error?
It is helpful that I get to know some of the things from this. But I have tried exactly like console filter and still getting error. Not able to resolve issue.
Please, post here a complete code example with validation. So I could reproduce it under my environment
Here is my complete code of my console application
class ResponseStatus
{
public int Code { get; set; }
public string Message { get; set; }
public object Exception { get; set; }
public object PasswordChangeUrl { get; set; }
public object RedirectUrl { get; set; }
}
class Program
{
private const string baseUri = @"https://[example].bpmonline.com";
// Query string to the Login method of the AuthService.svc service.
private const string authServiceUri = baseUri + @"/ServiceModel/AuthService.svc/Login";
// SelectQuery path string.
private const string selectQueryUri = baseUri + @"/0/DataService/json/SyncReply/SelectQuery";
// Bpm'online authentication cookie.
private static CookieContainer AuthCookie = new CookieContainer();
private static bool TryLogin(string userName, string userPassword)
{
// Creating an instance of the authentication service request.
var authRequest = HttpWebRequest.Create(authServiceUri) as HttpWebRequest;
// Defining the request's method.
authRequest.Method = "POST";
// Defining the request's content type.
authRequest.ContentType = "application/json";
// Enabling the use of cookie in the request.
authRequest.CookieContainer = AuthCookie;
// Placing user credentials to the request.
using (var requestStream = authRequest.GetRequestStream())
{
using (var writer = new StreamWriter(requestStream))
{
writer.Write(@"{
""UserName"":""" + userName + @""",
""UserPassword"":""" + userPassword + @"""
}");
}
}
// Auxiliary object where the HTTP reply data will be de-serialized.
ResponseStatus status = null;
// Getting a reply from the server. If the authentication is successful, cookie will be placed to the AuthCookie property.
// These cookies can be used for subsequent requests.
using (var response = (HttpWebResponse)authRequest.GetResponse())
{
using (var reader = new StreamReader(response.GetResponseStream()))
{
// De-serialization of the HTTP reply to an auxiliary object.
string responseText = reader.ReadToEnd();
status = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<ResponseStatus>(responseText);
}
}
// Checking authentication status.
if (status != null)
{
// Authentication is successful.
if (status.Code == 0)
{
return true;
}
// Authentication is unsuccessful.
Console.WriteLine(status.Message);
}
return false;
}
static void AddAuthCookie(HttpWebRequest request)
{
request.CookieContainer = AuthCookie;
// Adding a CSRF token to the request title.
CookieCollection cookieCollection = AuthCookie.GetCookies(new Uri(authServiceUri));
string csrfToken = cookieCollection["BPMCSRF"].Value;
((HttpWebRequest)request).Headers.Add("BPMCSRF", csrfToken);
}
static void Main(string[] args)
{
if (!TryLogin("Supervisor", "Supervisor1"))
{
return;
}
try
{
var selectQuery = new SelectQuery()
{
RootSchemaName = "Account",
Columns = new SelectQueryColumns()
};
var columnExpressionName = new ColumnExpression()
{
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
ColumnPath = "Name"
};
var selectQueryColumnName = new SelectQueryColumn()
{
Caption = "Company Name",
OrderDirection = OrderDirection.Ascending,
// Sorting order position.
OrderPosition = 0,
// Expression that specifies column type.
Expression = columnExpressionName
};
var columnExpressionContactName = new ColumnExpression()
{
// Expression type — subquery.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to column in relation to root schema.
ColumnPath = "PrimaryContact",
};
var selectQueryColumnContactName = new SelectQueryColumn()
{
//Title.
Caption = "Full Name",
// Expression, which specifies column type.
Expression = columnExpressionContactName
};
var columnExpressionJobTitle = new ColumnExpression()
{
// Expression type — subquery.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to column in relation to root schema.
ColumnPath = "JobTitle"
};
var selectQueryColumnJobTitle = new SelectQueryColumn()
{
//Title.
Caption = "Job Title",
// Sorting direction — ascending.
OrderDirection = OrderDirection.Ascending,
// Sorting order position.
OrderPosition = 1,
// Expression, which specifies column type.
Expression = columnExpressionJobTitle
};
var columnExpressionREPCode = new ColumnExpression()
{
// Expression type — subquery.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to column in relation to root schema.
ColumnPath = "UsrREPCode"
};
var selectQueryColumnREPCode = new SelectQueryColumn()
{
//Title.
Caption = "REP Code",
// Sorting direction — ascending.
OrderDirection = OrderDirection.Ascending,
// Sorting order position.
OrderPosition = 2,
// Expression, which specifies column type.
Expression = columnExpressionREPCode
};
var columnExpressionDAC = new ColumnExpression()
{
// Expression type — subquery.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to column in relation to root schema.
ColumnPath = "UsrDAC"
};
var selectQueryColumnDAC = new SelectQueryColumn()
{
//Title.
Caption = "DAC",
// Expression, which specifies column type.
Expression = columnExpressionDAC
};
var columnExpressionOwner = new ColumnExpression()
{
// Expression type — subquery.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to column in relation to root schema.
ColumnPath = "Owner"
};
var selectQueryColumnOwner = new SelectQueryColumn()
{
//Title.
Caption = "Owner",
// Sorting direction — ascending.
OrderDirection = OrderDirection.Ascending,
// Sorting order position.
OrderPosition = 4,
// Expression, which specifies column type.
Expression = columnExpressionOwner
};
var columnExpressionPhoneNumber = new ColumnExpression()
{
// Expression type — SchemaColumn.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to column in relation to root schema.
ColumnPath = "Phone"
};
var selectQueryColumnPhoneNumber = new SelectQueryColumn()
{
//Title.
Caption = "Contact Number",
// Expression, which specifies column type.
Expression = columnExpressionPhoneNumber
};
var columnExpressionEmail = new ColumnExpression()
{
// Expression type — subquery.
ExpressionType = EntitySchemaQueryExpressionType.SubQuery,
// Path to column in relation to root schema.
ColumnPath = "[Account:Contact].Email"
};
var selectQueryColumnEmail = new SelectQueryColumn()
{
//Title.
Caption = "Email",
// Sorting direction — ascending.
OrderDirection = OrderDirection.Ascending,
// Sorting order position.
OrderPosition = 1,
// Expression, which specifies column type.
Expression = columnExpressionEmail
};
var columnExpressionCity = new ColumnExpression()
{
// Expression type — subquery.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to column in relation to root schema.
ColumnPath = "City"
};
var selectQueryColumnCity = new SelectQueryColumn()
{
//Title.
Caption = "City",
// Expression, which specifies column type.
Expression = columnExpressionCity
};
var columnExpressionState = new ColumnExpression()
{
// Expression type — subquery.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to column in relation to root schema.
ColumnPath = "Region"
};
var selectQueryColumnState = new SelectQueryColumn()
{
//Title.
Caption = "State",
// Expression, which specifies column type.
Expression = columnExpressionState
};
var columnExpressionCountry = new ColumnExpression()
{
// Expression type — subquery.
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
// Path to column in relation to root schema.
ColumnPath = "Country"
};
var selectQueryColumnCountry = new SelectQueryColumn()
{
//Title.
Caption = "Country",
// Expression, which specifies column type.
Expression = columnExpressionCountry
};
// Adding columns to query.
selectQuery.Columns.Items = new Dictionary<string, SelectQueryColumn>()
{
{
"Company Name",
selectQueryColumnName
},
{
"Full Name",
selectQueryColumnContactName
},
{
"Job Title",
selectQueryColumnJobTitle
},
{
"REP Code",
selectQueryColumnREPCode
},
{
"DAC",
selectQueryColumnDAC
},
{
"Owner",
selectQueryColumnOwner
},
{
"Contact Number",
selectQueryColumnPhoneNumber
},
//{
// "Email",
// selectQueryColumnEmail
//},
{
"City",
selectQueryColumnCity
},
{
"State",
selectQueryColumnState
},
{
"Country",
selectQueryColumnCountry
}
};
// Query filters.
var selectFilters = new Filters()
{
FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.FilterGroup,
IsEnabled = true,
RootSchemaName = "Account",
Items = new Dictionary<string, Filter>
{
{
"FilterByCreatedOn",
new Filter
{
FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
ComparisonType = FilterComparisonType.Greater,
IsEnabled = true,
LeftExpression = new BaseExpression ()
{
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
ColumnPath = "ModifiedOn",
},
RightExpression= new BaseExpression ()
{
ExpressionType = EntitySchemaQueryExpressionType.Parameter,
Parameter = new Parameter ()
{
DataValueType = DataValueType.DateTime,
Value= "2018-05-11T11:17:04.000"
}
},
TrimDateTimeParameterToDate = true,
}
},
{
"FilterByName",
new Filter
{
FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
ComparisonType = FilterComparisonType.Contain,
IsEnabled = true,
LeftExpression = new BaseExpression ()
{
ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
ColumnPath = "Name",
},
RightExpression= new BaseExpression ()
{
ExpressionType = EntitySchemaQueryExpressionType.Parameter,
Parameter = new Parameter ()
{
DataValueType = DataValueType.Text,
Value= "Test"
}
},
TrimDateTimeParameterToDate = true,
}
}
}
};
selectQuery.Filters = selectFilters;
// Serialization of an instance of query class to add to JSON string.
var json = new JavaScriptSerializer().Serialize(selectQuery);
byte[] jsonArray = Encoding.UTF8.GetBytes(json);
var selectRequest = HttpWebRequest.Create(selectQueryUri) as HttpWebRequest;
selectRequest.Method = "POST";
selectRequest.ContentType = "application/json";
// Adding earlier received authentication cookies to a data fetch query.
selectRequest.CookieContainer = AuthCookie;
selectRequest.ContentLength = jsonArray.Length;
AddAuthCookie(selectRequest);
using (var requestStream = selectRequest.GetRequestStream())
{
requestStream.Write(jsonArray, 0, jsonArray.Length);
}
using (var response = (HttpWebResponse)selectRequest.GetResponse())
{
using (StreamReader reader = new StreamReader(response.GetResponseStream()))
{
var result = reader.ReadToEnd();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
Pay attention to this code: private const string baseUri = @"https://[example].bpmonline.com";
it should be the address of your application
yes i know... I have just write down example in this code only... for comment
You can put there 'example' instead of 'example'. It will give you exact scenario. Thanks
You passed a wrong date format. Again, if look at browser console when applying a date filter you will see the next format: ""2018-06-05T23:59:59.999"". So you should escape one more quote mark to rich the goal in c#: Value = "\""+DateTime.Now+ "\""