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(); } }