SSRS Process report query::insert_recordset

  public void processReport()

    {

        CompanyInfo          companyInfo;

        List                 itemIdList = new List(Types::String), itemGroupList = new List(Types::String);

        ALM_InventFormulaDynParameter _ALM_InventFormulaDynParameter;

        InventTableModule                           _inventTableModule;

        InventTableExpanded _InventTableExpanded;

        contract             = this.parmDataContract() as Alm_InventFormulaContract;

        fromDate             = contract.parmFromDate();

        toDate               = contract.parmToDate();

        itemIdList           = contract.parmItemId();

        itemGroupList        = contract.parmItemGroup();

        testItemId           = itemIdList.toString();

        testItemId           = strRem(testItemId,"<");

        testItemId           = strRem(testItemId,">");

        testItemGroup        = itemGroupList.toString();

        testItemGroup        = strRem(testItemGroup,"<");

        testItemGroup        = strRem(testItemGroup,">");

        //deleting data in stagging table

        ttsbegin;

        delete_from inventFormulaStagging where inventFormulaStagging.CreatedBy == curUserId();

        ttscommit;

        while select _ALM_InventFormulaDynParameter

        {

            if(_ALM_InventFormulaDynParameter.ALM_ColumnSource == ALM_ColumnSource::InventTransaction)

            {

                if(_ALM_InventFormulaDynParameter.InventTransPostingType == InventTransPostingType::Financial)

                   this.insertingIntoStaggingInventTrans(_ALM_InventFormulaDynParameter);

                else  if(_ALM_InventFormulaDynParameter.InventTransPostingType == InventTransPostingType::Physical)

                   this.insertingIntoStaggingInventTransPhysical(_ALM_InventFormulaDynParameter);

            }

            else if(_ALM_InventFormulaDynParameter.ALM_ColumnSource == ALM_ColumnSource::SalesTransaction)

                 this.insertingIntoStaggingCustomer(_ALM_InventFormulaDynParameter);

        }

        select firstonly forupdate inventFormulaStagging;

        {

            if(inventFormulaStagging)

            {

                inventFormulaStagging.CompanyName      = CompanyInfo::find().Name;

                inventFormulaStagging.CompanyLogo      = Formletter::companyLogo();

                ttsbegin;

                inventFormulaStagging.update();

                ttscommit;

            }

        }

        update_recordset inventFormulaStagging

        setting ItemName = _InventTableExpanded.ProductName,               

                UOM          = _inventTableModule.UnitId,

               ItemGroup = _InventTableExpanded.ItemGroupId

               

        join ProductName,ItemGroupId from _InventTableExpanded

        where _InventTableExpanded.ItemId == inventFormulaStagging.ItemId

        join UnitId from _inventTableModule

        where _inventTableModule.ItemId == _InventTableExpanded.ItemId && _inventTableModule.ModuleType == ModuleInventPurchSales::Invent;

        while select ItemId,count(RecId) from  inventFormulaStagging

            group by inventFormulaStagging.ItemId

            where inventFormulaStagging.CreatedBy == curUserId()

        {

            this.updateOpenBalance(inventFormulaStagging.ItemId,0);

        }

    }

