个人博客 用于记载日常收集的一些技术文章 ...

K3 BOS : win2019 + K3 15.1 配置 K3 BOS : win2019 + K3 15.1 配置 Source :Microsoft OLE DB Provider for SQL Server
Detail :新事务不能登记到指定的事务处理器中。
-----------------------------------------------------------------------------------------------------------------------------------------------------------
一、在【中间层】和【数据库】服务器　互相添加hosts解析：

1、以管理员身份运行【记事本】，点击菜单【文件/打开】【C:\WINDOWS\system32\drivers\etc\hosts】文件
2、在最后一行回车，按照格式“IP 计算机名”输入K3 中间服务器的【IP地址】【计算机名】，【保存】

　在【数据库】添加【中间层】IP 计算机名：　192.168.188.151 K3-151
　在【中间层】添加【数据库】IP 计算机名：　192.168.188.208 sql-2008

-----------------------------------------------------------------------------------------------------------------------------------------------------------
二、在【中间层】和【数据库】服务器　进行如下设置：

【管理工具】【组件服务】【计算机】【我的电脑】按右键【属性】

　　1、【默认属性】标签页：勾上【在此计算机上启用分布式COM】下面是【连接】【标识】

　　2、【默认协议】标签页：【面向连接的TCP/IP协议】

　　3、【COM安全】标签页：【编辑限制】【Everyone】完全控制的权限。

　　4、【MSDTC】标签页：勾选【使用本地协调器】


【管理工具】【组件服务】【计算机】【我的电脑】【Distributed Transaction Coordinator】【本地 DTC】按右键【属性】

　　【安全】标签页：勾上【网络DTC访问】【允许远程客户端】【允许远程管理】【允许入站】【允许出站】【不要求进行验证】

-----------------------------------------------------------------------------------------------------------------------------------------------------------
三、在【中间层】和【数据库】服务器　进行如下操作：

以管理员身份运行 CMD

msdtc -uninstall
msdtc -install
msdtc -resetlog
net start msdtc

【运行】输入【services.msc】,在【服务】中找到【Distributed Transaction Coordinator】设为【自动】，确保处于【启动】状态。

-----------------------------------------------------------------------------------------------------------------------------------------------------------
最后，重启服务器，再运行金蝶软件。 郭少锋 编辑 2023-05-01 22:44:29 创建 2023-05-01 22:43:58 K3 BOS K3 BOS : SQL 获取年月日 K3 BOS : SQL 获取年月日 方法一：利用DATENAME

在SQL数据库中，DATENAME(datetype,date)函数的作用是从日期中提取指定部分数据,其返回类型是nvarchar.datetype类型见附表1.

SELECT DATENAME(year,GETDATE())+'-'+ DATENAME(MONTH,GETDATE())+'-'+ DATENAME(DAY,GETDATE())
SELECT DATENAME(yyyy,GETDATE())+'-'+ DATENAME(MM,GETDATE())+'-'+ DATENAME(dd,GETDATE())

方法二：利用CONVERT

CONVERT(type(length),date,style) 函数是把日期转换为新数据类型的通用函数,可以用不同的格式显示日期/时间数据。style类型见附表2，另附件3为CONVERT()用法

SELECT CONVERT(VARCHAR(20),YEAR(GETDATE())) +'-'+ CONVERT(VARCHAR(20),MONTH(GETDATE()))+'-'+ CONVERT(VARCHAR(20),DAY(GETDATE()))
SELECT CONVERT(VARCHAR(20),GETDATE(),23)

下面两种方法 错误：因为YEAR(),MONTH(),DAY(),DATEPART()返回值都是INT类型，所以结果是相加

SELECT YEAR(GETDATE()) +'-'+ MONTH(GETDATE()) +'-'+ DAY(GETDATE())
SELECT DATEPART(yyyy,GETDATE())+'-'+ DATEPART(MM,GETDATE())+'-'+ DATEPART(dd,GETDATE())


日期部分 缩写
year yy, yyyy
quarter qq, q
month mm,m
dayofyear dy, y
day dd,d
week wk,ww
weeekday dw
hour hh
minute mi,n
second ss,s
millisecond ms

