Modify Query on Report DP Class example

[SysEntryPointAttribute]
public void processReport()
{
    //query class
    QueryRun            qr;
    Query               q;
    QueryBuildDataSource qbds;
    //table object
    InventSum           inventSum;
    WMSLocation         wmsLocation;
    RetailStoreTable    retailStoreTable;
    TIDLocType          tidLocType;
    //
    str                 headerName;

    contract = this.parmDataContract() as TIDStoreDisplayReportContract;
    parmShowQty = contract.parmShowQty();
    parmFilterItem = contract.parmStoreDisplayFilterItem();

    q = this.parmQuery();

    q.dataSourceTable(tableNum(WMSLocation)).addRange(fieldNum(WMSLocation,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
    switch(parmFilterItem)
    {
        case TIDStoreDisplayFilterItem::OnlyDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::Yes));
            headerName = "Only Display Item";
            break;
        case TIDStoreDisplayFilterItem::NoDisplay :
            qbds = q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::NoExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "No Display Item";
            break;
        case TIDStoreDisplayFilterItem::NotInDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::No));
            headerName = "Not in Display Item";
            break;
        case TIDStoreDisplayFilterItem::HaveDisplay :
            qbds =  q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::ExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "Have Display Item";
            break;
        default :
            headerName = "Store Display Item";
            break;
    }

    // only display > 0
    q.dataSourceTable(tableNum(InventSum)).addRange(fieldNum(InventSum,AvailPhysical)).value(queryValue(">0"));

    qr = new QueryRun(q);

    while(qr.next())
    {
        inventSum   = qr.get(tableNum(inventSum));
        wmsLocation = qr.get(tableNum(wmsLocation));
        tidLocType  = qr.get(tableNum(TIDLocType));

        tempTable.clear();
        tempTable.headerName        = headerName;
        tempTable.ItemId            = inventSum.ItemId;
        tempTable.EcoResDescription = TIDItemDisplayCustom::getItemDescription(inventSum.ItemId);
        tempTable.EcoResProductName = TIDItemDisplayCustom::getItemName(inventSum.ItemId);
        tempTable.NameAlias         = TIDItemDisplayCustom::getItemModel(inventSum.ItemId);
        tempTable.AvailPhysical     = inventSum.AvailPhysical;
        select firstOnly retailStoreTable where retailStoreTable.inventLocation == wmsLocation.inventLocationId;
        tempTable.Store             = retailStoreTable.StoreNumber;
        tempTable.WMSLocationId     = wmsLocation.wMSLocationId;
        tempTable.IsDisplay         = tidLocType.TIDIsDisplay;
        tempTable.insert();

    }

}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s