static void AIF_XMLExample(Args _args) { XML xml; CustInvoiceJour custInvoiceJour; Map keyData; AifEntityKey aifEntityKey = AifEntityKey::construct(); AifEntityKeyList aifEntityKeyList; SalesSalesInvoiceService service = SalesSalesInvoiceService::construct(); SalesSalesInvoice salesInvoice; SalesSalesInvoice salesInvoice2; SalesSalesInvoice_CustInvoiceJour custInvoiceJourClass; AfStronglyTypedDataContainerList strongContainerList; AfStronglyTypedDataContainer strongContainer; ; // example invoice select custInvoiceJour where custInvoiceJour.InvoiceId == 'CIV-000020'; keyData = SysDictTable::getKeyData(custInvoiceJour); aifEntityKey.parmTableId( custInvoiceJour.TableId ); aifEntityKey.parmRecId( custInvoiceJour.RecId ); aifEntityKey.parmKeyDataMap( keyData ); aifEntityKeyList = aifEntityKeyList::construct(); aifEntityKeyList.addEntityKey(aifEntityKey); // save to XML salesInvoice = service.read(aifEntityKeyList); xml = salesInvoice.serialize(); info('Sales invoice in XML :'); info( xml ); // read from XML salesInvoice2 = new SalesSalesInvoice(); salesInvoice2.deserialize(xml); strongContainerList = salesInvoice2.parmCustInvoiceJour(); if (strongContainerList.get_Count() > 0) { custInvoiceJourClass = strongContainerList.get_Item(1); info(strFmt("From XML - Invoice Id = %1", custInvoiceJourClass.parmInvoiceId())); info(strFmt("From XML - Sales Id = %1", custInvoiceJourClass.parmSalesId())); } }
Sebastian Bratko - BIZECCO
Moje notatki odnośnie SQL Server oraz Dynamics AX.
wtorek, 20 stycznia 2015
How to export sales invoice to XML using AIF classes
Example how to export sales invoice to XML and read from XML to AX.
piątek, 27 września 2013
Tworzenie bazy BaseLine dla AX2012R2
Plecenie do tworzenia bazy danych BaseLine dla Dynamics AX 2012 :
Initialize-AXModelStore -Server OPTSBR2\SQL2012 -Database AXDB_Baseline -Verbose
Initialize-AXModelStore -Server OPTSBR2\SQL2012 -Database AXDB_Baseline -Verbose
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'.
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(); }
Active sessions and locks
-- active requests SELECT r.session_id, se.host_name, se.login_name, Db_name(r.database_id) AS dbname, r.status, r.command, r.cpu_time, r.total_elapsed_time, r.reads, r.logical_reads, r.writes, s.text sql_text, p.query_plan query_plan, SQL_CURSORSQL.text, SQL_CURSORPLAN.query_plan FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions se ON r.session_id = se.session_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN WHERE r.session_id <> @@SPID AND se.is_user_process = 1
-- active locks SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS database_name, O.Name AS locked_object_name, P.object_id AS locked_objectId, L.resource_type AS locked_resource, L.request_mode AS lock_type, ST.text AS sql_statement_text, ES.login_name AS login_name, ES.host_name AS [host_name], TST.is_user_transaction as is_user_transaction, AT.name as transaction_name, CN.auth_scheme as authentication_method FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id() ORDER BY L.request_session_id
Etykiety:
SKRYPTY SQL,
SQL
poniedziałek, 11 marca 2013
Unused/Missing indexes
-- unused indexes SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName, OBJECT_NAME(I.OBJECT_ID) AS ObjectName, I.NAME AS IndexName FROM sys.indexes I WHERE -- only get indexes for user created tables OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 -- find all indexes that exists but are NOT used AND NOT EXISTS ( SELECT index_id FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = I.OBJECT_ID AND I.index_id = index_id -- limit our query only for the current db AND database_id = DB_ID()) AND I.index_id != 0 AND I.is_primary_key = 0 AND I.is_unique_constraint = 0 -- AND I.is_unique = 0 ORDER BY SchemaName, ObjectName, IndexName
-- rarely used indexes SELECT O.name AS object_name, I.name AS index_name, I.index_id AS index_id, DM_IUS.user_seeks AS user_seeks, DM_IUS.user_scans AS user_scans, DM_IUS.user_lookups AS user_lookups, DM_IUS.user_updates AS user_updates, P.table_rows as table_rows, DM_IUS.last_user_seek, DM_IUS.last_user_scan, DM_IUS.last_user_lookup, DM_IUS.last_user_update FROM sys.dm_db_index_usage_stats DM_IUS INNER JOIN sys.indexes I ON I.index_id = DM_IUS.index_id AND DM_IUS.OBJECT_ID = I.OBJECT_ID INNER JOIN sys.objects O ON DM_IUS.OBJECT_ID = O.OBJECT_ID INNER JOIN sys.schemas S ON O.schema_id = S.schema_id INNER JOIN (SELECT SUM(P.rows) table_rows, P.index_id, P.OBJECT_ID FROM sys.partitions P GROUP BY P.index_id, P.OBJECT_ID) P ON P.index_id = DM_IUS.index_id AND DM_IUS.OBJECT_ID = P.OBJECT_ID WHERE OBJECTPROPERTY(DM_IUS.OBJECT_ID,'IsUserTable') = 1 AND DM_IUS.database_id = DB_ID() AND I.type_desc = 'nonclustered' AND I.is_primary_key = 0 AND I.is_unique_constraint = 0 ORDER BY (DM_IUS.user_seeks + DM_IUS.user_scans + DM_IUS.user_lookups) ASC
-- used indexes stats SELECT DB_NAME(C.database_id) AS database_name, OBJECT_NAME(C.object_id, C.database_id) AS table_name, A.avg_user_impact * (A.user_seeks + A.user_scans) avg_estimated_impact, A.avg_total_user_cost * (A.user_seeks + A.user_scans) avg_estimated_total_user_cost, A.unique_compiles, A.user_seeks, A.last_user_seek, DATEPART(d, GETDATE() - A.last_user_seek) as last_user_seek_days_ago, A.user_scans, A.last_user_scan, DATEPART(d, GETDATE() - A.last_user_scan) as last_user_scan_days_ago, A.avg_total_user_cost, A.avg_user_impact, C.equality_columns, C.inequality_columns, C.included_columns FROM sys.dm_db_missing_index_group_stats AS A INNER JOIN sys.dm_db_missing_index_groups AS B ON A.group_handle = B.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS C ON B.index_handle = C.index_handle --WHERE --DATEPART(hh, last_user_seek) >= 6 AND DATEPART(hh, last_user_seek) <= 20 AND -- run in work hours --DATEPART(d, GETDATE() - A.last_user_seek) = 1 -- run today ORDER BY avg_estimated_total_user_cost DESC
Etykiety:
SKRYPTY SQL,
SQL
wtorek, 5 marca 2013
Create ledger journal from code (using LedgerJournalEngine)
static void SBR_Create_Ledger_Journal(Args _args) { #define.JournalName('KOST12') LedgerJournalName ledgerJournalName; LedgerJournalTable ledgerJournalTable; LedgerJournalTrans ledgerJournalTrans; LedgerJournalId journalId; Amount amount; LedgerJournalEngine engine; ; ttsBegin; ledgerJournalName = LedgerJournalName::find(#JournalName); ledgerJournalTable.clear(); ledgerJournalTable.JournalName = ledgerJournalName.JournalName; ledgerJournalTable.initFromLedgerJournalName(); ledgerJournalTable.insert(); journalId = ledgerJournalTable.JournalNum; // Create Trans engine = LedgerJournalEngine::construct(ledgerJournalName.JournalType); engine.newJournalActive(ledgerJournalTable); ledgerJournalTrans.clear(); ledgerJournalTrans.initValue(); ledgerJournalTrans.JournalNum = journalId; engine.initValue(ledgerJournalTrans); // account ledgerJournalTrans.AccountType = LedgerJournalACType::Cust; engine.accountNumModified(ledgerJournalTrans); ledgerJournalTrans.AccountNum = '10058'; engine.accountModified(ledgerJournalTrans); // offset account ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger; engine.offsetAccountTypeModified(ledgerJournalTrans); ledgerJournalTrans.OffsetAccount = '22500'; engine.offsetAccountModified(ledgerJournalTrans); // dimensions ledgerJournalTrans.Dimension[1] = ''; ledgerJournalTrans.Dimension[2] = ''; ledgerJournalTrans.Dimension[3] = ''; engine.initDimension(ledgerJournalTrans); // amount amount = 100; ledgerJournalTrans.AmountCurCredit = (amount > 0 ? abs(amount) : 0); if (ledgerJournalTrans.AmountCurCredit) engine.amountCurCreditModified(ledgerJournalTrans); ledgerJournalTrans.AmountCurDebit = (amount < 0 ? abs(amount) : 0); if (ledgerJournalTrans.AmountCurDebit) engine.amountCurDebitModified(ledgerJournalTrans); ledgerJournalTrans.insert(); pause; ttsCommit; info(strFmt('created journal %1', journalId)); }
czwartek, 27 września 2012
Odczyt danych z pliku Excel
Oto moja autorska metoda odczytu danych z pliku excel, umożliwia ona :
Potrzebna jest biblioteka EPPlus która w swojej standardowej postaci znajduje się pod adresem http://epplus.codeplex.com. Aby użyć tej biblioteki w połączeniu z Dynamics AX 2009 wymagała ona pewnych zmian (standardowa biblioteka używa pewnych składni języka C# z którą DAX sobie nie radzi).
Poprawiona przeze mnie wersja biblioteki znajduje się pod adresem AX_EPPlus.zip (kod źródłowy zostanie opublikowany niedługo).
Przykład użycia :
Bibliotekę dodajemy do referencji w Dynamics AX 2009 (po stronie klienta).
- odczyt z pliku xlsx bez konieczności posiadania zainstalowanego porgramu Microsoft Excel
- jest nieporównywalnie szybsza i wygodniejsza od innych znanych mi metod (a prawdopodobnie znam je wszystkie)
- obsługuje duże pliki xlsx (mechanizmy oparte na połączeniach COM mają z tym problem)
Potrzebna jest biblioteka EPPlus która w swojej standardowej postaci znajduje się pod adresem http://epplus.codeplex.com. Aby użyć tej biblioteki w połączeniu z Dynamics AX 2009 wymagała ona pewnych zmian (standardowa biblioteka używa pewnych składni języka C# z którą DAX sobie nie radzi).
Poprawiona przeze mnie wersja biblioteki znajduje się pod adresem AX_EPPlus.zip (kod źródłowy zostanie opublikowany niedługo).
Przykład użycia :
Bibliotekę dodajemy do referencji w Dynamics AX 2009 (po stronie klienta).
static void SBR_ReadFromExcelFile(Args _args) { OfficeOpenXml.ExcelPackage package; OfficeOpenXml.ExcelWorkbook workBook; OfficeOpenXml.ExcelWorksheets workSheets; OfficeOpenXml.ExcelWorksheet workSheet; System.IO.FileInfo file; CLRObject clrException; int i; CustAccount custAccount; Amount amount; ; try { file = new System.IO.FileInfo("c:\\temp\\a.xlsx"); package = new OfficeOpenXml.ExcelPackage(file); workBook = package.get_Workbook(); workSheets = workBook.get_Worksheets(); // read from first sheet workSheet = workSheets.get_Item(1); i = 1; while(!CLRInterop::isNull(worksheet.AxGetValue(i,1))) { custAccount = worksheet.AxGetValue(i,1); // if cell is empty NULL value will be returned if (!CLRInterop::isNull(worksheet.AxGetValue(i,2))) { // read value from excel file amount = worksheet.AxGetValue(i,2); } else { // set default value for empty cell amount = 0; } print custAccount, ' ', amount; i++; } Box::info(strFmt('Done. %1 rows readed.'); } catch( Exception::CLRError ) { clrException = CLRInterop::getLastException(); if( clrException ) { info( CLRInterop::getAnyTypeForObject( clrException.get_Message() ) ); } } catch( Exception::Internal ) { clrException = CLRInterop::getLastException(); if( clrException ) { info( CLRInterop::getAnyTypeForObject( clrException.get_Message() ) ); } } }
Subskrybuj:
Posty (Atom)