SELECT CONVERT(varchar(100), GETDATE(), 0)-- 08 25 2017 10:26AM
SELECT CONVERT(varchar(100), GETDATE(), 100)--08 25 2017 10:26AM
SELECT CONVERT(varchar(100), GETDATE(), 1)-- 08/25/17
SELECT CONVERT(varchar(100), GETDATE(), 101)--08/25/2017
SELECT CONVERT(varchar(100), GETDATE(), 2)-- 17.08.25
SELECT CONVERT(varchar(100), GETDATE(), 102)--2017.08.25

style(2位表年份) style(4位表年份) Style 格式
100或0 mon dd yyyy hh:miAM （或者 PM）
1 101 美国 mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 英法 dd/mm/yy
4 104 德国 dd.mm.yy
5 105 意大利 dd-mm-yy
6 106 dd mon yy
7 107 Mon dd, yy
108或8或24 hh:mm:ss(无年份月日)
109或9 mon dd yyyy hh:mi:ss:mmmAM(或者 PM)
10 110 美国 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO yymmdd
113或13 欧洲默认 dd mon yyyy hh:mm:ss:mmm(24h)
14 114 hh:mi:ss:mmm(24h)(无年份月日)
120或20 ODBC yyyy-mm-dd hh:mi:ss(24h)
121或21或25 yyyy-mm-dd hh:mi:ss.mmm(24h)
23 yyyy-mm-dd
126 yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM 郭少锋 创建 2023-04-21 09:24:24 K3 BOS K3 BOS : SQL 自动递增 字段 K3 BOS : SQL 自动递增 字段 drop table a_CustBillList;
create table a_CustBillList(

FID int IDENTITY(1,1) PRIMARY KEY -- 自动递增
,FLevel int NULL
,FSort int NULL

,FUserName varchar(20) NULL
,FCustName varchar(100) NULL
,FClassTypeName varchar(20) NULL

,FDate varchar(20) NULL
,FBillNo varchar(50) NULL
,FIndex int NULL
,FItemName nvarchar(200) NULL

,FQtySale decimal(23, 10) NULL
,FUnitSale varchar(10) NULL
,FPriceSale decimal(23, 10) NULL

,FAmountSale decimal(23, 10) NULL
,FAmountRec decimal(23, 10) NULL
,FAmountRemain decimal(23, 10) NULL
,FAmountCal decimal(23, 10) NULL

,FBillNo_Order varchar(50) NULL
,FNoteItem varchar(200) NULL
); 郭少锋 编辑 2023-04-19 14:35:14 创建 2023-04-19 14:34:20 K3 BOS K3 BOS : SQL 累计 K3 BOS : SQL 累计 前言，要实现按某个字段统计，直接用count/sum……group by语句就可以实现，
但是要实现累计，比如按时间累计，从12月3号开始累计数据，
比如：
4号统计3、4号的数据，
5号统计3、4、5号的数据，

递归累计：

with tab as(SELECT GIVE_DAY,COUNT(DISTINCT MOBILE) NUM FROM(SELECT a.*,to_char(GIVE_TIME,'yyyy-mm-dd') GIVE_DAY from T_SURVEY_USERINFO a) A WHERE A.GIVE_DAY>'2015-12-02' GROUP BY GIVE_DAY)
select a.GIVE_DAY,sum(b.NUM) NUM from tab a,tab b where b.GIVE_DAY<=a.GIVE_DAY group by a.GIVE_DAY order by a.GIVE_DAY

其实，上面的sql语句还可以有优化的空间，就是tab部分，可以不用count，
仅仅只是转换一下日期格式，
然后在后面的select语句中用count进行统计。 郭少锋 编辑 2023-04-18 23:36:30 创建 2023-04-18 22:47:44 K3 BOS K3 BOS : K3 调试跟踪版本 K3 BOS : K3 调试跟踪版本 K3 调试跟踪版本
C:\Program Files (x86)\Kingdee\K3ERP\KdMainDBG.exe 郭少锋 创建 2023-04-14 08:40:36 K3 BOS K3 BOS : K3 审核前检查，审核后反写 K3 BOS : K3 审核前检查，审核后反写 '【销售变更单 210001211】【审批流插件】
'-----------------------------------------------------------------------------------------------------------------------------

