wtorek, 12 marca 2013

Mój stary Job jeszcze z czasów AX3.0.
Umożliwia on wyszukanie w bazie danych w jakich tabelach i polach znajduje się konkretna wartość.
Przykładowo chcemy wyszukać w jakich tabelach jest pole o typie Voucher (niezależnie od jego nazwy, uwzględniając typy podrzędne) o wartości 'VOU0012345'.
  
static void SBR_FindValue_v1_1(Args _args)
{
    str                     func;
    Dictionary              dict;
    DictTable               dictTable;
    DictField               dictField;
    int                     tableCnt;
    int                     fieldCnt;
    Dialog                  dialog = new dialog();
    Dialogfield             dfLookFor;
    Dialogfield             dfLookForType;
    Name                    lookFor;
    int                     lookForTypeId;
    Set                     setTypes;
    ExtendedDataTypeName    lookForType;
    xRefTypeHierarchy       xRefTypeHierarchyBase;
    Query                   query;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    QueryRun                qr;

    Set fillList(Set _set, xRefTypeHierarchy _xRefTypeHierarchy, boolean _forward = true)
    {
        xRefTypeHierarchy   __xRefTypeHierarchy;
        int                 __id;

        if (_forward)
        {
            __id = _xRefTypeHierarchy.Id;
            select __xRefTypeHierarchy
                where __xRefTypeHierarchy.BaseType == _xRefTypeHierarchy.BaseType
                   && __xRefTypeHierarchy.Parent   == __id;
        } else {
            __id = _xRefTypeHierarchy.Parent;
            select __xRefTypeHierarchy
                where __xRefTypeHierarchy.BaseType == _xRefTypeHierarchy.BaseType
                   && __xRefTypeHierarchy.Id       ==  __id;
        }

        while (__xRefTypeHierarchy)
        {
            _set.add(__xRefTypeHierarchy.Id);
            fillList(_set, __xRefTypeHierarchy, _forward);
            next __xRefTypeHierarchy;
        }
        return _set;
    }
    ;

    func         = "int getExtendedTypeNum() {\n";
    func = func  + "  ;\n";
    func = func  + "  return extendedTypeNum(\'%1\');\n";
    func = func  + "}\n";

    dict = new Dictionary();

    dialog.caption('Looking for...');
    dfLookForType   = dialog.addField(typeid(ExtendedDataTypeName), 'Type');
    dfLookFor       = dialog.addField(typeid(Name), 'Value');

    if (!dialog.run())
        return;

    lookForType     = dfLookForType.value();
    lookFor         = dfLookFor.value();
    lookForTypeId   = runBuf(StrFmt(func, lookForType));
    setTypes        = new Set(Types::Integer);

    if (lookFor == '' || lookForType == '')
        throw error('Input parameters cannot be empty!');

    setPrefix(strFmt('Searching value \'%1\' found in:', lookFor));

    select xRefTypeHierarchyBase
        where xRefTypeHierarchyBase.Name == lookForType
           && xRefTypeHierarchyBase.Id   == lookForTypeId;

    if (xRefTypeHierarchyBase)
    {
        setTypes = fillList(setTypes, xRefTypeHierarchyBase);
        setTypes = fillList(setTypes, xRefTypeHierarchyBase, false);
    }

    setTypes.add(lookForTypeId);

    StartLengthyOperation();

    for(tableCnt = 1; tableCnt <= dict.tableCnt(); tableCnt++)
    {
        dictTable = new DictTable( dict.tableCnt2Id(tableCnt) );
        if (dictTable.isTmp() ||  dictTable.isView() || dictTable.isMap())
            continue;

        for(fieldCnt = 1; fieldCnt <= dictTable.fieldCnt(); fieldCnt++)
        {
            dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(fieldCnt));

            if (dictField.typeId() > 0 && setTypes.in(dictField.typeId()))
            {
                infolog.messageWin().addLine(strFmt('searching %1 in %2(%3)',  dictField.baseType(), dictTable.name(), dictField.name()));
                query = new Query();
                qbds  = query.addDataSource(dictTable.id());
                qbds.firstOnly(true);
                qbr = qbds.addRange(dictField.id());
                qbr.value(queryValue(lookFor));
                qr = new QueryRun(query);
                if (qr.next())
                {
                    infolog.messageWin().addLine('FOUND!');
                    info(strFmt('Table = %1, Field = %2', dictTable.name(), dictField.name()));
                }
            }
        }
    }
    EndLengthyOperation();
}