在AX开发的中，如果迂到要从两千万级的数据表中提取记录并加以运行生成报表，常规的 while select 代码执行效率极低，用户通常都等不到结果就失去信心了。此时，可以考虑调用数据库的储存过程来实现，储存过程全程在数据库里运行，只提取符合条件的记录给AX，大大减轻网络和AOS的负担，效率提升不止100位。
下面是生成物料库存周转率的代码实例，目的：根據用戶選定的倉庫，開始日期，截止日期，生成庫存物料周轉率報表，報表關鍵欄位：物料編號，物料名稱，單位，成本單價，盤點基數，期初庫存數量，期初庫存貨值，期間出庫數量，期間出庫貨值，期末庫存數量，期末庫存貨值，周轉率。
欄位說明：
盤點基數：距離起始日期之前最近的盤點日期及盤點數量，作為盤點基數。
期初庫存數量：盤點基數 加減 從盤點日到起始日的出入倉數量
期間出庫數量：起始日期到截止日期之間的出倉數量累計
期末庫存數量：期初庫存數量 加減 從起始日期到截止日期的出入倉數量
庫存貨值 = 成本單價 * 數量
周轉率% = 期間出庫貨值*100 / (期初庫存貨值+期末庫存貨值)/2
整个项目分两部份：一、数据库中的存储过程；二、AX代码。
数据库存储过程如下：
USE PROD
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPL_CalcInventoryTurnover]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[SPL_CalcInventoryTurnover]
GO
CREATE PROCEDURE [dbo].[SPL_CalcInventoryTurnover]
@CountingDate CHAR(8),
@DateFrom CHAR(8),
@DateTo CHAR(8),
@InventDimId VARCHAR(20),
@DataAreaId CHAR(3)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CountDate DATETIME, @FromDate DATETIME, @ToDate DATETIME;
SET @CountDate = CONVERT(DATETIME, @CountingDate, 112);
SET @FromDate = CONVERT(DATETIME, @DateFrom, 112);
SET @ToDate = CONVERT(DATETIME, @DateTo, 112);
-- 主結果表
CREATE TABLE #Turnover (
ItemId VARCHAR(20) PRIMARY KEY,
CountingQty DECIMAL(28,12) DEFAULT 0,
OpeningQty DECIMAL(28,12) DEFAULT 0,
ClosingQty DECIMAL(28,12) DEFAULT 0,
IssueQty DECIMAL(28,12) DEFAULT 0
);
-- 預定交易聚合表
CREATE TABLE #TransSum (
ItemId VARCHAR(20) PRIMARY KEY,
OpeningChange DECIMAL(28,12) DEFAULT 0,
ClosingChange DECIMAL(28,12) DEFAULT 0,
IssueQty DECIMAL(28,12) DEFAULT 0
);
-- 1. 盤點基準庫存
INSERT INTO #Turnover (ItemId, CountingQty)
SELECT ItemId, SUM(Counted)
FROM InventJournalTrans WITH (NOLOCK)
WHERE Voucher > ''
AND Counted > 0
AND InventDimId = @InventDimId
AND JournalType = 4
AND TransDate = @CountDate
AND DataAreaId = @DataAreaId
GROUP BY ItemId;
-- 2. 補充新物料
INSERT INTO #Turnover (ItemId, CountingQty)
SELECT ItemId, 0
FROM InventTrans WITH (NOLOCK)
WHERE InventDimId = @InventDimId
AND DatePhysical > @CountDate
AND DatePhysical <= @ToDate
AND DataAreaId = @DataAreaId
AND TransType <= 10
AND NOT EXISTS (
SELECT 1 FROM #Turnover t WHERE t.ItemId = InventTrans.ItemId)
GROUP BY ItemId;
-- 3. 預聚合交易數據
INSERT INTO #TransSum (ItemId, OpeningChange, ClosingChange, IssueQty)
SELECT
ItemId,
SUM(CASE WHEN DatePhysical > @CountDate AND DatePhysical <= @FromDate THEN Qty ELSE 0 END),
SUM(CASE WHEN DatePhysical > @FromDate AND DatePhysical <= @ToDate THEN Qty ELSE 0 END),
SUM(CASE WHEN DatePhysical >= @FromDate AND DatePhysical <= @ToDate AND Qty < 0 THEN -Qty ELSE 0 END)
FROM InventTrans WITH (NOLOCK)
WHERE InventDimId = @InventDimId
AND DataAreaId = @DataAreaId
AND TransType <= 10
AND DatePhysical > @CountDate
AND DatePhysical <= @ToDate
GROUP BY ItemId;
-- 4. 合併到主表
UPDATE t
SET
OpeningQty = t.CountingQty + ISNULL(s.OpeningChange, 0),
ClosingQty = t.CountingQty + ISNULL(s.OpeningChange, 0) + ISNULL(s.ClosingChange, 0),
IssueQty = ISNULL(s.IssueQty, 0)
FROM #Turnover t
LEFT JOIN #TransSum s ON t.ItemId = s.ItemId;
-- 5. 回傳結果
SELECT ItemId, CountingQty, OpeningQty, ClosingQty, IssueQty
FROM #Turnover
ORDER BY ItemId;
DROP TABLE #TransSum;
DROP TABLE #Turnover;
END
GO
GRANT EXECUTE ON [dbo].[SPL_CalcInventoryTurnover] TO axView
GOAX中调用存储过程并在FORM的GRID中展示结果的代码
//sandal 全部物料指定期间的庫存周轉率
//COM改成command-解决防執行超時问题
void SPL_EnquireNoItem()
{
InventTable _inventTable;
InventTableModule _inventTableModule;
str _strCountDate, _strFromDate, _strToDate;
COM adoConnection;
COM adoCommand; //New Component
COM adoRecordset;
COM adoFields;
COM adoField;
COMVariant _tempVar;
str sqlCmd;
str _itemId, _tempStr;
real _countingQty, _openingQty, _closingQty, _issueQty, _avgAmt;
int _recordCount;
str _connStr = "Provider=SQLOLEDB.1;Data Source=192.168.0.186;Initial Catalog=PROD;User ID=axView;Password=11111111111;";
;
_strCountDate = date2str(g_ckDate,321,2,0,2,0,4); //轉字符YYYYMMDD
_strFromDate = date2str(g_FromDate,321,2,0,2,0,4);
_strToDate = date2str(g_ToDate,321,2,0,2,0,4);
//臨時驗證參數代碼
//info(strFmt("盤點日: %1, 期初: %2, 期末: %3", _strCountDate, _strFromDate, _strToDate));
//info(strFmt("倉庫:'%1', 公司: %2", g_fullDimId, curext()));
//建立鏈接
adoConnection = new COM("ADODB.Connection");
adoConnection.Open(_connStr);
//建立Command對象並設置超時時間
adoCommand = new COM("ADODB.Command");
adoCommand.ActiveConnection(adoConnection);
adoCommand.CommandTimeout(600); // 超時10分鐘
adoCommand.CommandType(1); // adCmdText = 1
//取過程命令
sqlCmd = strFmt(
"EXEC [dbo].[SPL_CalcInventoryTurnover] '%1', '%2', '%3', '%4', '%5'",
_strCountDate, _strFromDate, _strToDate, g_fullDimId, curext() );
adoCommand.CommandText(sqlCmd);
adoRecordset = adoCommand.Execute(); // 通過 Command 執行
//adoRecordset = adoConnection.Execute(sqlCmd); //超時失敗
if (!adoRecordset || (adoRecordset.BOF() && adoRecordset.EOF()))
{
info('沒有取到數據');
if (adoRecordset)
adoRecordset.Close();
adoConnection.Close();
return;
}
//遍歷寫入臨時表
_recordCount = 0;
adoRecordset.MoveFirst();
while (!adoRecordset.EOF())
{
_countingQty= 0;
_openingQty = 0;
_closingQty = 0;
_issueQty = 0;
adoFields = adoRecordset.Fields();
adoField = adoFields.Item(0);
_tempVar = adoField.Value();
_itemId = (_tempVar.variantType() == 0) ? "" : _tempVar.bstr();
adoField = adoFields.Item(1);
_tempVar = adoField.Value();
_countingQty= (_tempVar.variantType() == 0) ? 0 : _tempVar.decimal();
adoField = adoFields.Item(2);
_tempVar = adoField.Value();
_openingQty = (_tempVar.variantType() == 0) ? 0 : _tempVar.decimal();
adoField = adoFields.Item(3);
_tempVar = adoField.Value();
_closingQty = (_tempVar.variantType() == 0) ? 0 : _tempVar.decimal();
adoField = adoFields.Item(4);
_tempVar = adoField.Value();
_issueQty = (_tempVar.variantType() == 0) ? 0 : _tempVar.decimal();
//_issueQty = str2num(_tempStr); //無效的空值
//_itemId = adoRecordset.Fields("ItemId").Value();//Error
//調試信息
//info(strFmt("第 %1 條記錄", _recordCount + 1));
//info(strFmt("ItemId: '%1'", _itemId));
_inventTable = InventTable::find(_itemId);
_inventTableModule = InventTableModule::find(_itemId, ModuleInventPurchSales::Invent);
SPL_tmpEnqTable.clear();
SPL_tmpEnqTable.ItemID = _itemId;
SPL_tmpEnqTable.ItemName = _inventTable.ItemName;
SPL_tmpEnqTable.BomUnitID = _inventTable.BOMUnitId;
SPL_tmpEnqTable.Price = _inventTableModule.Price;
// 映射存儲過程結果 → Grid 欄位
SPL_tmpEnqTable.Qty3 = _countingQty; // 盤點數量
SPL_tmpEnqTable.Qty4 = _openingQty; // 期初數量
SPL_tmpEnqTable.Qty5 = _issueQty; // 出庫數量
SPL_tmpEnqTable.Qty6 = _closingQty; // 期末數量
SPL_tmpEnqTable.Amt1 = decRound(_openingQty * SPL_tmpEnqTable.Price, 2);
SPL_tmpEnqTable.Amt2 = decRound(_issueQty * SPL_tmpEnqTable.Price, 2);
SPL_tmpEnqTable.Amt3 = decRound(_closingQty * SPL_tmpEnqTable.Price, 2);
_avgAmt = (SPL_tmpEnqTable.Amt1 + SPL_tmpEnqTable.Amt3)/2;
if (_avgAmt != 0)
SPL_tmpEnqTable.Qty1 = decRound(SPL_tmpEnqTable.Amt2*100 / _avgAmt,0);
else
SPL_tmpEnqTable.Qty1 = 0;
SPL_tmpEnqTable.insert();
_recordCount++;
adoRecordset.MoveNext();
}
//清理資源
adoRecordset.Close();
adoConnection.Close();
}