'【审核成功后】反写源单
'【驳回成功后】反写源单

'【审核前】检查：源单已记账，请先【删除凭证】 / 请先【反确认】源单
'【驳回前】检查：源单已记账，请先【删除凭证】 / 请先【反确认】源单

'-----------------------------------------------------------------------------------------------------------------------------




Private WithEvents m_ApprovalInterface As MixedApprovalEvents

Public Sub Show(ByVal oApprovalInterface As Object)

Set m_ApprovalInterface = oApprovalInterface '注意: 此方法必须存在, 请勿修改
End Sub

Private Sub Class_Terminate()

Set m_ApprovalInterface = Nothing '释放接口对象 '注意: 此方法必须存在, 请勿修改
End Sub




Private Sub m_ApprovalInterface_CheckDone(ByVal lCheckNodeIndex As Long, ByVal bSucceed As Variant) '审核后

aFID = m_ApprovalInterface.BillData("FID")

If lCheckNodeIndex = 2000 And bSucceed = True Then
m_ApprovalInterface.K3Lib.UpdateData ("exec a_SeAlterFan " & aFID & " ,'Check' --【审核】反写【源单数据】 ")
End If

End Sub






Private Sub m_ApprovalInterface_UnCheckDone(ByVal lUnCheckNodeIndex As Long, ByVal bSucceed As Variant) '驳回后

aFID = m_ApprovalInterface.BillData("FID")

If bSucceed = True Then
m_ApprovalInterface.K3Lib.UpdateData ("exec a_SeAlterFan " & aFID & " ,'UnCheck' --【驳回】反写【源单数据】 ")
End If
End Sub






Private Sub m_ApprovalInterface_CheckOptionConfigured(dctConfig As KFO.IDictionary, bCancel As Boolean) '审核前

If m_ApprovalInterface.BillData Is Nothing Then Exit Sub '没有数据，退出
'--------------------------------------------------------------------------------------------------------------------------------------------
Dim aType As String: aType = "启动审核"

If dctConfig.GetValue("IsBusinessCheck") Then 'True 启动审核

aType = "审核"
End If

bCancel = aCheck(aType, m_ApprovalInterface.BillData("FID"))

End Sub


Private Sub m_ApprovalInterface_UncheckOptionConfigred(dctConfig As KFO.IDictionary, bCancel As Boolean) '驳回前

If Not dctConfig.GetValue("IsBusinessCheck", False) Then Exit Sub '未启动审核，退出

If m_ApprovalInterface.BillData Is Nothing Then Exit Sub '没有数据，退出

'--------------------------------------------------------------------------------------------------------------------------------------------
bCancel = aCheck("驳回", m_ApprovalInterface.BillData("FID"))

End Sub


Function aCheck(aType As String, aFID As Long) As Boolean '检查源单

aMsg = "": aCheck = False

aSql = "select distinct a2.FID,a1.FBillNo,a1.FVoucherId,a1.FConfirmId from a_StockBill a1 join a_SeAlterEntry a2 on a1.FID = a2.FID_SRC and a2.FClassID_SRC in ( 210001202,210001203 ) and a2.FID = " & aFID

Set aRecordset = m_ApprovalInterface.K3Lib.GetData(aSql): aBillNo = ""
If aRecordset.RecordCount > 0 Then

For i = 1 To aRecordset.RecordCount

aFBillNo = aRecordset("FBillNo"): aFConfirmId = aRecordset("FConfirmId"): aFVoucherId = aRecordset("FVoucherId"):

If aFVoucherId <> "" Then
aMsg = aType & "失败 ！源单已记账，请先【删除凭证】 " & aFVoucherId: GoTo aExit

ElseIf aFConfirmId <> "0" Then
aMsg = aType & "失败 ！请先【反确认】源单 " & aFBillNo: GoTo aExit
End If

