[Dynamic AX/365] 在FORM QUERY 內加減FILTER (QueryRange) @ 呆子獅的夢想世界

	QueryBuildDataSource 	qbds;
	queryBuildRange			qbr;
	;
	qbds = this.query().dataSourceTable(tablenum(SalesQuotationTable));
	qbds.clearRanges();
	qbdr = qbds.addRange(fieldnum(SalesLine, PlanModelId));

	//STRING
	qbds.addRange(fieldnum(SalesLine, PlanModelId)).value(SysQuery::valueEmptyString()); // = value('=""');
	qbds.addRange(fieldnum(SalesLine, PlanModelId)).value('!=""');
	qbds.addRange(fieldnum(SalesLine, PlanModelId)).value('1');
	qbds.addRange(fieldnum(SalesLine, PlanModelId)).value(queryValue('!=' + planModel.PlanModelId));

	queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSLocation, SPLCustAccount));
	queryBuildRange.value('=""');				-> SPLCUSTAccount=''  -> ((SPLCUSTAccount=''))
	queryBuildRange.value(SysQuery::valueEmptyString());	-> SPLCUSTAccount=''  -> ((SPLCUSTAccount=''))
	queryBuildRange.value(queryValue(''));			-> SPLCUSTAccount=''  -> ((SPLCUSTAccount=''))

	queryBuildRange.value('!=""');  			-> !SPLCUSTAccount='' -> ((!SPLCUSTAccount=''))

	queryBuildRange.value('');	-> ATTENTION : THE Range is not added to the query !!!!!!!!!!
	
	//-> To initialize the range, the range is not took in the query (= value('') ?) 
	//value(""); is good also for integer and date.
	queryBuildRange.value("");	-> To initialize the range, the range is not took in the query.

//Range LIKE => value("*AA*")
    if (SearchText)
    {
        SearchText = "*" + SearchText + "*";
        if (ServiceSearchType == SPLWasteServiceSearchType::ServiceOrder)
        {
            qbds.addRange(fieldNum(SMAServiceOrderTable,serviceOrderId)).value(SearchText);
        }
        else if (ServiceSearchType == SPLWasteServiceSearchType::Customer)
        {
            qbds.addRange(fieldNum(SMAServiceOrderTable,CustAccount)).value(SearchText);
        }
    }
	-> range "*SearchText*" = LIKE

Range for integer

	qbds.addRange(fieldNum(SalesLine, SalesQty)).value(queryValue('>0'));
	qbds.addRange(fieldNum(SalesLine, SalesQty)).value('>0');

	note for integer : (value('>0') == value(queryValue('>0')))

Range for enum

	qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(queryValue(SalesQuotationStatus::Created));
	qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(queryValue('!=' + enum2str(SalesQuotationStatus::Created)));

	//ENUM - the different way to write a value for an enum.
	qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(enum2str(SalesQuotationStatus::Created));
	qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(enum2value(SalesQuotationStatus::Created));  //to check
	qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(SysQuery::value(SalesQuotationStatus::Created));
	qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(queryValue(SalesQuotationStatus::Created));
	
	//example code to add list of codes
	public void executeQuery()
	{
		qbrWMSPickingRouteType.value(element.strRangePickingRoutesTypes());
		super();
	}	
	public str strRangePickingRoutesTypes()
	{
		str                 selectionStr = '';
		if (showWMS)
		{
			selectionStr +=enum2str(WMSShipmentType::ConsolidatedPick);
		}
		if (!selectionStr || (showPick && showWMS))
		{
			return enum2str(WMSShipmentType::ConsolidatedPick) + ',' + enum2str(WMSShipmentType::OrderPick);
		}
		return selectionStr;
	}	
	
	statusRange = queryBuildDataSource.addRange(fieldNum(WMSTransport, ExpeditionStatus));
    statusRange.value(strFmt('%1',  enum2Value(WMSExpeditionStatus::Activated)));

Range for date

qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryValue(dateNull()));
qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryValue('!0'));
qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryValue('>0'));
reconciledDate.value('>' + queryValue(dateNull()));

reconciledDate.value(''); ? = reconciledDate.value(queryValue(dateNull()));


qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryRange(_startDateStart, _startDateEnd));
qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryValue(_fromDate) + '..' + queryValue(_toDate));

