Показать сообщение отдельно
Старый 01.06.2016, 14:50   #1  
syl is offline
syl
Участник
Аватар для syl
 
236 / 21 (1) +++
Регистрация: 10.06.2015
Адрес: Киев
Проверка структуры счета на использование аналитики
Доброго времени суток!

AX 2012

Необходимо проверить применима ли аналитика для конкретного счета, который в структуре счетов задан как ХХХ*.

На данный момент реализован класс который выгружает отчет плана счетов с перечнем допустимых аналитик (включая счета которые заданы в границах ХХХ-ХХХ), однако счета ХХХ* он не определяет. Помогите разобраться...

Код существующего класса:
X++:
class AccountDimension
{
}

public static void main(Args arg)
{
    DimensionAttribute dimensionAttribute;
    MainAccount acc;

    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    SysExcelCell cell;
    SysExcelCell cell1;
    SysExcelFont font;

    str Dimension;
    RecId Hierarchy;

    int row;
    int cel;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    workbook = workbooks.add();
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');


    startLengthyOperation();
    // Setting Header values
    cel = 2;
    while select dimensionAttribute
        where !(dimensionAttribute.Name like '*SystemGen*')
    {
        cel++;
        cell = cells.item(1, cel);
        cell.value(dimensionAttribute.name);
        font = cell.font();
        font.bold(true);
    }

    cel = 2;
    row = 1;
    while select acc order by MainAccountId
    {
        row++;
        cell = cells.item(row, 1);
        cell.value(acc.MainAccountId);
        font = cell.font();
        font.bold(true);
        cell = cells.item(row, 2);
        cell.value(acc.Name);
        font = cell.font();
        font.bold(true);

        Hierarchy = AccountDimension::getHierarchy(acc.MainAccountId);

        while select dimensionAttribute
            where !(dimensionAttribute.Name like '*SystemGen*')
        {
            cel++;
            cell1 = cells.item(row, cel);

            Dimension = dimensionAttribute.Name;

            if(AccountDimension::CheckChart(Hierarchy, Dimension))
                cell1.value('+');
        }
        cel = 2;
    }
    endLengthyOperation();
    application.visible(true);
}

private static server RecId getHierarchy(str _account)
{
    Query q;
    QueryRun queryRun;
    QueryBuildDataSource qbDCN;
    QueryBuildDataSource qbDHL;
    QueryBuildDataSource qbDCC;
    QueryBuildDataSource qbDH;
    QueryBuildDataSource qbLS;
    QueryBuildDataSource qbL;

    DimensionHierarchy dh;

    q = new Query();

    qbDCN = q.addDataSource(tablenum(DimensionConstraintNode));
    qbDCN.relations(false);

    qbDHL = qbDCN.addDataSource(tablenum(DimensionHierarchyLevel));
    qbDHL.relations(false);
    qbDHL.addLink(fieldNum(DimensionConstraintNode, DimensionHierarchyLevel),fieldNum(DimensionHierarchyLevel, RecId));
    qbDHL.joinMode(JoinMode::InnerJoin);
    qbDHL.fetchMode(QueryFetchMode::One2One);

    qbDH = qbDHL.addDataSource(tablenum(DimensionHierarchy));
    qbDH.relations(false);
    qbDH.addLink(fieldNum(DimensionHierarchyLevel, DimensionHierarchy),fieldNum(DimensionHierarchy, RecId));
    qbDH.joinMode(JoinMode::InnerJoin);
    qbDH.fetchMode(QueryFetchMode::One2One);

    qbLS = qbDH.addDataSource(tablenum(LedgerStructure));
    qbLS.relations(false);
    qbLS.addLink(fieldNum(DimensionHierarchy,RecId),fieldNum(LedgerStructure,DimensionHierarchy));
    qbLS.joinMode(JoinMode::InnerJoin);
    qbLS.fetchMode(QueryFetchMode::One2One);

    qbL = qbLS.addDataSource(tablenum(Ledger));
    qbL.relations(false);
    qbL.addLink(fieldNum(LedgerStructure,Ledger),fieldNum(Ledger,RecId));
    qbL.joinMode(JoinMode::InnerJoin);
    qbL.fetchMode(QueryFetchMode::One2One);

    qbDCC = qbDCN.addDataSource(tablenum(DimensionConstraintNodeCriteria));
    qbDCC.relations(false);
    qbDCC.addLink(fieldNum(DimensionConstraintNode,RecId),fieldNum(DimensionConstraintNodeCriteria,DimensionConstraintNode));
    qbDCC.joinMode(JoinMode::InnerJoin);
    qbDCC.fetchMode(QueryFetchMode::One2One);
    
    qbDCC.addRange(fieldnum(DimensionConstraintNodeCriteria, RangeFrom)).value('<'+SysQuery::value(_account));
    qbDCC.addRange(fieldnum(DimensionConstraintNodeCriteria, RangeFrom)).value(SysQuery::value(_account));
    qbDCC.addRange(fieldnum(DimensionConstraintNodeCriteria, RangeTo)).value('>'+SysQuery::value(_account));
    qbDCC.addRange(fieldnum(DimensionConstraintNodeCriteria, RangeTo)).value(SysQuery::value(_account));

    qbL.addRange(fieldnum(Ledger, Name)).value(SysQuery::value(curext()));
    queryRun = new QueryRun(q);

    if(queryRun.next())
    {
        dh = queryRun.get(tableNum(DimensionHierarchy));
        return dh.RecId;
    }
    else
        return 0;
}

