การใช้ Query คลาส เพื่อ Group by+Sum+ Date Range

static void UsingQuery(Args _args)
{
   Query                    query;
   QueryBuildDatasource     queryBuildDatasource1,
                            queryBuildDatasource2;
   QueryBuildRange          queryBuildRange;
   QueryBuildLink           queryBuildLink;

   QueryRun                 queryRun;
   PurchTable               purchTable;
   PurchLine                purchLine;
   ;

   /*
   query = new Query();
   queryBuildDatasource1   = query.addDataSource(tablenum(purchTable));
   queryBuildRange         = queryBuildDatasource1.addRange(fieldnum(purchTable,PurchID));
   queryBuildRange.value("PO12-003295..PO12-003299");

   queryBuildDatasource2   = queryBuildDatasource1.addDataSource(tablenum(purchLine));
   queryBuildLink          = queryBuildDatasource2.addLink(fieldnum(purchTable,purchID),fieldnum(purchLine,PurchId));
   */
   query = new Query();
   queryBuildDatasource1   = query.addDataSource(tablenum(purchLine));
   queryBuildRange         = queryBuildDatasource1.addRange(fieldnum(purchLine,PurchID));
  // queryBuildRange.value("PO12-003295..PO12-003299");

   queryBuildDataSource1.orderMode(OrderMode::GROUPBY);
   queryBuildDataSource1.addSortField(fieldnum(purchLine,PurchID));

   queryBuildDataSource1.addSelectionField(FieldNum(purchLine,LineAmount), SelectionField::SUM);

   queryBuildDatasource2   = queryBuildDatasource1.addDataSource(tablenum(purchTable));
   queryBuildLink          = queryBuildDatasource2.addLink(fieldnum(purchLine,purchId),fieldnum(purchTable,PurchId));

   queryRun   =  new QueryRun(query);
   while(queryRun.next())
   {
  //   purchTable  =  queryRun.get(tablenum(purchTable));
     purchLine   =  queryRun.get(tablenum(PurchLine));
     pause;
     print purchLine.PurchId;
     print purchLine.LineAmount;
     print "--";
     print "--";
     print "--";
     print "--";
     print "--";
    // PurchLine.PurchId));


   }


}








/*
Query                             query;
QueryBuildDataSource     queryBuildDataSource;
query = new Query();  
queryBuildDataSource = query.addDataSource(TableNum(AssetTrans));
queryBuildDataSource.orderMode(OrderMode::GROUPBY);  
queryBuildDataSource.addSelectionField(FieldNum(AssetTrans,amountMST), SelectionField::SUM);  
queryBuildDataSource.addSelectionField(FieldNum(AssetTrans,revaluationAmount), SelectionField::SUM);  
queryBuildDataSource.addSortField(FieldNum(AssetTrans, transType));  
queryBuildDataSource.addRange(FieldNum(AssetTrans,assetId)).value(rangeAssetId);  
queryBuildDataSource.addRange(FieldNum(AssetTrans,bookId)).value(rangeBookId);  
queryBuildDataSource.addRange(FieldNum(AssetTrans, transDate)).value(queryRange(dateFrom, dateTo));
    return query;

//Result of the above Query:  SELECT SUM(AmountMST), SUM(RevaluationAmount) FROM AssetTrans //GROUP BY AssetTrans.TransType ASC WHERE ((AssetId = FA-000001)) AND ((BookId = COMPUTERS)) //AND ((TransDate<=12/31/2153))

*/

/*
Query                             query = new Query();  
QueryBuildDataSource     queryBuildDataSource;
QueryBuildRange             queryBuildRange;
 
queryBuildDataSource = query.addDataSource(tablenum(PurchParmTable));
queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, Ordering));
querybuildrange.value(queryvalue(documentstatus::PackingSlip));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, PurchId));
querybuildrange.value(queryvalue(purchparmtable.PurchId));  

queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, ParmJobStatus));
querybuildrange.value(queryvalue(‘Executed’));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(PurchParmTable, Invoiced));  
querybuildrange.value(queryvalue(‘No’));

  this.query(query);

*/