D365: 过滤DefaultDimension和LedgerDimension中的财务维度

D365通过如下方法来过滤DefaultDimension和LedgerDimension中的财务维度

SysQuery::addDimensionAttributeRange(query,
                            　　　　　　datasourcename,
                            　　　　　　dimensionFieldName,
                            　　　　　　DimensionComponent::DimensionAttribute,
                            　　　　　　dimensionValue,
                            　　　　　　dimensionName);

　　

如下例子是中，实现了如何通过Main account, Project, Project group作为财务维度来过滤凭证，项目支出和费用的交易，供参考

public void setProjectProfitAndLossTmp(FromDate _fromDate, EndDate _toDate, str _projIds, str _projGroups, HCN_ProjReportType _reportType)
    {
        ProjTransPosting            projTransPosting;
        ProjCostTrans               projCostTrans;
        ProjRevenueTrans            projRevenueTrans;
        HCN_DimensionTypeTable      dimensionProject, dimensionProjectGroup;
        GeneralJournalAccountEntry  journalAccountEntry;
        GeneralJournalEntry         journalEntry;
        HCN_LegerAcountSetup        ledgerAccountSetup;
        Query                       query;
        QueryRun                    queryRun;
        QueryBuildDataSource        qbdsCost, qbdsRevenue, qbdsPosting, qbdsEntry, qbdsJournalEntry;
        ProjGroupId                 projGroupId;
        ;

        this.deleteProjectProfitAndLossTmp();

        dimensionProject        = HCN_DimensionTypeTable::findByType(HCN_DimensionType::Project);
        dimensionProjectGroup   = HCN_DimensionTypeTable::findByType(HCN_DimensionType::ProjectGroup);
        //项目收入
        query           = new Query();
        qbdsPosting     = query.addDataSource(tableNum(ProjTransPosting));
        qbdsRevenue     = qbdsPosting.addDataSource(tableNum(ProjRevenueTrans));
        qbdsRevenue.relations(true);
        SysQuery::findOrCreateRange(qbdsPosting, fieldNum(ProjTransPosting, PostingType)).value(queryValue(LedgerPostingType::ProjTurnover));
        SysQuery::findOrCreateRange(qbdsRevenue, fieldNum(ProjRevenueTrans, TransDate)).value(SysQuery::range(_fromDate, _toDate));
        if (_projIds)
        {
            SysQuery::findOrCreateRange(qbdsRevenue, fieldNum(ProjRevenueTrans, ProjId)).value(_projIds);
        }
        if (_projGroups)
        {
            SysQuery::addDimensionAttributeRange(query,
                            qbdsRevenue.name(),
                            fieldStr(ProjRevenueTrans, DefaultDimension),
                            DimensionComponent::DimensionAttribute,
                            _projGroups,
                            dimensionProjectGroup.DemensionValue);
        }
        
        queryRun        = new QueryRun(query);
        while(queryRun.next())
        {
            projRevenueTrans    = queryRun.get(tableNum(ProjRevenueTrans));
            projTransPosting    = queryRun.get(tableNum(ProjTransPosting));
            if (projRevenueTrans.projInvoiceStatus() != ProjInvoiceStatus::FullyInvoiced)
            {
                continue;
            }
            projectProfitAndLossTmp.clear();
            projectProfitAndLossTmp.ProjId          = projRevenueTrans.ProjId;
            projectProfitAndLossTmp.ProjName        = ProjTable::find(projRevenueTrans.ProjId).Name;
            projectProfitAndLossTmp.ProjTransType   = HCN_ProjTransType::Revenue;
            projectProfitAndLossTmp.CurrencyCode    = projRevenueTrans.CurrencyId;
            projGroupId                             = HCN_DimensionFunction::getDimensionValue(projTransPosting.DefaultDimension,
                                                                                               dimensionProjectGroup.DemensionValue);
            projectProfitAndLossTmp.ProjGroupId     = projGroupId;
            projectProfitAndLossTmp.MainAccountId   = LedgerDimensionFacade::getMainAccountFromLedgerDimension(projTransPosting.LedgerDimension).MainAccountId;
            projectProfitAndLossTmp.MainAccountName = LedgerDimensionFacade::getMainAccountFromLedgerDimension(projTransPosting.LedgerDimension).Name;
            projectProfitAndLossTmp.TransTxt        = projRevenueTrans.Txt;
            projectProfitAndLossTmp.Voucher         = projTransPosting.Voucher;
            projectProfitAndLossTmp.AmountCur       = -projRevenueTrans.salesPrice();
            projectProfitAndLossTmp.AmountMST       = -projTransPosting.AmountMst;
            projectProfitAndLossTmp.insert();
        }
        //项目成本
        query   = new Query();
        qbdsPosting     = query.addDataSource(tableNum(ProjTransPosting));
        qbdsCost        = qbdsPosting.addDataSource(tableNum(ProjCostTrans));
        qbdsCost.relations(true);
        SysQuery::findOrCreateRange(qbdsPosting, fieldNum(ProjTransPosting, PostingType)).value(queryValue(LedgerPostingType::ProjCost));
        SysQuery::findOrCreateRange(qbdsCost, fieldNum(ProjCostTrans, TransDate)).value(SysQuery::range(_fromDate, _toDate));
        if (_projIds)
        {
            SysQuery::findOrCreateRange(qbdsCost, fieldNum(ProjCostTrans, ProjId)).value(_projIds);
        }
        if (_projGroups)
        {
            SysQuery::addDimensionAttributeRange(query,
                            qbdsCost.name(),
                            fieldStr(ProjCostTrans, DefaultDimension),
                            DimensionComponent::DimensionAttribute,
                            _projGroups,
                            dimensionProjectGroup.DemensionValue);
        }
        
        queryRun        = new QueryRun(query);
        while(queryRun.next())
        {
            projCostTrans    = queryRun.get(tableNum(ProjCostTrans));
            projTransPosting = queryRun.get(tableNum(ProjTransPosting));
            if (projCostTrans.projInvoiceStatus() != ProjInvoiceStatus::FullyInvoiced)
            {
                continue;
            }
            projectProfitAndLossTmp.clear();
            projectProfitAndLossTmp.ProjId          = projCostTrans.ProjId;
            projectProfitAndLossTmp.ProjName        = ProjTable::find(projCostTrans.ProjId).Name;
            projectProfitAndLossTmp.ProjTransType   = HCN_ProjTransType::Cost;
            projectProfitAndLossTmp.CurrencyCode    = projCostTrans.CurrencyIdCost;
            projGroupId                             = HCN_DimensionFunction::getDimensionValue(projTransPosting.DefaultDimension,
                                                                                               dimensionProjectGroup.DemensionValue);
            projectProfitAndLossTmp.ProjGroupId     = projGroupId;
            projectProfitAndLossTmp.MainAccountId   = LedgerDimensionFacade::getMainAccountFromLedgerDimension(projTransPosting.LedgerDimension).MainAccountId;
            projectProfitAndLossTmp.MainAccountName = LedgerDimensionFacade::getMainAccountFromLedgerDimension(projTransPosting.LedgerDimension).Name;
            projectProfitAndLossTmp.TransTxt        = projCostTrans.Txt;
            projectProfitAndLossTmp.Voucher         = projTransPosting.Voucher;
            projectProfitAndLossTmp.AmountCur       = projCostTrans.costPriceCurrency();
            projectProfitAndLossTmp.AmountMST       = projCostTrans.costAmountLedger();
            projectProfitAndLossTmp.insert();
        }

        //手工账

        while select ledgerAccountSetup
        {
            query               = new Query(queryStr(HCN_LedgerTransVoucherQuery));
            qbdsEntry           = query.dataSourceTable(tableNum(GeneralJournalEntry));
            qbdsJournalEntry    = query.dataSourceTable(tableNum(GeneralJournalAccountEntry));

            SysQuery::findOrCreateRange(qbdsEntry, fieldNum(GeneralJournalEntry, AccountingDate)).value(SysQuery::range(_fromDate, _toDate));
            SysQuery::findOrCreateRange(qbdsEntry, fieldNum(GeneralJournalEntry, SubledgerVoucherDataAreaId)).value(curExt());
            SysQuery::findOrCreateRange(qbdsJournalEntry, fieldNum(GeneralJournalAccountEntry, PostingType)).value(queryValue(LedgerPostingType::LedgerJournal));
            
            if (_projIds)
            {
                SysQuery::addDimensionAttributeRange(query,
                            qbdsJournalEntry.name(),
                            fieldStr(GeneralJournalAccountEntry, LedgerDimension),
                            DimensionComponent::DimensionAttribute,
                            _projIds,
                            dimensionProject.DemensionValue);
            }
            else
            {
                SysQuery::addDimensionAttributeRange(query,
                            qbdsJournalEntry.name(),
                            fieldStr(GeneralJournalAccountEntry, LedgerDimension),
                            DimensionComponent::DimensionAttribute,
                            SysQuery::valueNotEmptyString(),
                            dimensionProject.DemensionValue);
            }
            if (_projGroups)
            {
                SysQuery::addDimensionAttributeRange(query,
                            qbdsJournalEntry.name(),
                            fieldStr(GeneralJournalAccountEntry, LedgerDimension),
                            DimensionComponent::DimensionAttribute,
                            _projGroups,
                            dimensionProjectGroup.DemensionValue);
            }
            else
            {
                SysQuery::addDimensionAttributeRange(query,
                            qbdsJournalEntry.name(),
                            fieldStr(GeneralJournalAccountEntry, LedgerDimension),
                            DimensionComponent::DimensionAttribute,
                            SysQuery::valueNotEmptyString(),
                            dimensionProjectGroup.DemensionValue);
            }
            if (ledgerAccountSetup.AccountRange)
            {
                SysQuery::addDimensionAttributeRange(query,
                            qbdsJournalEntry.name(),
                            fieldStr(GeneralJournalAccountEntry, LedgerDimension),
                            DimensionComponent::DimensionAttribute,
                            ledgerAccountSetup.AccountRange,
                            'MainAccount');
            }
            
            queryRun        = new QueryRun(query);
            while(queryRun.next())
            {
                journalAccountEntry = queryRun.get(tableNum(GeneralJournalAccountEntry));
                journalEntry        = queryRun.get(tableNum(GeneralJournalEntry));
                projectProfitAndLossTmp.clear();
                projectProfitAndLossTmp.ProjId          = HCN_DimensionFunction::getLedgerDimensionCode(journalAccountEntry.LedgerDimension,
                                                                                                        dimensionProject.DemensionValue);
                projectProfitAndLossTmp.ProjName        = ProjTable::find(projectProfitAndLossTmp.ProjId).Name;
                projectProfitAndLossTmp.ProjTransType   = HCN_ProjTransType::Cost;
                projectProfitAndLossTmp.CurrencyCode    = journalAccountEntry.TransactionCurrencyCode;
                projGroupId                             = HCN_DimensionFunction::getLedgerDimensionCode(journalAccountEntry.LedgerDimension,
                                                                                                        dimensionProjectGroup.DemensionValue);
                projectProfitAndLossTmp.ProjGroupId     = projGroupId;
                projectProfitAndLossTmp.MainAccountId   = LedgerDimensionFacade::getMainAccountFromLedgerDimension(journalAccountEntry.LedgerDimension).MainAccountId;
                projectProfitAndLossTmp.MainAccountName = LedgerDimensionFacade::getMainAccountFromLedgerDimension(journalAccountEntry.LedgerDimension).Name;
                projectProfitAndLossTmp.TransTxt        = journalAccountEntry.Text;
                projectProfitAndLossTmp.TransDate       = journalEntry.AccountingDate;
                projectProfitAndLossTmp.Voucher         = journalEntry.SubledgerVoucher;
                projectProfitAndLossTmp.AmountCur       = journalAccountEntry.TransactionCurrencyAmount;
                projectProfitAndLossTmp.AmountMST       = journalAccountEntry.AccountingCurrencyAmount;
                projectProfitAndLossTmp.insert();
            }
        }

        if (_reportType == HCN_ProjReportType::All)
        {
            this.calcTotal();
        }
        else if (_reportType == HCN_ProjReportType::Total)
        {
            this.calcTotal(true);
        }
    }