Langsung ke konten utama

QBDS Query Range

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

sumber  












Label:































Komentar











Posting Komentar



















Postingan populer dari blog ini



















Use X++ wildcard (LIKE and NOT LIKE) in X++ select statement




























For x++ select statements:  select firstOnly batchHistory      where batchHistory.Caption  LIKE  "*Test*"  For x++ queries:  queryBuildRange.value(*Test*); Note the LIKE instead of a '==' and the wildcards inside of the quotations. All other combinations of wildcard usage will work here. This is the same functionality as what the users would put in the grid filtering(eg. '*TEST*' in the caption field filter on the batch tasks form).  However, if you want to find all Captions that do not have the word Test in them (NOT LIKE, !LIKE), you will have to modify the above example slightly.  For x++ select statements:  select firstOnly batchHistory      where  !( batchHistory.Caption LIKE "*TEST*" ) ;  For x++ queries:  queryBuildRange.value(!*Test*);




















Posting Komentar









Baca selengkapnya






























Format Label Tom Jerry (MS Word)




























  Setelah beberapa lama aku tidak membuat label undangan, tiba-tiba kemarin aku diminta untuk membuat label undangan Wisuda dengan menggunakan label merek Tom & Jerry (TJ). Harusnya sih menjadi tugas sekretaris panitia tapi karena yang bersangkutan ada tugas di luar kantor maka aku yang membereskannya.   Mula-mula aku membuat format labelnya dengan cara manual, tapi untung ada pak Mulyadi (staf tata usaha) yang menyarankan aku untuk mengunduh format label yang telah ada di Internet. Mulailah aku browsing di google dan menemukan format label berbagai ukuran dari website Tom & Jerry di alamat berikut  http://tjlabels.com/en/download.html  (yang link ini agak rempong krn pake masukin nomor captcha-captcha-an). Sedangkan kalau yang link ini langsung pilih file ga pake nomor captcha-captcha-an ( http://tjlabels.com/assets/download/ ).   Ternyata juga ada banyak berbagai blog yang menyediakan  link download seperti ini, tapi aku mem- posting  artikel ini ...




















Posting Komentar









Baca selengkapnya




























Solved : Update on a valid time state table is not allowed without specifying a ValidTimeStateUpdateMode




























 How To Solve Error :    Cannot edit a record in Position hierarchies (xyz). Update on a valid time state table is not allowed without specifying a ValidTimeStateUpdateMode.     select forUpdate * from xyz where xyz.recid== _RecId ;   try {   ttsBegin;   _Value= “Abcd”;   xyz.Name = _Value;   xyz.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);   xyz.ValidFrom =today();   xyz.ValidTo=dateMax();   xyz.update();   ttsCommit;   }   catch   {   ttsAbort;   }  




















Posting Komentar









Baca selengkapnya