由于客户的出入库单据数量太多(上千万条),造成出入库相关的报表速度非常慢,为了解决这个问题,通过中间层插件将出入库表关键字段数据按照单据类型同步到了多个副本表(自定义的)中,然后重新开发对应报表(从自定义表中取数),性能有了质的提升,但是少量副本表和出入库表中数据不一致的问题,表现为部分单据的部分数据,目前有发现:其他入库单、其他出库单、销售出库单、盘亏损毁单,有些是副本表中有的单据出库单表中没有,有些是两方的单据分录数对不上,感觉像是某个时间段中间层插件失效一样,我是通过在中间层插件中拦截关键操作(保存、删除、审核、反审核)同步数据,部分代码如下:
1、保存同步:
Public Function EndSave(ByVal sDsn As String, vctSave As KFO.Vector, sReturnMsg As String) As Boolean
'-----------------------------------------------------------------------
'vctSave 值域 | 含义
'-----------------------------------------------------------------------
'FInterID | 单据的内码
'FTransType | 单据的事务类型
'IsRedBill | 当前单据是否是红单
'BillChecked | 当前单据是否审核
'CurrentUserRight | 当前用户是否有权限
'ActiveCtlValueChanged | 当前单据是否改变
'BeforeCloseStockBill | 当前单据是否启用期前仓存单据(暂估入库,销售出库)
'ICClosed | 当前是否关账
'-----------------------------------------------------------------------
On Error GoTo ConnectionCatch
'------------------------------------
'打开连接
Dim sql As String
Dim conn As ADODB.Connection
InitDataEnv sDsn
Set conn = datasource.Connection
On Error GoTo Catch
'------------------------------------
'操作逻辑
Dim FTranType As Long
FTranType = CLng(vctSave.Item(1)("FTransType"))
'同步数据
sql = GetSaveStockBillCopySql(FTranType, CLng(vctSave.Item(1)("FInterID")), vctSave.Item(1)("IsRedBill"))
conn.Execute sql
'------------------------------------
'操作完成,关闭连接
Set conn = Nothing
GetObjectContext.SetComplete
EndSave = True '如果正常完成,需要返回 True
Exit Function
......
2、审核、反审核、删除同步
Public Function HookInvoke(ByVal eventID As Long, dctParams As KFO.Dictionary) As Long
On Error GoTo ConnectionCatch
'------------------------------------
'验证事件有效性
If (eventID <> 300004 And eventID <> 200003) Then
HookInvoke = 1
Exit Function
End If
'验证单据有效性
Dim FTranType As Long
FTranType = CLng(dctParams.Value("TranType"))
If (FTranType <> 1 And FTranType <> 2 And FTranType <> 5 And FTranType <> 10 And FTranType <> 21 And FTranType <> 24 And FTranType <> 28 And FTranType <> 29 And FTranType <> 40 And FTranType <> 41 And FTranType <> 43 And FTranType <> 74 And FTranType <> 83) Then
HookInvoke = 1
Exit Function
End If
'------------------------------------
'打开连接
Dim sDsn As String
Dim conn As ADODB.Connection
Dim sql As String
Dim i As Integer
Dim sDsnItems() As String
Dim keyValue() As String
sDsn = dctParams.Value("sDsn")
InitDataEnv sDsn
Set conn = datasource.Connection
sql = ""
On Error GoTo Catch
'------------------------------------
'操作逻辑
Select Case eventID
Case 300004 '删除单据后事件
'--------------------------------------------------------
'dctParams 值域 | 含义
'--------------------------------------------------------
'sDsn | 连接字符串 dctParams.Value("sDsn")
'InterID | 单据的内码 CLng(dctParams.Value("InterID"))
'TranType | 单据的事务类型 dctParams.Value("TranType")
'OperateCode | 操作码。固定值。值为 8
'ListID | 序时簿ID
'BaseInfo | 连接信息
'BillClsID | 单据类别ID
'TemplateID | 序时簿模版ID
'ErrInfo | 返回的错误信息
'ErrInfo2 | 返回的错误信息
'Return | 返回的错误代码
'ReCheck | 检查控制代码
'ReCheck2 | 检查控制代码
'CheckItemInfo | 返回的信息(信用检查使用)
'--------------------------------------------------------
'删除出入库副本
If (FTranType = 1 Or FTranType = 2 Or FTranType = 5 Or FTranType = 10 Or FTranType = 21 Or FTranType = 24 Or FTranType = 28 Or FTranType = 29 Or FTranType = 40 Or FTranType = 41 Or FTranType = 43) Then
sql = GetDeleteStockBillCopySql(FTranType, CLng(dctParams.Value("InterID")))
End If
Case 200003 '审核退出前事件
'--------------------------------------------------------
'dctParams 值域 | 含义
'--------------------------------------------------------
'sDsn | 连接字符串 dctParams.Value("sDsn")
'InterID | 单据的内码 CLng(dctParams.Value("InterID"))
'TranType | 单据的事务类型 CLng(dctParams.Value("TranType"))
'OperateDelete | 默认值为0
'ROB | 是否是红单
'OperateCode | 操作码: 1------审核
' | 2------反审核
' | 4------保存
' | 8------删除
' | 16-----反作废
' | 32-----作废
'--------------------------------------------------------
Dim fStatus As String
If (CInt(dctParams.Value("OperateCode")) = 1) Then
fStatus = "已审核"
Else
fStatus = "未审核"
End If
'更新出入库副本状态
If (FTranType = 1 Or FTranType = 2 Or FTranType = 5 Or FTranType = 10 Or FTranType = 21 Or FTranType = 24 Or FTranType = 28 Or FTranType = 29 Or FTranType = 40 Or FTranType = 41 Or FTranType = 43) Then
sql = GetAuditOrUnauditStockBillCopySql(FTranType, CLng(dctParams.Value("InterID")), CInt(dctParams.Value("ROB")), fStatus)
End If
End Select
If Trim(sql) <> "" Then
conn.Execute sql
End If
'------------------------------------
'操作成功
Set conn = Nothing
GetObjectContext.SetComplete
HookInvoke = 1
Exit Function
......
请大神帮我分析一下造成这种问题有可能是什么问题呢?跪谢!!!
推荐阅读