public void FilterTest()
{
string expected = @"<?xml version=""1.0"" encoding=""utf-8""?>
<function controlid=""unittest"">
<query>
<select>
<field>CUSTOMERID</field>
<field>RECORDNO</field>
</select>
<object>ARINVOICE</object>
<filter>
<lessthanorequalto>
<field>RECORDNO</field>
<value>10</value>
</lessthanorequalto>
</filter>
<options />
</query>
</function>";
SelectBuilder builder = new SelectBuilder();
ISelect[] fields = builder.Fields(new[] { "CUSTOMERID", "RECORDNO" }).GetFields();
IFilter filter = (new Filter("RECORDNO")).SetLessThanOrEqualTo("10");
IQueryFunction query = new QueryFunction("unittest")
{
FromObject = "ARINVOICE",
SelectFields = fields,
Filter = filter
};
this.CompareXml(expected, query);
}
public void FilterOrWithAndConditionTest()
{
string expected = @"<?xml version=""1.0"" encoding=""utf-8""?>
<function controlid=""unittest"">
<query>
<select>
<field>BATCHNO</field>
<field>RECORDNO</field>
<field>STATE</field>
</select>
<object>GLBATCH</object>
<filter>
<or>
<equalto>
<field>JOURNAL</field>
<value>APJ</value>
</equalto>
<and>
<greaterthanorequalto>
<field>BATCHNO</field>
<value>1</value>
</greaterthanorequalto>
<equalto>
<field>STATE</field>
<value>Posted</value>
</equalto>
</and>
</or>
</filter>
<options />
</query>
</function>";
SelectBuilder builder = new SelectBuilder();
ISelect[] fields = builder.Fields(new[] { "BATCHNO", "RECORDNO", "STATE" }).GetFields();
AndOperator batchnoAndState = new AndOperator(new List <IFilter>());
batchnoAndState.AddFilter((new Filter("BATCHNO")).SetGreaterThanOrEqualTo("1"));
batchnoAndState.AddFilter((new Filter("STATE")).SetEqualTo("Posted"));
IFilter journal = new Filter("JOURNAL").SetEqualTo("APJ");
IFilter filter = new OrOperator(new List <IFilter>()
{
journal, batchnoAndState
});
IQueryFunction query = new QueryFunction("unittest")
{
FromObject = "GLBATCH",
SelectFields = fields,
Filter = filter
};
this.CompareXml(expected, query);
}
public void complex_non_fluent_test()
{
const string toBuild = "select id, name as b from products p join groups g on p.group_id=g.id left join stores s on g.id=s.group_id where (price>'100' and price<='1000') or code like 'a%' group by product_group having avg(price)>100 order by price desc";
var fields = new List <SelectField> {
new SelectField("id"), new SelectField("name", "b")
};
var qbuilder = new SelectBuilder();
qbuilder.Fields(fields).From("products", "p").Join("groups", "g", "p.group_id", "g.id").Join("stores", "s", "g.id", "s.group_id", JoinType.Left).OpenBracket()
.Where("price", Compare.GreaterThan, 100).And.Where("price", Compare.LessOrEquals, 1000)
.CloseBracket().Or.Where("code", Compare.Like, "a%")
.GroupBy("product_group").Having("avg(price)>100").OrderByDesc("price").Build();
Assert.Equal(toBuild.ToLower(), qbuilder.Build().ToLower());
}
public void FieldsTest()
{
string expected = @"<?xml version=""1.0"" encoding=""utf-8""?>
<function controlid=""unittest"">
<query>
<select>
<field>CUSTOMERID</field>
<field>TOTALDUE</field>
<field>WHENDUE</field>
<field>TOTALENTERED</field>
<field>TOTALDUE</field>
<field>RECORDNO</field>
</select>
<object>ARINVOICE</object>
<options />
</query>
</function>";
string[] fieldNames = { "CUSTOMERID",
"TOTALDUE",
"WHENDUE",
"TOTALENTERED",
"TOTALDUE",
"RECORDNO" };
SelectBuilder builder = new SelectBuilder();
ISelect[] fields = builder.Fields(fieldNames).GetFields();
IQueryFunction query = new QueryFunction("unittest")
{
FromObject = "ARINVOICE",
SelectFields = fields
};
this.CompareXml(expected, query);
}
public void ThreeLevelFilterTest()
{
string expected = @"<?xml version=""1.0"" encoding=""utf-8""?>
<function controlid=""unittest"">
<query>
<select>
<field>BATCHNO</field>
<field>RECORDNO</field>
<field>STATE</field>
</select>
<object>GLBATCH</object>
<filter>
<or>
<and>
<equalto>
<field>JOURNAL</field>
<value>APJ</value>
</equalto>
<equalto>
<field>STATE</field>
<value>Posted</value>
</equalto>
</and>
<and>
<equalto>
<field>JOURNAL</field>
<value>RCPT</value>
</equalto>
<equalto>
<field>STATE</field>
<value>Posted</value>
</equalto>
<or>
<equalto>
<field>RECORDNO</field>
<value>168</value>
</equalto>
<equalto>
<field>RECORDNO</field>
<value>132</value>
</equalto>
</or>
</and>
</or>
</filter>
<options />
</query>
</function>";
SelectBuilder builder = new SelectBuilder();
ISelect[] fields = builder.Fields(new[] { "BATCHNO", "RECORDNO", "STATE" }).GetFields();
AndOperator apjAndState = new AndOperator(new List <IFilter>());
apjAndState.AddFilter((new Filter("JOURNAL")).SetEqualTo("APJ"));
apjAndState.AddFilter((new Filter("STATE")).SetEqualTo("Posted"));
OrOperator recordnoOr = new OrOperator(new List <IFilter>());
recordnoOr.AddFilter((new Filter("RECORDNO")).SetEqualTo("168"));
recordnoOr.AddFilter((new Filter("RECORDNO")).SetEqualTo("132"));
AndOperator rcptAndState = new AndOperator(new List <IFilter>());
rcptAndState.AddFilter((new Filter("JOURNAL")).SetEqualTo("RCPT"));
rcptAndState.AddFilter((new Filter("STATE")).SetEqualTo("Posted"));
rcptAndState.AddFilter(recordnoOr);
IFilter filter = new OrOperator(new List <IFilter>()
{
apjAndState, rcptAndState
});
IFunction query = new QueryFunction("unittest")
{
FromObject = "GLBATCH",
SelectFields = fields,
Filter = filter
};
this.CompareXml(expected, query);
}
public static void Run(ILogger logger)
{
OnlineClient client = Bootstrap.Client(logger);
List <IFilter> filterList = new List <IFilter>();
filterList.Add((new Filter("CUSTOMERID")).SetLike("c%"));
filterList.Add((new Filter("CUSTOMERID")).SetLike("1%"));
OrOperator filter = new OrOperator(filterList);
OrderBuilder orderBuilder = new OrderBuilder();
IOrder[] orders = orderBuilder.Descending("CUSTOMERID").GetOrders();
SelectBuilder selectBuilder = new SelectBuilder();
ISelect[] fields = selectBuilder.
Fields(new[] { "CUSTOMERID", "CUSTOMERNAME" }).
Sum("TOTALDUE").
GetFields();
QueryFunction query = new QueryFunction()
{
SelectFields = fields,
FromObject = "ARINVOICE",
Filter = filter,
CaseInsensitive = true,
PageSize = 100,
OrderBy = orders
};
logger.LogInformation("Executing query to Intacct API");
Task <OnlineResponse> task = client.Execute(query);
task.Wait();
OnlineResponse response = task.Result;
Result result = response.Results[0];
dynamic json = JsonConvert.DeserializeObject(JsonConvert.SerializeObject(result.Data));
if (json != null && json.First != null)
{
Console.WriteLine("Success! Number of ARINVOICE objects found: " + result.TotalCount);
Console.WriteLine("First ARINVOICE result found");
Console.WriteLine("CUSTOMERID: " + json.First["ARINVOICE"]["CUSTOMERID"].Value);
Console.WriteLine("CUSTOMERNAME: " + json.First["ARINVOICE"]["CUSTOMERNAME"].Value);
Console.WriteLine("SUM.TOTALDUE: " + json.First["ARINVOICE"]["SUM.TOTALDUE"].Value);
Console.WriteLine("See the log file (logs/intacct.log) for the complete list of results.");
}
else
{
Console.WriteLine("The query executed, but no ARINVOICE objects met the query criteria.");
Console.WriteLine("Either modify the filter or comment it out from the query.");
Console.WriteLine("See the log file (logs/intacct.log) for the XML request.");
}
try
{
string jsonString = json.ToString();
logger.LogDebug(
"Query successful [ Company ID={0}, User ID={1}, Request control ID={2}, Function control ID={3}, Total count={4}, Data={5} ]",
response.Authentication.CompanyId,
response.Authentication.UserId,
response.Control.ControlId,
result.ControlId,
result.TotalCount,
jsonString
);
}
catch (NullReferenceException e)
{
logger.LogDebug("No response in Data. {0}", e);
}
finally
{
LogManager.Flush();
}
}