private static server boolean CheckChart(RecId _account, str _dimension)
{
    Query q;
    QueryRun queryRun;
    QueryBuildDataSource qbDHL;
    QueryBuildDataSource qbDA;
    QueryBuildDataSource qDR;

    q = new Query();

    qDR = q.addDataSource(tablenum(DimensionRule));
    qDR.relations(false);

    qbDHL = qDR.addDataSource(tablenum(DimensionHierarchyLevel));
    qbDHL.relations(false);
    qbDHL.addLink(fieldNum(DimensionRule, AccountStructure),fieldNum(DimensionHierarchyLevel, DimensionHierarchy));
    qbDHL.joinMode(JoinMode::InnerJoin);
    qbDHL.fetchMode(QueryFetchMode::One2One);

    qbDA = qbDHL.addDataSource(tablenum(DimensionAttribute));
    qbDA.relations(false);
    qbDA.addLink(fieldNum(DimensionHierarchyLevel, DimensionAttribute),fieldnum(DimensionAttribute, RecId));
    qbDA.joinMode(JoinMode::InnerJoin);
    qbDA.fetchMode(QueryFetchMode::One2One);

    qDR.addRange(fieldnum(DimensionRule, AccountStructure)).value(SysQuery::value(_account));
    qbDA.addRange(fieldnum(DimensionAttribute, Name)).value(SysQuery::value(_dimension));
    queryRun = new QueryRun(q);

    if(queryRun.next())
    {
        return true;
    }
    else
        return AccountDimension::CheckRules(_account, _dimension);
}

private static server boolean CheckRules(RecId _account, str _dimension)
{
    Query q;
    QueryRun queryRun;
    QueryBuildDataSource qbDHL;
    QueryBuildDataSource qbDRAH;
    QueryBuildDataSource qbDA;
    QueryBuildDataSource qDR;

    q = new Query();

    qDR = q.addDataSource(tablenum(DimensionRule));
    qDR.relations(false);

    qbDRAH = qDR.addDataSource(tablenum(DimensionRuleAppliedHierarchy));
    qbDRAH.relations(false);
    qbDRAH.addLink(fieldNum(DimensionRule, RecId),fieldNum(DimensionRuleAppliedHierarchy, DimensionRule));
    qbDRAH.joinMode(JoinMode::InnerJoin);
    qbDRAH.fetchMode(QueryFetchMode::One2One);

    qbDHL = qbDRAH.addDataSource(tablenum(DimensionHierarchyLevel));
    qbDHL.relations(false);
    qbDHL.addLink(fieldNum(DimensionRuleAppliedHierarchy, DimensionHierarchy),fieldNum(DimensionHierarchyLevel, DimensionHierarchy));
    qbDHL.joinMode(JoinMode::InnerJoin);
    qbDHL.fetchMode(QueryFetchMode::One2One);

    qbDA = qbDHL.addDataSource(tablenum(DimensionAttribute));
    qbDA.relations(false);
    qbDA.addLink(fieldNum(DimensionHierarchyLevel, DimensionAttribute),fieldnum(DimensionAttribute, RecId));
    qbDA.joinMode(JoinMode::InnerJoin);
    qbDA.fetchMode(QueryFetchMode::One2One);

    qDR.addRange(fieldnum(DimensionRule, AccountStructure)).value(SysQuery::value(_account));
    qbDA.addRange(fieldnum(DimensionAttribute, Name)).value(SysQuery::value(_dimension));
    queryRun = new QueryRun(q);

    if(queryRun.next())
    {
        return true;
    }
    else
        return false;
}

Последний раз редактировалось syl; 01.06.2016 в 14:52.