--> (periodStart .. periodEnd)
criteriaStartDate.value(queryRange(period.PeriodStart,period.PeriodEnd));

--> (dateNull or (aujourd'hui et max)).
queryBuildRange.value(queryValue(dateNull()) + ',' + queryRange(today(), dateMax()));

FromDate            fromDate;
ToDate              toDate;
qbr.value(queryValue(fromDate)+ '..'+ queryValue(toDate));


//Range : EndDate = Null OR EndDate >= DateJour : (we need to define 2 times the QueryBuildRange)
queryBuildRangeDate = queryBuildDataSource1.addRange(fieldNum(SMAAgreementTable,EndDate));
queryBuildRangeDate.value(queryValue(dateNull()));
queryBuildRangeDate = queryBuildDataSource1.addRange(fieldNum(SMAAgreementTable,EndDate));
queryBuildRangeDate.value(queryRange(systemDateGet(),dateMax()));

Range for All dates

//This example show with date how to use the null date. 
//If null date and we want to see all the lines we need to write qbrDate.value('');
public class FormRun extends ObjectRun
{
    date                            dateSearch;
    QueryBuildRange                 qbrDate;
}
//Init of the form
public void init()
{
    dateSearch      = systemDateGet() + 1;
    super();
    DateHeader.dateValue(dateSearch);
}
//Init of the DS
public void init()
{
    QueryBuildDataSource    qbds;
    super();
    qbds = this.query().dataSourceTable(tablenum(SPLDailyBID));
    qbrDate = qbds.addRange(fieldNum(SPLDailyBID,BIDDate));
    qbrDate.value(QueryValue(dateSearch));
}
//Modified of the field Date
public boolean modified()
{
    boolean ret;
    ret = super();

    if (this.dateValue())
    {
        qbrDate.value(QueryValue(this.dateValue()));
    }
    else
    {
        qbrDate.value('');
    }
    SPLDailyBID_ds.executeQuery();
    return ret;
}

Range for date Time

class SMAServiceFunctionLine
protected void setQueryDatePeriod()
{
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    utcdatetime             dateFrom;
    utcdatetime             dateTo;

    qbds = queryRun.query().dataSourceTable(tableNum(SMAServiceOrderTable));
    dateFrom = DateTimeUtil::newDateTime(periodFromDate, 0);
    dateTo = DateTimeUtil::newDateTime(periodToDate, 0);
    // Add 1 day to get inclusive range
    dateTo = DateTimeUtil::addDays(dateTo, 1);

    if (periodFromDate && periodToDate)
    {
        qbr = qbds.addRange(fieldNum(SMAServiceOrderTable, ServiceDateTime));
        qbr.value(strFmt('((%1 >= %2) && (%3 < %4))',
            fieldStr(SMAServiceOrderTable, ServiceDateTime), DateTimeUtil::toStr(dateFrom),
            fieldStr(SMAServiceOrderTable, ServiceDateTime), DateTimeUtil::toStr(dateTo)));
    }
    else if (periodFromDate)
    {
        qbr = qbds.addRange(fieldNum(SMAServiceOrderTable, ServiceDateTime));
        qbr.value(strFmt('(%1 >= %2)',
            fieldStr(SMAServiceOrderTable, ServiceDateTime), DateTimeUtil::toStr(dateFrom)));
    }
}

Range with strfmt

//example 1
	queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSPickingRoute, ExpeditionStatus));
	queryBuildRange.value(enum2Value(WMSExpeditionStatus::Activated));
	queryBuildRange.status(RangeStatus::Locked);

	queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSPickingRoute, ExpeditionStatus));
	queryRange = strfmt('((' + fieldstr(WMSPickingRoute, ExpeditionStatus) + ' == WMSExpeditionStatus::Started) && ('+ fieldstr(WMSPickingRoute, Operator) + ' == "%1"))', _emplId);
	queryBuildRange.value(queryRange);
	queryBuildRange.status(RangeStatus::Locked);

	((expeditionStatus = 3) OR
	-> (((ExpeditionStatus == WMSExpeditionStatus) && (Operator == "0110"))))

