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

K3 BOS : K3 异常用户信息清理 K3 BOS : K3 异常用户信息清理 select * from t_User -- 用户表

select * from t_UserProfile where FUserID = 16480 -- 用户配置
select * from ICClassUserProfile where FUserID = 16480 -- 用户配置

select * from t_LedgerPageSetup where FUserID = 16480 -- 打印设置方案
select * from ICReportProfile where FUserID = 16480 -- 用户报表方案

select * from t_GroupAccess where FUserID = 16480 -- 用户权限：查询权,管理权
select * from t_AccessControl where FUserID = 16480 -- 用户权限：高级

/*
delete t_UserProfile where FUserID = 16480
delete ICClassUserProfile where FUserID = 16480

delete t_LedgerPageSetup where FUserID = 16480
delete ICReportProfile where FUserID = 16480

delete t_GroupAccess where FUserID = 16480
delete t_AccessControl where FUserID = 16480

【采购订单 权限】
【供应商查看、金额查看】会隐藏字段，如果是这些字段不显示，要查看一下是否有权限
*/ 郭少锋 编辑 2023-03-20 13:38:20 创建 2023-03-20 13:33:21 K3 BOS K3 BOS : KIS-U-42 销售订单 关联 关闭标志 K3 BOS : KIS-U-42 销售订单 关联 关闭标志 ---------------------------------------------------------------------------------------------------------------------------------------------
-- 销售订单：整单关闭标志

UPDATE a1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET FClosed = CASE WHEN EXISTS( SELECT 1 FROM SEOrderEntry WHERE FInterID = a1.FInterID AND FEntrySelfS0149W > 0 AND FMRPClosed = 0 ) -- 【存在】【数量未出库】+【行未关闭】
THEN 0
ELSE 1
END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
,FStatus = CASE WHEN EXISTS( SELECT 1 FROM SEOrderEntry WHERE FInterID = a1.FInterID AND FEntrySelfS0149W > 0 AND FMRPClosed = 0 ) -- 【存在】【数量未出库】+【行未关闭】
THEN 1
ELSE 3 -- FStatus 1 未下推 2 已关联 3 已关闭 FChildren 关联单据数 ,反审核 判断 FStatus = 2 OR FChildren > 0 存在关联单据
END
-----------------------------------------------------------------------------------------------
FROM SEOrder a1
WHERE FInterID IN ( @FOrderInterId ) 郭少锋 编辑 2023-03-14 15:12:39 创建 2023-03-14 15:11:23 K3 BOS SQL : SQL Print 字符串 超过8000会截断 SQL : SQL Print 字符串 超过8000会截断 解决方法：

Declare @sqldata varchar(max) set @sqldata = @Sql2

WHILE LEN(@sqldata) > 8000
BEGIN
print LEN(@sqldata); print left(@sqldata,8000); set @sqldata= right(@sqldata,LEN(@sqldata)-8000+3);
END
print LEN(@sqldata); print( @sqldata) 郭少锋 编辑 2023-03-14 13:18:09 创建 2023-03-11 19:23:30 SQL K3 BOS : SQL 对比数据 是否有差异 K3 BOS : SQL 对比数据 是否有差异 SELECT
FTranType2,FInterID,FEntryID
,FDetailID,FSourceTranType,FSourceInterId,FSourceEntryID,FSourceBillNo,FOrderInterID,FOrderEntryID,FOrderBillNo
,FItemID,FAuxPropID,FDCStockID,FBatchNo,FUnitID,FQtyMust,FAuxQtyMust,FQty,FAuxQty,FQtyInvoice,FAuxQtyInvoice,FPrice,FAuxPrice,FAmount
,FEntrySelfB0139,FEntrySelfB0138,FConsignPrice,FConsignAmount,FDiscountRate,FDiscountAmount
,FNote,FPlanMode,FChkPassItem,FComplexQty

FROM ICStockBillEntry WHERE FInterID = 352587 AND FEntryID = 1

EXCEPT -- 对比有差异的数据

SELECT
FTranType2,FInterID,FEntryID
,FDetailID,FSourceTranType,FSourceInterId,FSourceEntryID,FSourceBillNo,FOrderInterID,FOrderEntryID,FOrderBillNo
,FItemID,FAuxPropID,FDCStockID,FBatchNo,FUnitID,FQtyMust,FAuxQtyMust,FQty,FAuxQty,FQtyInvoice,FAuxQtyInvoice,FPrice,FAuxPrice,FAmount
,FEntrySelfB0139,FEntrySelfB0138,FConsignPrice,FConsignAmount,FDiscountRate,FDiscountAmount
,FNote,FPlanMode,FChkPassItem,FComplexQty

FROM ICStockBillEntry_bak WHERE FInterID = 352588 AND FEntryID = 1 郭少锋 创建 2023-03-14 11:42:46 K3 BOS K3 BOS : K3 15.1 转换显示 K3 BOS : K3 15.1 转换显示 UPDATE ICClassTableInfo SET FLookUpList = 'VALUELIST{1=Y,0=}' WHERE FClassTypeID = 200000003 AND FFieldName = 'FClose' --【转换显示】 FCtlType = 8 ,FSourceType = 9 ,FDspColType = 500
UPDATE ICClassType SET FID=FID 郭少锋 创建 2023-03-13 20:53:35 K3 BOS K3 BOS : K3 15.1 新单读取 字段数据 K3 BOS : K3 15.1 新单读取 字段数据 Private Sub m_BillInterface_AfterLoadBill() '单据加载后事件

Dim sKey As String
Dim sValue As String
Dim dtValue As Date
Dim dbQty As Double
'-------------------------------------------------------------------
sKey = m_BillInterface.TableInfo("Map")("FTime")
sValue = m_BillInterface.Data("page1")(sKey)("FFLD")

