Item Price Analysis Code %%%

// Item Price Analysis Code %%%

 InventTable         inventTable;
    InventTrans         inventTrans;
    InventSum               inventSum;
    Query                   query;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbrItemid,qbrItemGroup;
    QueryRun                qr;
    DimensionAttributeValueSetStorage   dimStorage;
    InventTableModule       inventTableModule;
    CustInvoiceTrans        custInvoiceTransMax,custInvoiceTrans,custInvoiceTransMin;
    InventTransOrigin       inventTransOrigin;
    Amount                  amount,minPrice,amountDiv,sales25,sales50,sales75,sales100;
    int i;

    super();
    delete_from itempriceAnalysis;
    query = new Query();
    qbds = query.addDataSource(tableNum(InventTable));
    _item = ItemId.valueStr();
    _itemBuyerGroup = ItemGroup.valueStr();
    _subDivision = SubDivision.valueStr();
    if(_item)
    {
        qbrItemid = qbds.addRange(fieldNum(InventTable,ItemId));
        qbrItemid.value(strReplace(_item,";",","));
    }
    if(_itemBuyerGroup)
    {
        qbrItemGroup = qbds.addRange(fieldNum(InventTable,ItemBuyerGroupId));
        qbrItemGroup.value(strReplace(_itemBuyerGroup,";",","));
    }
    qr = new QueryRun(query);
    while(qr.next())
    {
        inventTable = qr.get(tableNum(InventTable));

        dimStorage = DimensionAttributeValueSetStorage::find(inventTable.DefaultDimension);
        itempriceAnalysis.clear();
        if (_subDivision && (_subDivision!= dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName('SubDivisions').RecId)))
        {
                continue;
        }
        itempriceAnalysis.SubDivision = dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName('SubDivisions').RecId);
        itempriceAnalysis.ItemId = inventTable.ItemId;
        itempriceAnalysis.ItemBuyerGroupId = inventTable.ItemBuyerGroupId;
        itempriceAnalysis.ItemName = inventTable.itemName();
        select inventSum where inventSum.ItemId == inventTable.ItemId;
        itempriceAnalysis.CurrentUnitCost = inventSum.averageCostPrice();

        select * from inventTrans order by RecId desc
                where inventTrans.ItemId == inventTable.ItemId
                        && inventTrans.StatusReceipt == StatusReceipt::Purchased
                join inventTransOrigin where inventTransOrigin.RecId == inventTrans.InventTransOrigin
                                && inventTransOrigin.ReferenceCategory == InventTransType::Purch;
             itempriceAnalysis.LastPurchasePrice =  inventTrans.costValue();

        select minof(SalesPrice) from custInvoiceTransMin
                where custInvoiceTransMin.ItemId == inventTable.ItemId;
            itempriceAnalysis.LowestSalesPrice = custInvoiceTransMin.SalesPrice;

        select maxOf(SalesPrice) from custInvoiceTransMax
                where custInvoiceTransMax.ItemId == inventTable.ItemId;
            itempriceAnalysis.HighestSalesPrice = custInvoiceTransMax.SalesPrice;
           amount = custInvoiceTransMax.SalesPrice - custInvoiceTransMin.SalesPrice;

        amountDiv   = amount/4;
        //if (amountDiv != 0.00)
        //{
            minPrice    = custInvoiceTransMin.SalesPrice;
            sales25     = minPrice + amountDiv;
            sales50     = sales25 + amountDiv;
            sales75     = sales50 + amountDiv;
            sales100    = sales75 + amountDiv;


        while select custInvoiceTrans
                where custInvoiceTrans.ItemID == inventTable.ItemId
        {

            if(custInvoiceTrans.SalesPrice >= minPrice && custInvoiceTrans.SalesPrice <= sales25)
                itempriceAnalysis.SalesValue25 += custInvoiceTrans.LineAmount;
            if(custInvoiceTrans.SalesPrice > sales25 && custInvoiceTrans.SalesPrice <= sales50)
                itempriceAnalysis.SalesValue50 += custInvoiceTrans.LineAmount;
             if(custInvoiceTrans.SalesPrice > sales50 && custInvoiceTrans.SalesPrice <= sales75)
                itempriceAnalysis.SalesValue75 += custInvoiceTrans.LineAmount;
             if(custInvoiceTrans.SalesPrice > sales75 && custInvoiceTrans.SalesPrice <= sales100)
                itempriceAnalysis.SalesValue100 += custInvoiceTrans.LineAmount;

        }

        //}

        itempriceAnalysis.insert();

    }
    ItemPriceAnalysisTmp_ds.research();
    ItemPriceAnalysisTmp_ds.refresh();