//example 2
	queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSPickingRoute, ExpeditionStatus));
	queryBuildRange.value(enum2Value(WMSExpeditionStatus::Activated));
	queryBuildRange.status(RangeStatus::Locked);

	queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSPickingRoute, ExpeditionStatus));
	queryRange = strfmt('((' + fieldstr(WMSPickingRoute, ExpeditionStatus) + ' == WMSExpeditionStatus::Started) && ('+ fieldstr(WMSPickingRoute, Operator) + ' == "%1"))', _emplId);
	queryBuildRange.value(queryRange);
	queryBuildRange.status(RangeStatus::Locked);

	((expeditionStatus = 3) OR
	-> (((ExpeditionStatus == WMSExpeditionStatus) && (Operator == "0110"))))

//example 3
	queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',
	fieldStr(InventTable, ItemType),
	any2int(ItemType::Service),
	any2int(ItemType::Item),
	fieldStr(InventTable, ProjCategoryId),
	queryValue("Spares")));

	if (inventTableModule.LineDisc)
	{
		qbr = qBuild.addRange(fieldnum(LPLSalesDealLine,ItemCode));
				strQuery = strFmt('((%1 == %2) && (%3 == "%4"))',
				fieldStr(LPLSalesDealLine, ItemCode),
				any2int(LPLTableGroupBrandBgAll::All),
				fieldStr(LPLSalesDealLine, ItemRelation),
				"");                                                       <---use "" and not SysQuery::valueEmptyString()
		qbr.value(strQuery);
	}
	
//example 4
Query hideReturnReservationLine(Query _query)
{
	QueryBuildRange      qbrHideReservationLine;
	QueryBuildDataSource qbdsSalesLine = _query.dataSourceTable(tablenum(SalesLine));
	int                  statusAwaiting = ReturnStatusLine::Awaiting;
	;

	qbrHideReservationLine = qbdsSalesLine.addRange(fieldnum(SalesLine, ReturnStatus));
	qbrHideReservationLine.value(strfmt('((%1.%4 != %5) || (%1.%2 < %3)&&(%1.%4 == %5))',
	qbdsSalesLine.name(),//%1
	fieldstr(SalesLine, ExpectedRetQty),//%2
	0, //%3
	fieldstr(SalesLine, ReturnStatus), //%4
	statusAwaiting)); //%5
	qbrHideReservationLine.status(RangeStatus::Hidden);

	return _query;
}

Range with strfmt and field T.X = T.Y

	&&  (vendTrans.TransType     == LedgerTransType::Payment ||
		(vendTrans.Correct == NoYes::Yes && vendTrans.TransType == LedgerTransType::Vend  &&
		(vendtrans.BPLAdvPayment == '' || (vendTrans.LastSettleVoucher == vendtrans.BPLAdvPayment && vendtrans.BPLAdvPayment != ''))))

    qbdsVendTrans	= qbdsLedgerTrans.addDataSource(tablenum(VendTrans),"VendTrans"); //Here "Vendtrans" because if not : qbdsVendTrans.name() -> vendTrans_1 !
    qbrTransType 	= qbdsVendTrans.addRange(fieldnum(VendTrans, TransType));
    qbrTransType.value(strfmt('( (%1.%2 == %6) || ( (%1.%3 = %7) && (%1.%2 == %8) && ( (%1.%4 == %9) || ( (%1.%5 == %1.%4) && (%1.%4 != %9) ))))',
    qbdsVendTrans.name(),                       //%1
    fieldstr(VendTrans, TransType),             //%2
    fieldstr(VendTrans, Correct),               //%3
    fieldstr(VendTrans, BPLAdvPayment),         //%4
    fieldstr(VendTrans, LastSettleVoucher),     //%5
    any2int(LedgerTransType::Payment),          //%6
    any2int(NoYes::Yes),                        //%7
    any2int(LedgerTransType::Vend),             //%8
    SysQuery::valueEmptyString()));             //%9   // (%1.%4 == %9) -> VendTrans.BPLAdvPayment == ""  //I check SysQuery::valueEmptyString() this is good.

RangeStatus

	QueryBuildRange         qbr;
	;
	qbr = queryBuildDataSource.addRange(fieldnum(Address, Address));
	qbr.value(queryExpression);
	qbr.status(RangeStatus::Hidden);// - RangeStatus::Hidden - RangeStatus::Locked