aRecordset.MoveNext
Next
End If

Exit Function

'--------------------------------------------------------------------------------------------------------------------------------------------
aExit:
aCheck = True: Set aRecordset = Nothing: MsgBox aMsg, vbExclamation, "一星提示": Exit Function

End Function 郭少锋 编辑 2023-04-13 10:32:56 创建 2023-04-13 10:32:23 K3 BOS K3 BOS : K3 审批流事件 K3 BOS : K3 审批流事件 Option Explicit

Attribute m_ApprovalInterface.VB_VarHelpID = -1

Private WithEvents m_ApprovalInterface As MixedApprovalEvents

Public Sub Show(ByVal oApprovalInterface As Object)

Set m_ApprovalInterface = oApprovalInterface '注意: 此方法必须存在, 请勿修改
End Sub

Private Sub Class_Terminate()

Set m_ApprovalInterface = Nothing '释放接口对象 '注意: 此方法必须存在, 请勿修改
End Sub




Private Sub m_ApprovalInterface_UncheckOptionConfigred(dctConfig As KFO.IDictionary, bCancel As Boolean) '驳回时

Dim aFID As Long: aFID = m_ApprovalInterface.BillData("FID")
Dim aDay As Long

Dim aFKey As String
Dim aFDate As Date

'--------------------------------------------------------------------------------------------------------------------------------------------
If Not dctConfig.GetValue("IsBusinessCheck", False) Then Exit Sub '判断是否业务审核

If m_ApprovalInterface.BillData Is Nothing Then Exit Sub

'--------------------------------------------------------------------------------------------------------------------------------------------
If Not m_ApprovalInterface.BillEvent Is Nothing Then '单据界面 触发 m_ApprovalInterface.BillEvent = m_oBillInterface

aFKey = m_ApprovalInterface.BillEvent.TableInfo("Map")("FDate")
aFDate = m_ApprovalInterface.BillEvent.Data("page1")(aFKey)("FFLD")

aDay = DateDiff("d", aFDate, Date) '天数计算

If aDay > 30 Then

bCancel = True '停止驳回操作
MsgBox "单据驳回失败! 不允许驳回30天以前单据的业务审核级次!" & sErrMsg, vbExclamation, "金蝶提示"
End If

'--------------------------------------------------------------------------------------------------------------------------------------------
ElseIf Not m_ApprovalInterface.ListEvent Is Nothing Then '序时薄界面 触发 m_ApprovalInterface.ListEvent = m_oListInterface

aFKey = m_ApprovalInterface.ListEvent.DataSrv.TableInfo("Map")("FDate")
aFDate = m_ApprovalInterface.BillData("page1")(aFKey)("FFLD")

aDay = DateDiff("d", aFDate, Date)

If aDay > 30 Then

bCancel = True '停止驳回操作
MsgBox "单据驳回失败! 不允许驳回30天以前单据的业务审核级次!" & sErrMsg, vbExclamation, "金蝶提示"
End If

'--------------------------------------------------------------------------------------------------------------------------------------------
End If
End Sub 郭少锋 编辑 2023-04-12 16:38:04 创建 2023-03-29 21:17:59 K3 BOS K3 BOS : 小数前加0 K3 BOS : 小数前加0 Function Num(aTxt As String) As String

If Left(aTxt) = "." Then
aTxt = "0" & aTxt '小数点 前加0
End If
End Function

aTxt = aTxt & " 销售数量 +" & Num(aFQtySale2 - aFQtySale)
aTxt = aTxt & " 销售金额 +" & Num(Round(aFAmountSale2 - aFAmountSale, 2)) ‘金额 只保留2位小数 郭少锋 编辑 2023-04-11 23:05:25 创建 2023-04-11 10:25:18 K3 BOS K3 BOS : 审批流插件 K3 BOS : 审批流插件 '【审批流插件】
'-----------------------------------------------------------------------------------------------------------------------------