If Len(sValue) > 0 Then
dtValue = CDate(sValue) '文本转日期
End If
'-------------------------------------------------------------------
sKey = m_BillInterface.TableInfo("Map")("FQty")
sValue = m_BillInterface.Data("page2")(1)(sKey)("FFLD")

If Len(sValue) > 0 And IsNumeric(sValue) Then
dbQty = CDbl(sValue) '文本转数字
End If

MsgBox "审核日期为：" & dtValue & " 第一行实收数量为：" & dbQty

End Sub 郭少锋 编辑 2023-03-13 14:17:07 创建 2023-03-12 23:48:09 K3 BOS K3 BOS : K3 15.1 修改 分录行数 K3 BOS : K3 15.1 修改 分录行数 Private Sub m_BillInterface_AfterLoadBill() '单据加载后事件

m_BillInterface.BillEntrys(1).MaxRows = 2000 '单据最大行数扩充至2000
End Sub


Private Sub m_BillInterface_AfterNewBill() '单据新增时触发

m_BillInterface.BillEntrys(1).MaxRows = 2000 '单据最大行数扩充至2000
End Sub 郭少锋 编辑 2023-03-12 23:13:32 创建 2023-03-12 23:10:51 K3 BOS K3 BOS : K3 15.1 单据 客户端插件 K3 BOS : K3 15.1 单据 客户端插件 Option Explicit '强制声明变量 Dim sValue As String


Private WithEvents m_BillInterface As BillEvent '定义 BillEvent 接口. 必须具有的声明, 以此来获得事件


Public Sub Show(ByVal oBillInterface As Object) 'BillEvent 接口实现 注意: 此方法必须存在, 请勿修改

Set m_BillInterface = oBillInterface
End Sub

郭少锋 创建 2023-03-12 23:12:11 K3 BOS K3 BOS : k3 15.1 系统设置 K3 BOS : k3 15.1 系统设置 Select * From t_SystemProfile where FCategory = 'SafeMode' --【认证方式】

Select * From t_SystemProfile where FCategory = 'Base' --【基本设置】
/*
ServicePack 8.0.01.08 账套版本
AcctType Industry 账套类型：标准供应链解决方案
Type Industry
UUID 347B7E67-049E-4B27-8641-5CC800DC64A0

InitDataLanguage chs 语言
*/

Select * From t_SystemProfile where FCategory = 'GL' --【总账设置】
/*
StartYea 2023 启用年份
StartPeriod 1 启用月份
Closed 0 启用账套 0 未启用 1 启用 update t_SystemProfile set FValue = 0 where FCategory = 'GL' AND FKey = 'Closed' --【账套 未启用】

MaxAccountLevel 6 最大科目级数
NewVchUseNumber 0 新增凭证自动填补断号
VchlistViewItem 0 凭证查询分录科目显示核算项目

VchNumberSortByPeriod 0 凭证号按期间统一排序
VchNoViewItemClasslName 0 凭证套打不显示核算项目类别名称
VchViewItemFullName 0 凭证/明细账分级显示核算项目名称
*/


Select * From t_SystemProfile where FCategory = 'IC' --【供应链设置】
/*
ICClosed 1 业务系统是否完成初始化

ISUseMultiCheck 1 启用多级审核
ISUsePriceManage 1 启用价格管理
IsUsePurPrcMgr 1 启用采购价格管理

Item4Name 物料 存货名称
*/
郭少锋 创建 2023-03-12 22:56:49 K3 BOS K3 BOS : SQL 字段不存在，添加字段 K3 BOS : SQL 字段不存在，添加字段 SELECT name FROM syscolumns WHERE id = OBJECT_ID('t_CloudPassport') --【取得 数据表字段】

exec p_AlterTableAddColumns @TableName='t_CloudPassport',@Fields='UserId VARCHAR(50)|UserName VARCHAR(50)',@Delimeter='|' --【如果字段不存在，添加字段】


sp_helptext p_AlterTableAddColumns

/******************************************************************************
* FUNCTION NAME: p_AlterTableAddColumns *
* CREATED BY: Michael .H *
* CREATION DATE: 2005-06-15 *
* DESCRIPTION: Generate a dynamic sql to modify table structure *
* PARAMETERS: @TableName - Table name *
* @Fields - Field list *
* @Delimeter - Delimeter *
******************************************************************************/
CREATE PROCEDURE p_AlterTableAddColumns
@TableName VARCHAR(255),
@Fields VARCHAR(8000),
@Delimeter CHAR(1) = ','
AS
SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000)

IF NOT EXISTS ( SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID(@TableName)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
RETURN

IF EXISTS (SELECT 1
FROM dbo.fn_SplitStringToTable(@Fields, @Delimeter)
WHERE SUBSTRING(FValue, 1, CHARINDEX(SPACE(1), FValue)) NOT IN
(
SELECT name
FROM syscolumns
WHERE id = OBJECT_ID(@TableName)
)
)
BEGIN
SELECT @SQL = 'ALTER TABLE ' + @TableName
+ SPACE(1)
+ 'ADD'
+ SPACE(1)

SELECT @SQL = @SQL + FValue + ','
FROM dbo.fn_SplitStringToTable(@Fields, @Delimeter)
WHERE SUBSTRING(FValue, 1, CHARINDEX(SPACE(1), FValue)) NOT IN (
SELECT name
FROM syscolumns
WHERE ID = OBJECT_ID(@TableName)
)

SELECT @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1)
EXECUTE(@SQL)
END 郭少锋 编辑 2023-03-12 22:47:49 创建 2023-03-12 22:46:56 K3 BOS