QueryRangeConcat

	//1)
	queryBuildRange.value(queryRangeConcat(SysQuery::value(InventQuarantineStatus::Created), SysQuery::value(InventQuarantineStatus::ReportedFinished)));
	//2)
	str	queryRange = '';

	queryRange = queryRangeConcat(queryRange,queryValue(TaxDirection::IncomingTax));
	queryRange = queryRangeConcat(queryRange,queryValue(TaxDirection::TaxExemptPurchase));
	queryRange = queryRangeConcat(queryRange,queryValue(TaxDirection::OutgoingTax));
	queryRange = queryRangeConcat(queryRange,queryValue(TaxDirection::TaxExemptSales));
	queryBDS.addRange(fieldnum(TaxTrans, TaxDirection)).value(queryRange);

	range = queryRangeConcat('',StatusIssue::ReservPhysical);
	range = queryRangeConcat(range,StatusIssue::ReservOrdered);
	range = queryRangeConcat(range,StatusIssue::OnOrder);
	range = queryRangeConcat(range,StatusIssue::QuotationIssue);
	qbds_InventTrans2.addRange(fieldnum(InventTrans,StatusIssue)).value(range);

	//3)
	queryBuildDataSource.addRange(fieldnum(WMSOrderTrans,OrderType)).value(Global::queryRangeConcat(queryValue(WMSOrderType::OrderPick),WMSOrderType::Output));
	-> ((orderType = 3 OR orderType = 0))

	//4     rangeStatus = queryRangeConcat(queryNotValue(WMSShipmentStatus::Shipped),queryNotValue(WMSShipmentStatus::Canceled));
	-> ((NOT (status = 9)) AND NOT (status = 3)))

Range A = "X" or A = "Y"

	qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));

	-> qbr         = wMSLocationId = N'A00' or wMSLocationId = N'A01' OR wMSLocationId = N'A02'
	-> rangeWMSLoc = "A00,A01,A02"
	qbr.value(rangeWMSLoc) -> ((wMSLocationId = N'A00' or wMSLocationId = N'A01' OR wMSLocationId = N'A02'))

	qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));
	qbr.value("A00");
	qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));
	qbr.value("A01");
	qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));
	qbr.value("A02");
	-> ((wMSLocationId = N'A00' or wMSLocationId = N'A01' OR wMSLocationId = N'A02'))

FindRange

	range rangeWMSLoc;
	;
	qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));
	if (qbr)
	   rangeWMSLoc = qbr.value();

Find or create range

    qbrWMSPickingRouteType = SysQuery::findOrCreateRange(qbdsWMSPickingRoute,fieldnum(WMSPickingRoute,ShipmentType));

List of the range

	int                     cnt, i;
	QueryBuildRange         qbr;
	Query                   queryFreeText;
	QueryBuildDataSource    qdbsFreeText;

	queryFreeText = new Query();
	qdbsFreeText  = queryFreeText.addDataSource(tableNum(VendInvoiceJour));

	cnt = qr.query().dataSourceTable(tablenum(VendInvoiceJour)).rangeCount();

	for (i=1 ; i<=cnt; i++)
	{
		qbr = qr.query().dataSourceTable(tablenum(VendInvoiceJour)).range(i);
		qdbsFreeText.addRange(qbr.field()).value(qbr.value());
	}

Add range on the same field x times

//AddRange on the same field x times.
public void linkActive()
{
    SalesTable          localSalesTable;
    InventTransferTable localInventTransferTable;
    SalesLine           localSalesLine;

    if (salesTable_ds)
    {
        this.query().dataSourceTable(tablenum(WMSPickingRouteLink)).clearDynalinks();
        this.query().dataSourceTable(tablenum(WMSPickingRouteLink)).clearRanges();

        for (localSalesTable = salesTable_ds.getFirst(true) ? salesTable_ds.getFirst(true) : salesTable_ds.cursor(); localSalesTable; localSalesTable = salesTable_ds.getNext())
        {
            this.query().dataSourceTable(tablenum(WMSPickingRouteLink)).addRange(fieldnum(WMSPickingRouteLink, OrigInventTransRefId)).value(queryValue(localSalesTable.SalesId));
        }
    }
    super();
}

Range with fieldName2Id