'启动审核
'
' 2023-04-11 11:53:25.455 Show(oApprovalInterface)
' 2023-04-11 11:53:33.894 m_ApprovalInterface_CheckPrepare( False,False )
' 2023-04-11 11:53:33.894 m_ApprovalInterface_CheckPrepareExt( True,False,dctCheckInfo,False )
' 2023-04-11 11:53:33.894 m_ApprovalInterface_CheckOptionConfigured( dctConfig,False )
' 2023-04-11 11:53:34.19 m_ApprovalInterface_ReturnMixedPromptInfoExt(dctMixedCheckInfo,dctMixedPromptInfoExt)
' 2023-04-11 11:53:34.19 m_ApprovalInterface_CheckDone( 0,True )
'
'节点审核
'
' 2023-04-11 11:54:13.815 m_ApprovalInterface_CheckPrepare( False,False )
' 2023-04-11 11:54:13.815 m_ApprovalInterface_CheckPrepareExt( False,False,dctCheckInfo,False )
' 2023-04-11 11:54:13.815 m_ApprovalInterface_CheckOptionConfigured( dctConfig,False )
' 2023-04-11 11:54:13.830 m_ApprovalInterface_ReturnMixedPromptInfoExt(dctMixedCheckInfo,dctMixedPromptInfoExt)
' 2023-04-11 11:54:13.846 m_ApprovalInterface_CheckDone( 2000,True )
' 2023-04-11 11:54:14.938 Class_Terminate()
' 2023-04-11 11:54:15.32 Show(oApprovalInterface)
'
'驳回
'
' 2023-04-11 11:54:25.359 m_ApprovalInterface_UncheckPrepare( False,False )
' 2023-04-11 11:54:25.359 m_ApprovalInterface_UnCheckPrepareExt( False,dctCheckInfo,False )
' 2023-04-11 11:54:25.359 m_ApprovalInterface_UncheckOptionConfigred( dctConfig,False )
' 2023-04-11 11:54:25.374 m_ApprovalInterface_ReturnMixedPromptInfoExt(dctMixedCheckInfo,dctMixedPromptInfoExt)
' 2023-04-11 11:54:26.498 m_ApprovalInterface_UnCheckDone( -1,True )
' 2023-04-11 11:54:26.513 Class_Terminate()
' 2023-04-11 11:54:26.591 Show(oApprovalInterface)

'-----------------------------------------------------------------------------------------------------------------------------



Private WithEvents m_ApprovalInterface As MixedApprovalEvents


Function aTime() As String

aTime = Format(Now(), "yyyy-mm-dd hh:MM:ss") & "." & (Timer() * 1000 Mod 1000) '时间格式 + 毫秒
End Function



Public Sub Show(ByVal oApprovalInterface As Object)

Debug.Print aTime & " Show(oApprovalInterface)"
Set m_ApprovalInterface = oApprovalInterface '注意: 此方法必须存在, 请勿修改
End Sub

Private Sub Class_Terminate()

Debug.Print aTime & " Class_Terminate()"
Set m_ApprovalInterface = Nothing '释放接口对象 '注意: 此方法必须存在, 请勿修改
End Sub