public void insertingIntoStaggingInventTransPhysical(ALM_InventFormulaDynParameter _ALM_InventFormulaDynParameter)

    {

        Alm_InventFormulaView              inventFormulaView;

        ALM_InventFormulaSalesView         inventFormulaSalesView;

       

        InventTransOrigin                  inventTransOrigin;

        InventTransPosting                 inventTransPosting;

        DimensionAttributeValueCombination combination;

        RetailTransactionSalesTrans        salesTrans;

        InventTable                        inventTable;

        InventTrans                        inventTrans;

        TransDate                          openBalDate = fromDate - 1;

      

        Query                               query;

        QueryBuildDataSource                qbds;

        QueryBuildFieldList                 fldList_InventFormulaStagging;

        Map                                 fieldMapping;

      

        

        query   = new Query();

        qbds    = query.addDataSource(tableNum(ALM_InventFormulaViewPhysical));

        qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, DatePhysical)).value(SysQuery::range(fromDate,toDate));

        if(testItemId)

        {

            qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ItemId)).value(testItemId);

        }

        //if(testItemGroup)

        //{

        //    qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ItemGroup)).value(testItemGroup);

        //}

        if(_ALM_InventFormulaDynParameter.Account !="")

        {

            qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, MainAccountValue)).value(_ALM_InventFormulaDynParameter.Account);

        }

    

        qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, InventTransPostingType)).value(SysQuery::value( _ALM_InventFormulaDynParameter.InventTransPostingType));

        if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType != ALM_InventFromulaInventTransType::All)

        {

            if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType == ALM_InventFromulaInventTransType::InventTransaction)

               qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ReferenceCategory)).value(SysQuery::value( InventTransType::InventTransaction));

            if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType == ALM_InventFromulaInventTransType::ProdLine)

                qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ReferenceCategory)).value(SysQuery::value( InventTransType::ProdLine));

            if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType == ALM_InventFromulaInventTransType::Purch)

                qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ReferenceCategory)).value(SysQuery::value( InventTransType::Purch));

            if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType == ALM_InventFromulaInventTransType::Sales)

                qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ReferenceCategory)).value(SysQuery::value( InventTransType::Sales));

        

        }

        if(_ALM_InventFormulaDynParameter.ALM_InventFormulaDynInOutStatus != ALM_InventFormulaDynInOutStatus::Both)

        {

            if  (_ALM_InventFormulaDynParameter.ALM_InventFormulaDynInOutStatus == ALM_InventFormulaDynInOutStatus::Purchased)

                qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, StatusReceipt)).value(SysQuery::value( StatusReceipt::Purchased));

            else if(_ALM_InventFormulaDynParameter.ALM_InventFormulaDynInOutStatus == ALM_InventFormulaDynInOutStatus::Sold)

                qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, StatusIssue)).value(SysQuery::value( StatusIssue::Sold));

        }

        qbds.addGroupByField(fieldNum(ALM_InventFormulaViewPhysical, ItemId));

      

     

        qbds.fields().clearFieldList();

        fldList_InventFormulaStagging = qbds.fields();

        

     

        fldList_InventFormulaStagging = qbds.fields();

        fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, ItemId));

        //fldList_InventFormulaStagging.addField(fieldNum(Alm_InventFormulaView, Account));

        fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, CostAmountPosted), SelectionField::Sum);

        fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, CostAmountAdjustment), SelectionField::Sum);

        fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, defaultvalue), SelectionField::Sum);

        fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, Qty), SelectionField::Sum);

        fldList_InventFormulaStagging.dynamic(QueryFieldListDynamic::No);

        fieldMapping    = new Map(Types::String, Types::Container);

        fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, ItemId), [qbds.uniqueid(), fieldstr(ALM_InventFormulaViewPhysical, ItemId)]);

        fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, CostAmount), [qbds.uniqueid(), SysComputedColumn::sum(fieldstr(ALM_InventFormulaViewPhysical, CostAmountPosted))]);

        fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, CostAmountAdj), [qbds.uniqueid(), SysComputedColumn::sum(fieldstr(ALM_InventFormulaViewPhysical, CostAmountAdjustment))]);

        fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, Sequence), [qbds.uniqueid(), SysComputedColumn::sum(fieldstr(ALM_InventFormulaViewPhysical, defaultvalue))]);

        fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, Qty), [qbds.uniqueid(), SysComputedColumn::sum(fieldstr(ALM_InventFormulaViewPhysical, Qty))]);

        //     fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, Account), [qbds.uniqueid(), "'dfsdfdsf' as Account"]);

        query::insert_recordset(inventFormulaStagging, fieldMapping, query);

        update_recordset inventFormulaStagging

            setting Account = _ALM_InventFormulaDynParameter.Account,

            Sequence =  _ALM_InventFormulaDynParameter.Sequence,

            Description = _ALM_InventFormulaDynParameter.Description,

            CostAmount = inventFormulaStagging.CostAmount + inventFormulaStagging.CostAmountAdj,

            ALM_InventFormulaDynInOut = _ALM_InventFormulaDynParameter.ALM_InventFormulaDynInOut

            where inventFormulaStagging.Sequence <= -1 && inventFormulaStagging.CreatedBy == curUserId();

    }