Thursday, September 7, 2017

Using Query with Range and Grouping in AX 2012 R3

static void Test_Query_CustTrans_with_Grouping(Args _args)
{
    Query query,queryParam;
    QueryRun qr;
    QueryBuildDataSource qbdsCustTransSum,qbdsQuery;

    CustTrans  _custTrans;
    TransDate               toDate;

    toDate = str2Date('31102016',123);

    if (toDate == dateNull())
    {
        toDate = DateTimeUtil::date(DateTimeUtil::utcNow());
    }

    query = new query();

    qbdsCustTransSum = query.addDataSource(tableNum(CustTrans));

    qbdsCustTransSum.addRange(fieldNum(CustTrans, AccountNum)).value(queryValue(strFmt('1051')));
    qbdsCustTransSum.addRange(fieldNum(CustTrans, TransDate)).value(SysQuery::range(dateNull(), toDate));

    qbdsCustTransSum.orderMode(ordermode::GroupBy);
    qbdsCustTransSum.addGroupByField(fieldnum(CustTrans, AccountNum));
    qbdsCustTransSum.addGroupByField(fieldnum(CustTrans, TransDate));

    qbdsCustTransSum.addSelectionField(fieldNum(CustTrans, AmountMST), SelectionField::Sum);
    query.addHavingFilter(qbdsCustTransSum, fieldStr(CustTrans, AmountMST), AggregateFunction::Sum).value(SysQuery::valueNot('0'));

    qr = new QueryRun(query);

    while(qr.next())
    {
        _custTrans = qr.get(tableNum(CustTrans));
        info(strFmt('%1 %2',_custTrans.AmountMST,_custTrans.TransDate));
    }

}

No comments:

Post a Comment