Private Sub m_ApprovalInterface_CheckPrepare(bShowConfigurationManager As Boolean, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_CheckPrepare( " & bShowConfigurationManager & "," & bCancel & " )"
End Sub

Private Sub m_ApprovalInterface_CheckPrepareExt(ByVal bIsStart As Boolean, bShowExtinfo As Boolean, dctCheckInfo As KFO.IDictionary, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_CheckPrepareExt( " & bIsStart & "," & bShowExtinfo & ",dctCheckInfo," & bCancel & " )"
End Sub

Private Sub m_ApprovalInterface_CheckOptionConfigured(dctConfig As KFO.IDictionary, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_CheckOptionConfigured( dctConfig" & "," & bCancel & " )"
End Sub

Private Sub m_ApprovalInterface_ReturnMixedPromptInfoExt(ByVal dctMixedCheckInfo As KFO.IDictionary, dctMixedPromptInfoExt As KFO.IDictionary)

Debug.Print aTime & " m_ApprovalInterface_ReturnMixedPromptInfoExt(dctMixedCheckInfo,dctMixedPromptInfoExt)"
End Sub

Private Sub m_ApprovalInterface_CheckDone(ByVal lCheckNodeIndex As Long, ByVal bSucceed As Variant)

Debug.Print aTime & " m_ApprovalInterface_CheckDone( " & lCheckNodeIndex & "," & bSucceed & " )"
End Sub






Private Sub m_ApprovalInterface_UncheckPrepare(bShowConfigurationManager As Boolean, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_UncheckPrepare( " & bShowConfigurationManager & "," & bCancel & " )"
End Sub

Private Sub m_ApprovalInterface_UnCheckPrepareExt(bShowExtinfo As Boolean, dctCheckInfo As KFO.IDictionary, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_UnCheckPrepareExt( " & bShowExtinfo & ",dctCheckInfo" & "," & bCancel & " )"
End Sub

Private Sub m_ApprovalInterface_UncheckOptionConfigred(dctConfig As KFO.IDictionary, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_UncheckOptionConfigred( dctConfig" & "," & bCancel & " )"
End Sub

Private Sub m_ApprovalInterface_UnCheckDone(ByVal lUnCheckNodeIndex As Long, ByVal bSucceed As Variant)

Debug.Print aTime & " m_ApprovalInterface_UnCheckDone( " & lUnCheckNodeIndex & "," & bSucceed & " )"
End Sub






Private Sub m_ApprovalInterface_AfterUnPassedCheck(ByVal dctCurrentNode As KFO.IDictionary, dctPrepareInfo As KFO.IDictionary, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_AfterUnPassedCheck( dctCurrentNode,dctPrepareInfo" & "," & bCancel & " )"
End Sub

Private Sub m_ApprovalInterface_BeforeUnPassedCheck(ByVal dctCurrentNode As KFO.IDictionary, dctPrepareInfo As KFO.IDictionary, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_BeforeUnPassedCheck( dctCurrentNode,dctPrepareInfo" & "," & bCancel & " )"
End Sub

Private Sub m_ApprovalInterface_MessageConfigred(MsgObj As Object, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_MessageConfigred( MsgObj" & "," & bCancel & " )"
End Sub

Private Sub m_ApprovalInterface_ReMixedCheck(ByVal lBillID As Long, ByVal dctError As KFO.IDictionary, dctBatchExtInfo As KFO.IDictionary, bReCheck As Boolean)

Debug.Print aTime & " m_ApprovalInterface_ReMixedCheck( " & lBillID & "dctError,dctBatchExtInfo," & bReCheck & " )"
End Sub

Private Sub m_ApprovalInterface_ReUnMixedCheck(ByVal lBillID As Long, ByVal dctError As KFO.IDictionary, dctBatchExtInfo As KFO.IDictionary, bReUnCheck As Boolean)

Debug.Print aTime & " m_ApprovalInterface_ReUnMixedCheck( " & lBillID & "dctError,dctBatchExtInfo" & bReUnCheck & " )"
End Sub

Private Sub m_ApprovalInterface_SubmitDone(ByVal lSubmitRouteIndex As Long, ByVal bSucceed As Variant)

Debug.Print aTime & " m_ApprovalInterface_SubmitDone( " & lSubmitRouteIndex & "," & bSucceed & " )"
End Sub

Private Sub m_ApprovalInterface_SubmitOptionConfigured(dctConfig As KFO.IDictionary, Cancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_SubmitOptionConfigured( dctConfig" & "," & Cancel & " )"
End Sub

Private Sub m_ApprovalInterface_SubmitPrepare(bShowConfigurationManager As Boolean, bCancel As Boolean)

Debug.Print aTime & " m_ApprovalInterface_SubmitPrepare( " & bShowConfigurationManager & "," & bCancel & " )"
End Sub 郭少锋 创建 2023-04-11 11:57:44 K3 BOS K3 BOS : VB 空值转为0 K3 BOS : VB 空值转为0 x = Val(a) '如果a是空字符串，x为0 郭少锋 创建 2023-04-11 10:19:38 K3 BOS