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 ranges
Add range on the same field x times
Range with fieldName2Id
Range with fieldName2Id
Note


Range for string

	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

呆子獅的夢想世界

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