Following job that illustrates how we can use regular expressions in axapta for validating only numbers in string.
static void THK_regularExpression(Args _args)
{
TextBuffer txt = new TextBuffer();
str msg = "9877897";
;
txt.setText(msg);
txt.regularExpressions(true); // activate regular expr in search
// Regular expression to validate only digits
if (txt.find("^[0-9]+$"))
{
info("string contains only numbers");
}
else
warning("This string have not all numbers");
}
2012年6月20日星期三
How to creating multiple CSV files from one dataset
Here's a little bit of code that will create multiple CSV files depending on how large the dataset is,
and how many records you want in each CSV. Our company outsources some letters that we need to print and they only process 10,000 letters at a
time, so this was a nice and easy way to split our files up without doing it manually. I'm just going to use SalesTable records as an example.
Optional first step: Create an "Alphabet" enum.(1 = A, 2 = B, 3 = C,...) I use this to tack on an "_A", "_B", "_C", etc. to the end of the filename.
You'll need to add some logic if your csv may potentially loop more than 26 times, though.
static void THK_CSVSplit(Args _args)
{
SalesTable SalesTable;
int recordCount;
int recordsPerFile = 500;
//amAlphabetUppercase alphabet = amAlphabetUppercase::A;
int alphabet = 65; //"A"
CommaIO file;
container line;
Filepath filepath = @"C:\TestCSV\";
String30 filePrefix = "Test";
Filename filename;
#File
;
filePath = WinAPI::browseForPath(infolog.hWnd(),"Please selected Images folder path");//FilePath = @"d:\Pic0608\";
if(!filePath)
return ;
while select SalesTable
where SalesTable.createdDateTime >= DateTimeUtil::addDays(DateTimeUtil::getSystemDateTime(),-20) &&
SalesTable.SalesType == SalesType::Sales
{
if(recordCount MOD recordsPerFile == 0)
{
filename = strfmt("%1%2%3_%4.csv",filepath,"\\",filePrefix,num2char(alphabet));
alphabet +=1;
file = new CommaIo(filename,#io_write);
if(!file || file.status() != IO_Status::Ok)
throw error("File cannot be opened");
line = ["Sales ID","Account number","Order date","Sales status"];
file.writeExp(line);
info(strfmt("CSV successfully saved to %1",filename));
}
line = [ SalesTable.SalesId,
SalesTable.CustAccount,
SalesTable.createdDateTime,
enum2str(SalesTable.SalesStatus)
];
file.writeExp(line);
recordCount++;
}
IF(winAPI::fileExists(filePath,TRUE))
winAPI::shellExecute(filePath);
}
and how many records you want in each CSV. Our company outsources some letters that we need to print and they only process 10,000 letters at a
time, so this was a nice and easy way to split our files up without doing it manually. I'm just going to use SalesTable records as an example.
Optional first step: Create an "Alphabet" enum.(1 = A, 2 = B, 3 = C,...) I use this to tack on an "_A", "_B", "_C", etc. to the end of the filename.
You'll need to add some logic if your csv may potentially loop more than 26 times, though.
static void THK_CSVSplit(Args _args)
{
SalesTable SalesTable;
int recordCount;
int recordsPerFile = 500;
//amAlphabetUppercase alphabet = amAlphabetUppercase::A;
int alphabet = 65; //"A"
CommaIO file;
container line;
Filepath filepath = @"C:\TestCSV\";
String30 filePrefix = "Test";
Filename filename;
#File
;
filePath = WinAPI::browseForPath(infolog.hWnd(),"Please selected Images folder path");//FilePath = @"d:\Pic0608\";
if(!filePath)
return ;
while select SalesTable
where SalesTable.createdDateTime >= DateTimeUtil::addDays(DateTimeUtil::getSystemDateTime(),-20) &&
SalesTable.SalesType == SalesType::Sales
{
if(recordCount MOD recordsPerFile == 0)
{
filename = strfmt("%1%2%3_%4.csv",filepath,"\\",filePrefix,num2char(alphabet));
alphabet +=1;
file = new CommaIo(filename,#io_write);
if(!file || file.status() != IO_Status::Ok)
throw error("File cannot be opened");
line = ["Sales ID","Account number","Order date","Sales status"];
file.writeExp(line);
info(strfmt("CSV successfully saved to %1",filename));
}
line = [ SalesTable.SalesId,
SalesTable.CustAccount,
SalesTable.createdDateTime,
enum2str(SalesTable.SalesStatus)
];
file.writeExp(line);
recordCount++;
}
IF(winAPI::fileExists(filePath,TRUE))
winAPI::shellExecute(filePath);
}
2012年6月14日星期四
How to creating a new Number Sequence for an Existing Module, and Putting it to Use
When I first learned how to create a Number Sequence in my Dynamics AX 2009
Development IV Class (80014) in February of 2010, I left with more
confusion about the topic than it should have, and I dreaded coming back to work
with the prospect of actually having to implement new Number Sequences. However,
once I pulled together the precise steps from various books, online documents,
and flat out trial-and-error, the task turned out to not be quite as bad as I
had originally left the class thinking. I was however dumbfounded as to why no
single source (that I was able to find) outlined the exact steps from creating a
new Number Sequence to putting it to use in one clean tutorial.
NOTE: Please bear with me in this tutorial; I am going to break naming convention best practices, so that my example is clearer.
The first thing you need to do is determine which module you want/need to create the new number sequence for. For this example, I am going create a new number sequence for the “Accounts Receivable” module. Then take the following steps:
Creating a new Number Sequence for the Accounts Receivable Module
1. Create your Extended Data Type that will be used in your table as your Number Sequence field. For this example we will create a string based Extended Data Type called edt_ComplaintId (with a Label property = “Complaint Report”)
2. Next, since we are using the “Accounts Receivable” module, we must modify the proper NumberSeqReference_XXXX Class. Accounts Receivable, actually maps to Customer (or Cust), so we need to make a modification to the NumberSeqReference_Customer class.
We are only concerned with the loadModule() method, and will simply need to add the following code to it:
4. Click on the “Wizard” button, which then should initialize the wizard.
6. On the following screen, verify that the Module is “Accounts receivable” and the Reference is “Complaint Report” (which is the label of our Extended Data Type). The Number Sequence code is just an auto-generated value that you can leave as is (Remember this code, so that you can find it in your list of Number Sequences, because you are going to want to make a couple changes to it). Once you have verified the above, click the Next > button.
7. The next screen just gives you an overview, click the Finish button.
8. You should then see your Number Sequence in the list.
9. You will probably want to change the Format to something such as “CR-######”
10. At this point, your Number Sequence is ready for use!
Using the Number Sequence
1. Create a Table and name it tbl_Complaint.
2. Add the edt_ComplaintId Extended Data Type as one of the fields (drag and drop the Extended Data Type directly into the fields of the Table) and name this field ComplaintId.
3. Add the following Method to the tbl_Complaint:
5. Add the tbl_Complaint table to the form’s Data Sources, and name the Data Source ds_Complaint.
6. Add the following Methods to the Form:
NOTE: Please bear with me in this tutorial; I am going to break naming convention best practices, so that my example is clearer.
The first thing you need to do is determine which module you want/need to create the new number sequence for. For this example, I am going create a new number sequence for the “Accounts Receivable” module. Then take the following steps:
Creating a new Number Sequence for the Accounts Receivable Module
1. Create your Extended Data Type that will be used in your table as your Number Sequence field. For this example we will create a string based Extended Data Type called edt_ComplaintId (with a Label property = “Complaint Report”)
2. Next, since we are using the “Accounts Receivable” module, we must modify the proper NumberSeqReference_XXXX Class. Accounts Receivable, actually maps to Customer (or Cust), so we need to make a modification to the NumberSeqReference_Customer class.
We are only concerned with the loadModule() method, and will simply need to add the following code to it:
numRef.dataTypeId = typeId2ExtendedTypeId(typeid(edt_ComplaintId)); numRef.referenceHelp = "Unique key for the Complaint Report"; numRef.wizardContinuous = true;3. After compiling and saving your changes, the next step is to use the Number Sequence Wizard to set it up. Click Basic > Setup > Number sequences > Number sequences to open the form where you can manage your Number Sequences.
numRef.wizardManual = NoYes::No;
numRef.wizardAllowChangeDown = NoYes::No;
numRef.wizardAllowChangeUp = NoYes::No;
numRef.wizardHighest = 999999;
this.create(numRef);
4. Click on the “Wizard” button, which then should initialize the wizard.
NOTE: If you receive an error at this point telling you “The application already has the required number sequences”, this means that you either did not add the definition to an established NumberSeqReference_XXXX Class' (in this example, the NumberSeqReference_Customer Class) loadModule() method, or you have already run the Wizard and set it up.5. Once you arrive on the Wizard Welcome screen, click the Next > button.
6. On the following screen, verify that the Module is “Accounts receivable” and the Reference is “Complaint Report” (which is the label of our Extended Data Type). The Number Sequence code is just an auto-generated value that you can leave as is (Remember this code, so that you can find it in your list of Number Sequences, because you are going to want to make a couple changes to it). Once you have verified the above, click the Next > button.
7. The next screen just gives you an overview, click the Finish button.
8. You should then see your Number Sequence in the list.
9. You will probably want to change the Format to something such as “CR-######”
10. At this point, your Number Sequence is ready for use!
Using the Number Sequence
1. Create a Table and name it tbl_Complaint.
2. Add the edt_ComplaintId Extended Data Type as one of the fields (drag and drop the Extended Data Type directly into the fields of the Table) and name this field ComplaintId.
3. Add the following Method to the tbl_Complaint:
static client server NumberSequenceReference numRefComplaintId()4. Create a Form called frm_Complaint.
{
return NumberSeqReference::findReference(typeId2ExtendedTypeId(typeid(edt_ComplaintId)));
}
5. Add the tbl_Complaint table to the form’s Data Sources, and name the Data Source ds_Complaint.
6. Add the following Methods to the Form:
public class FormRun extends ObjectRun7. Add the following Methods to the ds_Complaint Data Source:
{
NumberSeqFormHandler numberSeqFormHandler;
}
NumberSeqFormHandler numberSeqFormHandler()
{
if (!numberSeqFormHandler)
{
numberSeqFormHandler = NumberSeqFormHandler::newForm(Tmt_CallReport::numRefComplaintId().NumberSequence, element, ds_Complaint.dataSource(), fieldnum(tbl_Complaint, ComplaintId));
}
return numberSeqFormHandler;
}
public void create(boolean _append = false)8. Your Number Sequence should now function!
{
element.numberSeqFormHandler().formMethodDataSourceCreatePre();
super(_append);
element.numberSeqFormHandler().formMethodDataSourceCreate();
}
public void delete()
{
element.numberSeqFormHandler().formMethodDataSourceDelete();
super();
}
public void write()
{
super();
element.numberSeqFormHandler().formMethodDataSourceWrite();
}
2012年6月8日星期五
如何使用SQL Server读取TXT或者CSV文件
如果你需要在SQL语句中读取外部的TXT或者CSV文件中的内容，那么可以使用以下的语句
SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\','SELECT * FROM file.csv')
SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\','SELECT * FROM file.txt')
如果出现运行错误，则尝试运行下列语句
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
以上代码已经在32位的Win7和Sql Server 2008中测试。
如果你是64位（Bit）系统，那么可能会遇到以下的错误：Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
那么说明你的64位系统中没有相应的驱动“Microsoft Text Driver”，查看路径为：Control Pane => Administrative Tools => Data Sources (ODBC) => Drivers
那么下载微软提供的64位软件：Microsoft Access Database Engine 2010 Redistributable： http://www.microsoft.com/en-us/download/details.aspx?id=13255
然后再将“Microsoft Text Driver”替换为“Microsoft Access Text Driver”即可。
如何解决Win7英文版中文乱码问题
1）可以解决显示中文乱码问题，但是导入仍然是乱码。
我们的网站经常需要导出一些csv文件，其中包含有中文。过去用Excel 2003处理，没有任何问题。但是当升级到Excel 2007之后，打开这些csv文件，里面的中文就会显示为乱码。
百思不得其解。后来终於发现，跟语言编码有关。据说csv不支持Unicode，或者是不支持UTF-8，所以导出的csv文件往往都是默认ANSI编码的。但是这种ANSI编码含中文的文件，notepad记事本可以正常打开，Excel 2007打开就是乱码。
最后发现，问题的关键是“编辑语言”，Start – All Programs – Microsoft Office – Microsoft Office Tools – Microsoft Office 2007 Language Settings ，在其中的 Editing Language标签，选择Primary Editing Language 為 Chinese(PRC).
然后打开这些含有中文的csv文件就不会有乱码了。不要问我為什么，这是实践的结果。不过我猜，也许微软认為经常编辑英文的人不需要面对中文问题？
2）可以解决导入和显示问题。
Win7英文版如果出现中文不能正常显示的话，那么只要把不支持Unicode的选项改为中国就可以了，具体步骤如下。
我们的网站经常需要导出一些csv文件，其中包含有中文。过去用Excel 2003处理，没有任何问题。但是当升级到Excel 2007之后，打开这些csv文件，里面的中文就会显示为乱码。
百思不得其解。后来终於发现，跟语言编码有关。据说csv不支持Unicode，或者是不支持UTF-8，所以导出的csv文件往往都是默认ANSI编码的。但是这种ANSI编码含中文的文件，notepad记事本可以正常打开，Excel 2007打开就是乱码。
最后发现，问题的关键是“编辑语言”，Start – All Programs – Microsoft Office – Microsoft Office Tools – Microsoft Office 2007 Language Settings ，在其中的 Editing Language标签，选择Primary Editing Language 為 Chinese(PRC).
然后打开这些含有中文的csv文件就不会有乱码了。不要问我為什么，这是实践的结果。不过我猜，也许微软认為经常编辑英文的人不需要面对中文问题？
2）可以解决导入和显示问题。
Win7英文版如果出现中文不能正常显示的话，那么只要把不支持Unicode的选项改为中国就可以了，具体步骤如下。
Control Pannel-->Clock, Language, and Region-->Administrative-->Change system locale-->选择 Chinese (PRC) 即可。
Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005
Locking and Blocking Scripts in
SQL Server 2000 vs SQL Server 2005
Written By: Jeremy Kadlec -- 10/25/2007 --
SolutionIn SQL Server 2000, a few different stored procedures and tables were used to find the number and types of locks being issued, which spid (system process identifier) the locks were associated with and which user\application issued the commands. In SQL Server 2005, these same stored procedures are available, but Microsoft has also introduced some new dynamic views\functions which replace the system tables as a means to research the locking issues further. Let's dive into these objects and scripts to take the crosswalk from SQL Server 2000 to 2005.
SQL Server 2000
In SQL Server 2000 the main stored procedure to find out which locks were issued was sp_lock. Typically this command was followed by sp_who2 in order to find out more information about the spid in terms of the user, host name, application, CPU and memory utilization. To drill into the spid more either DBCC INPUTBUFFER or fn_get_sql was issued to find out exactly what code was being issued. If you could not find the data that you were looking for or built your own scripts, typically you drilled into the master.dbo.syslockinfo table and/or the master.dbo.sysprocesses table, although this was not always the recommended approach. Here are some sample scripts:
SQL Server 2005
In SQL Server 2005, these objects remain available but some are scheduled to be deprecated in SQL Server 2008 which means now is the time to make sure you transition your scripts prior to the SQL Server 2008 migration:
sys.dm_tran_locks DMV in SQL
Server 2005 functionally replacing sp_lock and
master.dbo.syslockinfo a simple query can be issued to
capture the pertinent locking related information. The resource
related information (ResourceSubType, ResourceType, Request_Owner_Type, etc.)
has all be provided as a descriptive name as opposed to having to write case
statements to decipher the values. As such, this simplifies the overall
querying as well as provides the opportunity to query supported objects and
issue COUNT commands, determine specific lock types, etc.
Written By: Jeremy Kadlec -- 10/25/2007 --
SolutionIn SQL Server 2000, a few different stored procedures and tables were used to find the number and types of locks being issued, which spid (system process identifier) the locks were associated with and which user\application issued the commands. In SQL Server 2005, these same stored procedures are available, but Microsoft has also introduced some new dynamic views\functions which replace the system tables as a means to research the locking issues further. Let's dive into these objects and scripts to take the crosswalk from SQL Server 2000 to 2005.
SQL Server 2000
In SQL Server 2000 the main stored procedure to find out which locks were issued was sp_lock. Typically this command was followed by sp_who2 in order to find out more information about the spid in terms of the user, host name, application, CPU and memory utilization. To drill into the spid more either DBCC INPUTBUFFER or fn_get_sql was issued to find out exactly what code was being issued. If you could not find the data that you were looking for or built your own scripts, typically you drilled into the master.dbo.syslockinfo table and/or the master.dbo.sysprocesses table, although this was not always the recommended approach. Here are some sample scripts:
| SQL Server 2000 - Locking Related Objects | ||
| ID | Object | Example |
| 1 | sp_lock - System stored procedure to identify all of the locks issued across the SQL Server instance | EXEC
master.dbo.sp_lock GO |
| 2 | sp_who and sp_who2 - System stored procedure to identify all of the processes across the SQL Server instance | EXEC
master.dbo.sp_who2 GO |
| 3 | master.dbo.syslockinfo - System table with the locks issued across the SQL Server instance | SELECT req_spid AS 'spid',
DB_NAME(rsc_dbid) AS 'Database', OBJECT_NAME(rsc_objid) AS 'Name', rsc_indid AS 'Index', rsc_text AS 'Description', ResourceType = CASE WHEN rsc_type = 1 THEN 'NULL Resource' WHEN rsc_type = 2 THEN 'Database' WHEN rsc_type = 3 THEN 'File' WHEN rsc_type = 4 THEN 'Index' WHEN rsc_type = 5 THEN 'Table' WHEN rsc_type = 6 THEN 'Page' WHEN rsc_type = 7 THEN 'Key' WHEN rsc_type = 8 THEN 'Extent' WHEN rsc_type = 9 THEN 'RID (Row ID)' WHEN rsc_type = 10 THEN 'Application' ELSE 'Unknown' END, Status = CASE WHEN req_status = 1 THEN 'Granted' WHEN req_status = 2 THEN 'Converting' WHEN req_status = 3 THEN 'Waiting' ELSE 'Unknown' END, OwnerType = CASE WHEN req_ownertype = 1 THEN 'Transaction' WHEN req_ownertype = 2 THEN 'Cursor' WHEN req_ownertype = 3 THEN 'Session' WHEN req_ownertype = 4 THEN 'ExSession' ELSE 'Unknown' END, LockRequestMode = CASE WHEN req_mode = 0 THEN 'No access ' WHEN req_mode = 1 THEN 'Sch-S (Schema stability)' WHEN req_mode = 2 THEN 'Sch-M (Schema modification)' WHEN req_mode = 3 THEN 'S (Shared)' WHEN req_mode = 4 THEN 'U (Update)' WHEN req_mode = 5 THEN 'X (Exclusive)' WHEN req_mode = 6 THEN 'IS (Intent Shared)' WHEN req_mode = 7 THEN 'IU (Intent Update)' WHEN req_mode = 8 THEN 'IX (Intent Exclusive)' WHEN req_mode = 9 THEN 'SIU (Shared Intent Update)' WHEN req_mode = 10 THEN 'SIX (Shared Intent Exclusive)' WHEN req_mode = 11 THEN 'UIX (Update Intent Exclusive)' WHEN req_mode = 12 THEN 'BU. (Bulk operations)' WHEN req_mode = 13 THEN 'RangeS_S' WHEN req_mode = 14 THEN 'RangeS_U' WHEN req_mode = 15 THEN 'RangeI_N' WHEN req_mode = 16 THEN 'RangeI_S' WHEN req_mode = 17 THEN 'RangeI_U' WHEN req_mode = 18 THEN 'RangeI_X' WHEN req_mode = 19 THEN 'RangeX_S' WHEN req_mode = 20 THEN 'RangeX_U' WHEN req_mode = 21 THEN 'RangeX_X' ELSE 'Unknown' END FROM master.dbo.syslockinfo GO |
| 4 | master.dbo.sysprocesses - System table with the process information across the SQL Server instance | SELECT * FROM master.dbo.sysprocesses GO |
In SQL Server 2005, these objects remain available but some are scheduled to be deprecated in SQL Server 2008 which means now is the time to make sure you transition your scripts prior to the SQL Server 2008 migration:
- sp_lock - For backward compatibility only and expected to be deprecated in
SQL Server 2008
- Replacement (SQL Server 2005) =
- Expected Replacement (SQL Server 2008) = sys.syslock_information
- Replacement (SQL Server 2005) =
- sp_who and sp_who2 - Not expected to be deprecated in SQL Server 2008
- master.dbo.syslockinfo - For backward compatibility only and expected to be
deprecated in SQL Server 2008
- Replacement (SQL Server 2005) =
- Expected Replacement (SQL Server 2008) = sys.syslock_information
- Replacement (SQL Server 2005) =
- master.dbo.sysprocesses - For backward compatibility only and expected to be
deprecated in SQL Server 2008
- Replacement (SQL Server 2005) = sys.dm_exec_connections,
sys.dm_exec_sessions, sys.dm_exec_requests
- Stay tuned for an upcoming tip on the mapping and opportunities with these three DMVs
- Replacement (SQL Server 2005) = sys.dm_exec_connections,
sys.dm_exec_sessions, sys.dm_exec_requests
|
SQL Server 2005 -
master.sys.dm_tran_locks
|
| -- Simple query to return all records and all
columns SELECT * FROM master.sys.dm_tran_locks; GO -- Focused result set SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id, resource_subtype, resource_description, request_status, request_owner_type, request_mode FROM sys.dm_tran_locks; GO -- Number of lock records per database SELECT COUNT(*) AS 'NumberofLockRecords', DB_NAME(resource_database_id) FROM master.sys.dm_tran_locks GROUP BY resource_database_id; GO -- Query for specific lock types SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id, resource_subtype, resource_description, request_status, request_owner_type, request_mode FROM sys.dm_tran_locks WHERE resource_type IN ('PAGE', 'KEY', 'EXTENT', 'RID'); GO SELECT tl.request_session_id, wt.blocking_session_id, DB_NAME(tl.resource_database_id) AS DatabaseName, tl.resource_type, tl.request_mode, tl.resource_associated_entity_id FROM sys.dm_tran_locks as tl INNER JOIN sys.dm_os_waiting_tasks as wt ON tl.lock_owner_address = wt.resource_address; GO |
AX users were hung in system
Sometimes, users were hung in AX system and you'll find a user have more than
one SPID.
Let's check step by step. For example, we have two users(A and B), A has two spid(A1,A2) and B has two spid(B1,B2)
Execute sp_who in AX database.
A1 has a block spid B2
B1 has block spid A2
So you'll find A is waiting for B and B is waiting for A. It lead two users or more were hung in system.
Execute sp_lock in AX database
SPID ObjID Status
A1 1111111 Grant
A1 1111115 Grant
A1 1111116 Grant
A2 1111112 Grant
B1 1111113 Grant
B2 1111114 waiting
So you'll find B2 is waiting someting for object:111114 and B2 is the root cause of users hung.
Please kill B2 in AX database execute kill B2.
select * from sysobjects where id = '1111114' and you'll find the object name and for your further study.
from: http://www.cnblogs.com/JackyXu1981/archive/2009/04/30/1446827.html
Let's check step by step. For example, we have two users(A and B), A has two spid(A1,A2) and B has two spid(B1,B2)
Execute sp_who in AX database.
A1 has a block spid B2
B1 has block spid A2
So you'll find A is waiting for B and B is waiting for A. It lead two users or more were hung in system.
Execute sp_lock in AX database
SPID ObjID Status
A1 1111111 Grant
A1 1111115 Grant
A1 1111116 Grant
A2 1111112 Grant
B1 1111113 Grant
B2 1111114 waiting
So you'll find B2 is waiting someting for object:111114 and B2 is the root cause of users hung.
Please kill B2 in AX database execute kill B2.
select * from sysobjects where id = '1111114' and you'll find the object name and for your further study.
from: http://www.cnblogs.com/JackyXu1981/archive/2009/04/30/1446827.html
2012年6月1日星期五
批量给多个表的Dimension字段赋初值
[需求]
最近遇到这样一个需求，在实施的时候把系统现有的三个纬度前两个分别用于记录分公司和部门，在做财务凭证或者销售采购订单的时候，需要给这些表相应的Dimension字段的前两个纬度赋值，用户要求根据当前用户所属的分公司和部门直接赋值，而不想自己去选择。
[分析] 最直观的想法就是修改各个表的InitValue()方法，给相应的表赋初值，不过这样的工作量有些大，要修改多个表，并且当需要赋初值的表增加时还需要继续修改相应的表。
AX中用Map实现表方法的共享，于是考虑用Map来实现这个功能。
只有Map还是不行的因为InitValue()这个方法还是在各个表上的,好在AX为了使用模板,所有的initValue()方法都会调用类classFactory的CreateRecord()方法，所以可以把代码添加到这里。
[解决方案]1.创建DimensionMap
创建名为DimensionMap的Map，添加字段Dimension，设定其EDT属性为Dimension，然后把需要设定初始值的表与该Map用Dimension字段建立影射。
2.写一个工具类SysUtility，添加根据当前用户获取部门和分公司的方法
http://www.cnblogs.com/Farseer1215/archive/2007/04/20/721585.html
static Container GetComanpyAndDepartmentOfCurrentUser()
{
Container c;
EmplTable emplTable;
;
select Dimension from emplTable
where emplTable.EmplId == curuserid();
if(emplTable.Dimension[1]&&emplTable.Dimension[2])
return [emplTable.Dimension[1],emplTable.Dimension[2]];
else
throw Error("@GCN588");
}
在这个工具类里再添加一个方法，用于判断表是否需要给Dimension赋初值，判断的标准是如果该表包含在DimensionMap中则需要赋初值，否则不需要
static anytype IfNeedInitDimension(TableId tableId)
{
#AOT
SysGlobalCache sysGlobalCache = classFactory.globalCache();
TreeNode treeNodeMap;
TreeNode treeNodeMapTable;
Name mapTableName;
str strPath;
;
//如果在全局缓存中没有对应的纪录，则添加之
if(!sysGlobalCache.elements(TableStr(DimensionMap)))
{
//找到Map的Mapping路径
strPath = strFmt(#TableMapsPath+@"\%1\Mappings",TableStr(DimensionMap));
treeNodeMap = TreeNode::findNode(strPath);
treeNodeMapTable = treeNodeMap.AOTfirstChild();
while(treeNodeMapTable)
{
//将属性添加到全局缓存
mapTableName = treeNodeMapTable.AOTgetProperty("MappingTable");
sysGlobalCache.set(TableStr(DimensionMap),tableName2Id(mapTableName),mapTableName);
treeNodeMapTable = treeNodeMapTable.AOTnextSibling();
}
}
return sysGlobalCache.get(TableStr(DimensionMap),tableId,false);
}
3.在DimensionMap中添加方法，给表赋初值
void InitDimension()
{
str 10 dirCompany;
str 10 dirDepartment;
;
[dirCompany,dirDepartment] = SysUtility::GetComanpyAndDepartmentOfCurrentUser();
this.Dimension[1] = dirCompany;
this.Dimension[2] = dirDepartment;
}
4.在classFactory的CreateRecord方法中添加代码调用DimensionMap的方法给相关表的Dimension字段赋初值
void createRecord(Common common)
{
SysRecordTemplate sysRecordTemplate;
;
if (! common.isTmp() && new SysDictTable(common.TableId).isRecordTemplateEnabled())
{
sysRecordTemplate = SysRecordTemplate::newCommon(common);
sysRecordTemplate.createRecord();
}
//赋予初始值
if(SysUtility::IfNeedInitDimension(common.TableId))
{
common.DimensionMap::InitDimension();
}
}
最近遇到这样一个需求，在实施的时候把系统现有的三个纬度前两个分别用于记录分公司和部门，在做财务凭证或者销售采购订单的时候，需要给这些表相应的Dimension字段的前两个纬度赋值，用户要求根据当前用户所属的分公司和部门直接赋值，而不想自己去选择。
[分析] 最直观的想法就是修改各个表的InitValue()方法，给相应的表赋初值，不过这样的工作量有些大，要修改多个表，并且当需要赋初值的表增加时还需要继续修改相应的表。
AX中用Map实现表方法的共享，于是考虑用Map来实现这个功能。
只有Map还是不行的因为InitValue()这个方法还是在各个表上的,好在AX为了使用模板,所有的initValue()方法都会调用类classFactory的CreateRecord()方法，所以可以把代码添加到这里。
[解决方案]1.创建DimensionMap
创建名为DimensionMap的Map，添加字段Dimension，设定其EDT属性为Dimension，然后把需要设定初始值的表与该Map用Dimension字段建立影射。
2.写一个工具类SysUtility，添加根据当前用户获取部门和分公司的方法
http://www.cnblogs.com/Farseer1215/archive/2007/04/20/721585.html
static Container GetComanpyAndDepartmentOfCurrentUser(){
Container c; EmplTable emplTable; ; select Dimension from emplTable where emplTable.EmplId == curuserid(); if(emplTable.Dimension[1]&&emplTable.Dimension[2]) return [emplTable.Dimension[1],emplTable.Dimension[2]]; else throw Error("@GCN588");}static anytype IfNeedInitDimension(TableId tableId){ #AOT SysGlobalCache sysGlobalCache = classFactory.globalCache(); TreeNode treeNodeMap; TreeNode treeNodeMapTable; Name mapTableName; str strPath; ; //如果在全局缓存中没有对应的纪录，则添加之 if(!sysGlobalCache.elements(TableStr(DimensionMap))) {
//找到Map的Mapping路径 strPath = strFmt(#TableMapsPath+@"\%1\Mappings",TableStr(DimensionMap)); treeNodeMap = TreeNode::findNode(strPath); treeNodeMapTable = treeNodeMap.AOTfirstChild(); while(treeNodeMapTable) { //将属性添加到全局缓存 mapTableName = treeNodeMapTable.AOTgetProperty("MappingTable"); sysGlobalCache.set(TableStr(DimensionMap),tableName2Id(mapTableName),mapTableName); treeNodeMapTable = treeNodeMapTable.AOTnextSibling(); } } return sysGlobalCache.get(TableStr(DimensionMap),tableId,false);}void InitDimension(){ str 10 dirCompany; str 10 dirDepartment; ; [dirCompany,dirDepartment] = SysUtility::GetComanpyAndDepartmentOfCurrentUser(); this.Dimension[1] = dirCompany; this.Dimension[2] = dirDepartment;}void createRecord(Common common){ SysRecordTemplate sysRecordTemplate; ; if (! common.isTmp() && new SysDictTable(common.TableId).isRecordTemplateEnabled()) { sysRecordTemplate = SysRecordTemplate::newCommon(common); sysRecordTemplate.createRecord(); } //赋予初始值 if(SysUtility::IfNeedInitDimension(common.TableId)) { common.DimensionMap::InitDimension(); }}
订阅：
博文 (Atom)