欢迎来到村长的小花园!

这里记录着ES/ERP/ESAP最佳实践之路

ESAP第四弹模糊搜索技术二(控件篇)

2013-09-04

esap中级

excel服务器 excelserver 模糊查询 ESAP SQL VBA

概述
过程演示
小结

概述

适量的使用 vba，可以打破 ES 本身的限制，实现许多强大的功能。本文将结合数据库技术，展示一种基于 myGrid 控件的模糊查询技术。阅读本文需要一定的 vba 及 sql 知识。

过程演示

首先，在数据库中创建如下存储过程，其中红色字样请换成自己的库存表及相关字段。

CREATE PROCEDURE p_arrSearch
@Str varchar(100)--可多条件如："螺丝 M6 90"
AS
BEGIN
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @SqlStr varchar(200)
SET @PointerPrev = 1
SET @SqlStr = ''
WHILE(@PointerPrev <LEN(@Str))
BEGIN
 SET @PointerCurr=CharIndex(' ',@Str,@PointerPrev)
 IF(@PointerCurr>0)
BEGIN
SET @SqlStr = @SqlStr +' and charindex('''+SUBSTRING(@Str,@PointerPrev,@PointerCurr -
@PointerPrev)+''',描述)>0'
SET @PointerPrev = @PointerCurr+1
END
 ELSE
BREAK
END
--最后一段在跳出循环后获取（）
SET @SqlStr = 'SELECT 0,物料编号,描述,批次编号,库存数,单位,0 FROM 批次库存视图 WHERE 库存数>0' + @SqlStr
+ ' and charindex(''' + SUBSTRING(@Str,@PointerPrev,LEN(@Str)-@PointerPrev+1) + ''',描述)>0'
--执行构造的 sql 语句
EXEC (@SqlStr)
END
GO

————————创建后，执行一下看看是否正常输出———————–

接下来，画出 ES 表样，选中描述字段，定义名称为“_wlms”

然后，添加两个控件:extbox1 和 myGrid1。

说明：myGrid 控件也可以是 combobox 或者 listbox 或 listview 等，但用法稍有不同。

ALT+F11 打开 VBE 在 sheet1 中加入以下代码:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Sheet1.myGrid1.Visible = False
 Sheet1.TextBox1.Visible = False
 If Application.Intersect(Target, [_wlms]) Is Nothing Then Exit Sub
 With Sheet1.TextBox1 '初始化 textbox
 .Value = Target.Value
 .Top = Target(1).Top
 .Left = Target(1).Left
 .Visible = True
 .Activate
 End With
 With Sheet1.myGrid1 '初始化 grid
 .Top = Target(1).Offset(1, 0).Top
 .Left = Target(1).Left
 End With
End Sub
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
 Dim sErr As String
 Dim rs As New ADODB.Recordset
 '非空时调用存储过程搜索物料
 If TextBox1.Text <> "" Then
 If Application.COMAddIns("ESClient10.Connect").Object.execQryproc("p_arrSearch", _
rs, sErr, Trim(TextBox1.Text)) = False Then Exit Sub
 With Sheet1.myGrid1
 .SetDatasource rs '绑定数据集
 .ColWidth(2) = 3000 '描述列宽
 .Visible = True '显示 grid
 End With
 rs.Close
 End If
 Set rs = Nothing
End Sub
Private Sub myGrid1_DblClick()
 Dim selectRow As Integer
 selectRow = Selection(1).Row
 With myGrid1
 Cells(selectRow, 4) = .Text(.Row, 2) '描述
 Cells(selectRow, 5) = .Text(.Row, 1) '编号
 Cells(selectRow, 6) = .Text(.Row, 3) '批号
 Cells(selectRow, 7) = .Text(.Row, 4) '数量
 Cells(selectRow, 8) = .Text(.Row, 5) '单位
 Cells(selectRow, 11) = .Text(.Row, 4) '库存
 .Visible = False
 End With
 TextBox1.Visible = False
End Sub

Ok,大功告成，实际效果如下。 1.填入“m6 螺丝 90”

2．双击选择后

小结

基于控件的模糊搜索技术的主要工作原理是：使用 VBA 调用存储过程进行查询，通过 Grid 控件显示查询结果，最后由用户双击选择。

注：由于 myGrid 控件为非微软官方控件，某些 EXCEL 版本打开时会有 ActiveX 控件启用提示，消除提示的方法请自行百度

阅读全文

ESAP第三弹清理工作流

2013-08-29

esap中级

excel服务器 excelserver 工作流 ESAP SQL

概述
分析演示
应用拓展
小结

概述

ES 工作流的使用在带来诸多便利的同时，也在严重地耗费着资源。本文将针对数据库中的工作流部分进行分析演示，展示一种扫描清理方案。

分析演示

下图是一个 700 表的数据库的“大头”部分占用情况（前 60%），

其中黄色标记为与工作流相关的数据表，汇总统计情况如下：

–分类–	–占比–	–区域占比–
紫色	61.09%
工作流	30.40%	49.77%
系统	27.83%	45.55%

从统计结果来看，工作流约占总空间的 1/3，在大头部分约占 50%！
在正式研究清理方案前，我们需要先认识几个 ES 系统表：
ES_RepCase：表单记录，表单的系统信息，如填报人，填报时间等。
ES_WfCase:工作流执行，工作流的执行入口。
ES_Witodo:工作流待办，工作流的待办人。
ES_WiLink:工作流衔接，节点衔接表。
ES_WorkItem:工作流汇总，所有的工作流的状态，相关人等。(核心记录)
认识系统表后定出清理方案：指定一个时间点，扫描清理该点之前已完成的所有工作流。
首先，我们从核心记录中选出符合条件的数据。

SELECT  wiid, --节点 ID    
	rcid, --表单 ID    
	piid --工作流 ID 
INTO  #ScanWi  FROM  ES_workitem 
WHERE   datediff(mm,credate,getdate())>= 6 AND state =2   --扫描工作流

上述查询生成一个 3 列的临时表#ScanWi：

其次，以临时表#ScanWi 为参照删除相关工作流信息。

DELETE  FROM ES_WITODO WHERE WIID IN (SELECT WIID FROM #ScanWi)   --删待办 
DELETE  FROM ES_WIlink WHERE WIID IN (SELECT WIID FROM #ScanWi) or PREVWIID IN (SELECT WIID FROM #ScanWi)   --删节点 
DELETE  FROM ES_Wfcase WHERE PIID IN (SELECT PIID FROM #ScanWi)   --删流程 
UPDATE ES_REPCASE SET WIID='' WHERE RCID IN (SELECT RCID FROM #ScanWi)   --恢复表单为普通 

执行效果如下：

再次，是最麻烦的部分，删除工作流快照，由于快照分布在各个数据表的附属 Wi 表中，我们需要从系统表 sysobjects 查出有哪些 Wi 表，然后遍历这些 Wi 表并删除相关数据，这将清理出一大堆空间，哇喔^_^!

IF object_id('tempdb..#ScanTmp') IS NOT NULL    
	DROP TABLE #ScanTmp 
GO 
CREATE TABLE #ScanTmp ( NAME varchar(100),rowid int identity(1,1)) --存放 WI 表名称 
INSERT INTO #ScanTmp  
	SELECT NAME FROM SYSOBJECTS WHERE RIGHT(NAME,3)='_Wi' 
GO 
DECLARE @TmpNo int --wi 模板序号 
DECLARE @MaxNo int --wi 模板数 
DECLARE @TmpName varchar(100)  --wi 模板名称 
SET @MaxNo=@@ROWCOUNT 
SET @TmpNo=1  
WHILE (@TmpNo<=@MaxNo)      --遍历 wi 快照 
BEGIN 
SET @TmpName=(SELECT NAME FROM #ScanTmp WHERE rowid=@TmpNo) --构造执行删除语句 
EXEC ('DELETE FROM  '+ @TmpName + '  WHERE EXCELSERVERWIID IN (SELECT WIID FROM #ScanWi)' ) 
SET @TmpNo=@TmpNo+1 
END 

运行效果如下：（示例中涉及 58 个 wi 表）

接下来，回过头来把 ES_WorkItem 中的记录干掉并删除临时表

DELETE  FROM ES_WorkItem WHERE WIID IN (SELECT WIID FROM #ScanWi)   --删工作流记录 
DROP TABLE #ScanWi    --删除临时表 
DROP TABLE #ScanTmp --删除临时表 

运行效果如下：

Ok,大功告成，此时我们再来分析一下数据表占用比例。

可以看出，工作流数据的占用量大幅减少。将半年前的数据清除，使总体占比降到 10%，未清除时为 30%。

细心的读者会发现 ES_WorkItem 表之前有 6W 行记录，清理后为 2W 行，比例为 1:3,与总变化比例吻合，这也印证了清理方案的周全性。

登陆工作台，查看表单记录情况。临界时间点（半年前）之后的记录进度正常。

临界时间点（半年前）之前的记录已转化为普通表单，不再有进度选项。

应用拓展

将整个过程封装为存储过程，执行 EXEC p_ClearWi 6 就可以删除 6 个月前的工作流了。

CREATE PROC p_ClearWi 
@SMonth INT 
AS 
BEGIN 
DECLARE @TmpNo int 
DECLARE @MaxNo int 
DECLARE @TmpName varchar(100) 
IF object_id('tempdb..#ScanWi') IS NOT NULL    
DROP TABLE #ScanWi --扫描工作流放入临时表，state=2 表示已完成 
SELECT  wiid, --节点 ID   
	rcid, --表单 ID   
	piid --工作流 ID 
INTO  #ScanWi  
FROM  ES_workitem 
WHERE   datediff(mm,credate,getdate())>= @SMonth AND state =2     
DELETE  
FROM ES_WITODO WHERE WIID IN (SELECT WIID FROM #ScanWi)   --删待办  
DELETE  
FROM ES_WIlink WHERE WIID IN (SELECT WIID FROM #ScanWi) OR PREVWIID IN (SELECT WIID FROM #ScanWi)   --删节点  
DELETE  FROM ES_Wfcase WHERE PIID IN (SELECT PIID FROM #ScanWi)   --删流程  
UPDATE ES_REPCASE SET WIID='' WHERE RCID IN (SELECT RCID FROM #ScanWi)   --恢复表单为普通  
IF object_id('tempdb..#ScanTmp') IS NOT NULL    
	DROP TABLE #ScanTmp 
CREATE TABLE #ScanTmp ( NAME varchar(100),rowid int identity(1,1)) --存放 WI 表名称  
INSERT INTO #ScanTmp  
	SELECT NAME FROM SYSOBJECTS WHERE RIGHT(NAME,3)='_Wi'  
SET @MaxNo=@@ROWCOUNT 
SET @TmpNo=1 
WHILE (@TmpNo<=@MaxNo)      --遍历 wi 快照，构造删除语句并执行 
BEGIN 
SET @TmpName=(SELECT NAME FROM #ScanTmp WHERE rowid=@TmpNo) 
EXEC ('DELETE FROM  '+ @TmpName + '  WHERE EXCELSERVERWIID IN (SELECT WIID FROM #ScanWi)' )  
SET @TmpNo=@TmpNo+1 
END  
DELETE  
FROM ES_WorkItem WHERE WIID IN (SELECT WIID FROM #ScanWi)   --删流程  
DROP TABLE #ScanWi    --删除临时表
DROP TABLE #ScanTmp  --删除临时表  
END 
GO 

小结

本文的工作流清理技术与工作流扫描恢复技术，解决了 ES 系统中关于工作流应用的两大顽疾，也使企业在使用工作流时不用再畏手畏脚。本文的示例系统为 9.4，其他版本请自行测试。

注：本文技术仅供借鉴，使用本技术需要一定的数据库知识，尝试本技术所导致的任何后果与本文作者无关。

阅读全文

ESAP第二弹数据映射

2013-08-13

esap中级

聚表慧表 excel服务器 excelserver 映射 ESAP

ES纯查询模板也需要定义数据表，然后才能定义和使用表间公式。

通常情况下，纯查询模板的数据表并不存储数据，却又真实的存储在数据库中，以至于我们翻阅数据库时满眼都是各种“XXX查询_主表，XXX查询_明细”。使用映射技术除了可以把一些性质相同的模板数据存储在一起，还能将查询模板的数据表化为“虚无”。

本文将针对数据映射技术进行案例演示，全面展示一种突破常规的设计方案。

pdf下载

阅读全文
ESAP第一弹模糊查询技术一（形如篇）

2013-08-08

esap中级

聚表慧表 excel服务器 excelserver 模糊查询 ESAP

ES制作查询模板所使用的一个重要的运算符就是“形如”。例如条件：A 形如 B，意思是A 包含 B。

上述语句的等价SQL语句为：where A like ‘%B%’，基于这个事实，我们可以充分利用like运算符支持通配符的特性实现很多高级查询功能。

本文将针对使用“形如”的查询进行分析演示，并给出一些多条件查找的快速解决办法。

pdf下载

阅读全文
ESAP前传 ES工作流扫描恢复

2013-07-27

esap中级

excel服务器 excelserver 工作流 SQL
概述

ES工作流是其能成为OA的一个重要因素，但是工作流要慎用，尤其是表单带有大量明细（假设平均N节点），会导致该模板的附属WI表成N倍体积增大。

ES工作流另一个不利于应用的因素是：在办理时如果未正常关闭会出现卡在进行中待办的现象，此问题不易发现，若出现在订单类模板上，可能造成交期延误进而造成重大损失。

本文将针对工作流卡在进行中待办的问题进行探讨并提供一种解决办法。

问题呈现
- 新建任意含工作流的表单-暂存
- 在待办中双击办理
- 强制结束任务
4.重新登录时会发现待办中无项目

5.工作流卡在“进行汇总待办”中，必须右键恢复

解决办法
- 在数据库中定义视图（扫描进行中待办）
```
CREATE VIEW dbo.v_WiCKOut
AS
SELECT A1.wiId AS 工作流序号,
	A1.CreByName AS 创建人, 
	A1.State AS 工作流状态, 
	A1.CheckOutByName AS 进行人, 
	A1.ComByName AS 暂存人,
	A1.state1 AS 暂存状态,
	A3.pName AS 描述, 
	A4.IP, A1.CreDate AS 创建时间
FROM dbo.ES_WorkItem A1 INNER JOIN
      dbo.ES_WfCase A2 ON A1.piId = A2.piId INNER JOIN
      dbo.ES_WorkFlow A3 ON A2.pId = A3.pId LEFT OUTER JOIN
      ESSystem.dbo.ES_SesLog A4 ON A1.CheckOutBy = A4.UserId
WHERE (A1.State = 1)  --1表示进行中，0表示待办，2表示完成
```
————–执行效果————
- 在管理控制台中注册该视图
- 创建模板定义提数公式
- 定义回写公式保存时恢复工作流状态（为确保安全仅恢复不在线用户的进行中待办）
- 设置定时自动任务自动扫描
小结

通过该办法可以有效控制进行中待办不被一直卡住，当然，示例中的视图也可以定义为存储过程，由SQL代理作业定时自动完成。

村长补记：自动任务偶尔会挂掉，SQL代理作业设为自动启动后可以作为最后一道防线！
阅读全文