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





















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 adalah untuk diri sendiri, ya




















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






























Cara membuat Harddisk External menjadi Bootable USB untuk install Windows




























 Untuk install windows dari harddisk external, silahkan siapkan hal hal berikut ini gan :     Harddisk External  File Image (ISO) dari Windows yang mau digunakan  Laptop/ Komputer       Oke, kita masuk ke  LANGKAH PERTAMA :      Setelah kita persiapkan hal hal yang ada diatas, ada baiknya kalo antivirus / Windows Defender nya itu matiin dulu (recommended) bagi harddisk nya terdapat banyak crack. Karena crack itu bersifat virus dan pasti bakalan langsung keapus sama antivirusnya.      Pastiin kalo harddisk ente itu ada space minimal 5 – 10 Gb untuk penempatan File Image Windows yang akan dipakai. Recommended 10Gb    Abis harddisk nya di colokkk silahkan buka  Control Panel  >  Administrative Tools                 Lalu buka  Computer Management              Kalo udah kebuka, pilih menu  Disk Management  yang ada di toolbar kiri. Contoh dibawah ini              Karena ane make harddisk yang merknya Toshiba, maka yang akan kedetek itu  Wibi Toshiba  pada bagian  Disk 1      Langung  Kli




















Posting Komentar









Baca selengkapnya