// Range using fieldName2id;
   if (HeaderDateMonthYear.dateValue())
    {
        iMonth = MthOfYr(HeaderDateMonthYear.dateValue());
        fieldName = #SMAFormulaFieldMonth + strRFix(int2str(iMonth),2,"0");
        qbdsSPLWasteSMAFormula.addRange(fieldName2id(tableNum(SPLWasteSMAFormula),fieldName)).value(queryValue(NoYes::Yes));
    }
	
	//======getFieldValue ============//
	//in the table I have :
	//Month01
	//Month02
	//Month03 ...
	//#SMAFormulaFieldMonth = "Month"
    for (i = 1; i <= 12; i++)
    {
        MonthValue = this.getFieldValue(#SMAFormulaFieldMonth + strLFix(int2str(i),2,"0"));
        if (MonthValue)
        {
            transDate = mkDate(01,i,iYear);
            return transDate;
        }
    }	
	
// another example class MultiSelectionHelper, called in SMAServiceFunctionLine

multiSelectionHelper.createQueryRanges(qbdsAgreementLine, fieldstr(SMAAgreementLine, AgreementLineNum)); 

public void createQueryRanges(QueryBuildDataSource _queryBuildDataSourece, FieldName _fieldName, FieldName _recordFieldName = _fieldName)
{
    fieldId         datasourceFieldId;
    fieldId         recordFieldId;
    Common          record;

    // currentRecord and currentRecordIdx should be restored before return
    Common          copyOfCurrentRecord     = currentRecord;
    int             copyOfCurrentRecordIdx  = currentRecordIdx;
    ;

    if (!_queryBuildDataSourece)
    {
        throw error("@SYS96323");
    }

    record = this.getFirst();

    if (record)
    {
        datasourceFieldId   = fieldname2id(_queryBuildDataSourece.table(), _fieldName);
        recordFieldId       = fieldname2id(record.TableId, _recordFieldName);

        if (!datasourceFieldId)
        {
            throw error(strFmt("@SYS94058", _fieldName, _queryBuildDataSourece.name()));
        }

        if (!recordFieldId)
        {
            throw error(strFmt("@SYS75684", _recordFieldName, tableid2name(record.TableId)));
        }
    }


    while (record)
    {
        _queryBuildDataSourece.addRange(datasourceFieldId).value(SysQuery::value(record.(recordFieldId)));
        record = this.getNext();
    }

    currentRecord       = copyOfCurrentRecord;
    currentRecordIdx    = copyOfCurrentRecordIdx;
}

Note

	SysQuery::value() = queryValue()
	EX : SysQuery::value(InventQuarantineStatus::Created)  = queryValue(InventQuarantineStatus::Created)

	qbr.value('!=""');   !!!*** qbr.value("!=''"); doesn't work ***!!!
	//QueryValue is used if I write a variable inside the value :
	qbr.value('!=""'); qbr.value(queryValue('!=' + planModel.PlanModelId));

lionlionchopper

呆子獅的夢想世界

lionlionchopper 發表在痞客邦留言(0) 人氣()

E-mail轉寄

呆子獅的夢想世界

工作 X 旅行 X 玩樂＠食玩訓人生觀（＠＿＠↑）

[Dynamic AX/365] 在FORM QUERY 內加減FILTER (QueryRange)

QueryRange

Range for string

Range for integer

Range for enum

Range for date

Range for All dates

Range for date Time

Range with strfmt

Range with strfmt and field T.X = T.Y

RangeStatus

QueryRangeConcat

Range A = "X" or A = "Y"

FindRange

Find or create range

List of the range

Add range on the same field x times

Range with fieldName2Id

Note

留言列表

站方公告

活動快報

痞客邦...

我的好友

熱門文章

文章分類

最新文章

最新留言

動態訂閱

文章精選

文章搜尋

新聞交換(RSS)

誰來我家

參觀人氣

QR Code

POWERED BY

工作 X 旅行 X 玩樂 ＠ 食玩訓 人生觀 （＠＿＠↑）

留言列表

站方公告

活動快報

我的好友

熱門文章

文章分類

最新文章

最新留言

動態訂閱

文章精選

文章搜尋

新聞交換(RSS)

誰來我家

參觀人氣

QR Code

POWERED BY

工作 X 旅行 X 玩樂＠食玩訓人生觀（＠＿＠↑）