专家坐堂-重置不计算Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  重置不计算  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=119502)

--  作者：铜豌豆之首
--  发布时间：2018/5/25 21:24:00
--  重置不计算
删除数据后重置无效，手动重置也无效
Select Case e.DataCol.name
Case "日期"
Dim r As Date = e.DataRow("日期")
e.DataRow("广告费") = DataTables("百度消费").Compute("Sum(消费)", Filter) / DataTables("客服").Compute("count(姓名)", Filter)
Dim Total As Single
Total = DataTables("客服").Compute("Min(广告费)",filter)
DataTables("客服").ReplaceFor("广告费", Total, filter)

e.DataRow("点击") = DataTables("百度消费").Compute("Sum(点击)", Filter) / DataTables("客服").Compute("count(姓名)", Filter)
Dim Total2 As Single
Total2 = DataTables("客服").Compute("Min(点击)",filter)
DataTables("客服").ReplaceFor("点击", Total2, filter)
End Select

以下也是不自动计算需要手动重置
Select Case e.DataCol.Name
Case "开始时间"
Dim str As String = e.DataRow("开始时间")
Dim mc = System.Text.RegularExpressions.Regex.Matches(str,"\\d{4}-\\d{1,2}-\\d{1,2}")
Dim mc1 = System.Text.RegularExpressions.Regex.Matches(str,"\\d+?(?=:)")
If e.DataRow.IsNull("开始时间") Then
e.DataRow("时") = Nothing
e.DataRow("日期") = Nothing
Else
e.DataRow("日期") = mc(0).value
e.DataRow("时") = mc1(0).value
End If
End Select

[此贴子已经被作者于2018/5/25 21:29:34编辑过]

--  作者：有点蓝
--  发布时间：2018/5/25 21:39:00
--
贴出这个事件完整代码

--  作者：铜豌豆之首
--  发布时间：2018/5/25 21:43:00
--

Dim idx As Integer = Tables("客服").FindRow(e.DataRow)
If idx = Tables("客服").rows.count-1 Then
DataTables("客服").addnew
End If
Select Case e.DataCol.Name
Case "日期"
If e.DataRow.IsNull("日期") = False Then
Dim d As Date = e.NewValue
e.DataRow("年") = d.Year
e.DataRow("月") = d.Month
e.DataRow("周") = System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(d, Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Monday).ToString
e.DataRow("星期") = d.DayOfWeek
e.DataRow("季度") = (d.Month-1) \\ 3 + 1
End If
End Select
Select Case e.DataCol.name
Case "日期"
Dim r As Date = e.DataRow("日期")
Dim r1 As String = e.DataRow("姓名")
Dim Filter As String = "[日期] = \'" & r & "\'"
Dim Filter0 As String = "[日期] = \'" & r & "\' And [姓名] = \'" & r1 & "\'"
Dim Filter1 As String = "[订单日期] = \'" & r & "\' And [姓名] = \'" & r1 & "\'"
Dim Filter2 As String = "[过来日期] = \'" & r & "\' And [姓名] = \'" & r1 & "\'"
Dim Filter3 As String = "[访客日期] = \'" & r & "\' And [姓名] = \'" & r1 & "\'"
Dim num As Byte = DataTables("客服").Compute("count(姓名)", Filter)

e.DataRow("广告费") = DataTables("百度消费").Compute("Sum(消费)", Filter) / DataTables("客服").Compute("count(姓名)", Filter)
Dim Total As Single
Total = DataTables("客服").Compute("Min(广告费)",filter)
DataTables("客服").ReplaceFor("广告费", Total, filter)

e.DataRow("点击") = DataTables("百度消费").Compute("Sum(点击)", Filter) / DataTables("客服").Compute("count(姓名)", Filter)
Dim Total2 As Single
Total2 = DataTables("客服").Compute("Min(点击)",filter)
DataTables("客服").ReplaceFor("点击", Total2, filter)

e.DataRow("四00Y") = DataTables("访客日期").Compute("sum(四00)", Filter3)
e.DataRow("离线宝Y") = DataTables("访客日期").Compute("sum(离线宝)", Filter3)
e.DataRow("商务通Y") = DataTables("访客日期").Compute("sum(商务通)", Filter3)
e.DataRow("咨询数") = DataTables("商务通参与接待").Compute("Sum(合计)", Filter0)
e.DataRow("邀请") = DataTables("商务通参与邀请").Compute("Sum(邀请客人数)", Filter0)
e.DataRow("有效信息") = DataTables("商务通有效").Compute("Sum(数量)", Filter0)
e.DataRow("D订单") = DataTables("易途订单").Compute("Sum(单数)", Filter1)
e.DataRow("D大人") = DataTables("易途订单").Compute("Sum(大人)", Filter1)
e.DataRow("D小孩") = DataTables("易途订单").Compute("Sum(小孩)", Filter1)
e.DataRow("D毛利") = DataTables("易途订单").Compute("Sum(预算利润t)", Filter1)
e.DataRow("D商务通百度") = DataTables("易途订单").Compute("Sum(商务通百度)", Filter1)
e.DataRow("D离线宝") = DataTables("易途订单").Compute("Sum(离线宝)", Filter1)
e.DataRow("D电话400") = DataTables("易途订单").Compute("Sum(电话400)", Filter1)
e.DataRow("G订单") = DataTables("易途过来").Compute("Sum(单数)", Filter2)
e.DataRow("G大人") = DataTables("易途过来").Compute("Sum(大人)", Filter2)
e.DataRow("G小孩") = DataTables("易途过来").Compute("Sum(小孩)", Filter2)
e.DataRow("G毛利") = DataTables("易途过来").Compute("Sum(预算利润t)", Filter2)
e.DataRow("G商务通百度") = DataTables("易途过来").Compute("Sum(商务通百度)", Filter2)
e.DataRow("G离线宝") = DataTables("易途过来").Compute("Sum(离线宝)", Filter2)
e.DataRow("G电话400") = DataTables("易途过来").Compute("Sum(电话400)", Filter2)
End Select
问题2
Dim idx As Integer = Tables("离线宝").FindRow(e.DataRow)
If idx = Tables("离线宝").rows.count-1 Then
DataTables("离线宝").addnew
End If

Select Case e.DataCol.Name
Case "开始时间"
Dim str As String = e.DataRow("开始时间")
Dim mc = System.Text.RegularExpressions.Regex.Matches(str,"\\d{4}-\\d{1,2}-\\d{1,2}")
Dim mc1 = System.Text.RegularExpressions.Regex.Matches(str,"\\d+?(?=:)")
If e.DataRow.IsNull("开始时间") Then
e.DataRow("时") = Nothing
e.DataRow("日期") = Nothing
Else
e.DataRow("日期") = mc(0).value
e.DataRow("时") = mc1(0).value
End If
End Select
Select Case e.DataCol.Name
Case "开始时间"
Dim dr As DataRow = e.DataRow
Dim filter1 As String
Dim drs As List(Of DataRow)
filter1 = "[主叫号码] = \'" & dr("主叫号码") & "\' And [日期] = #" & dr("日期") & "#"
drs = DataTables("离线宝").Select(filter1)
dr("数量") = 1/drs.Count
Dim Total As Single
Total = DataTables("离线宝").Compute("Min(数量)",filter1 )
DataTables("离线宝").ReplaceFor("数量", Total, filter1)
End Select

--  作者：有点蓝
--  发布时间：2018/5/25 22:09:00
--

Dim idx As Integer = Tables("客服").FindRow(e.DataRow)
If idx = Tables("客服").rows.count-1 Then
DataTables("客服").addnew
End If
Select Case e.DataCol.Name
Case "日期"
If e.DataRow.IsNull("日期") = False Then
Dim d As Date = e.NewValue
e.DataRow("年") = d.Year
e.DataRow("月") = d.Month
e.DataRow("周") = System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(d, Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Monday).ToString
e.DataRow("星期") = d.DayOfWeek
e.DataRow("季度") = (d.Month-1) \\ 3 + 1
Dim Filter As String = "[日期] = \'" & r & "\'"
Dim num As Byte = DataTables("客服").Compute("count(姓名)", Filter)

e.DataRow("广告费") = DataTables("百度消费").Compute("Sum(消费)", Filter) / DataTables("客服").Compute("count(姓名)", Filter)
Dim Total As Single
Total = DataTables("客服").Compute("Min(广告费)",filter)
DataTables("客服").ReplaceFor("广告费", Total, filter)

e.DataRow("点击") = DataTables("百度消费").Compute("Sum(点击)", Filter) / DataTables("客服").Compute("count(姓名)", Filter)
Dim Total2 As Single
Total2 = DataTables("客服").Compute("Min(点击)",filter)
DataTables("客服").ReplaceFor("点击", Total2, filter)
End If
End Select
Select Case e.DataCol.name
Case "日期","姓名"
Dim r As Date = e.DataRow("日期")
Dim r1 As String = e.DataRow("姓名")
Dim Filter0 As String = "[日期] = \'" & r & "\' And [姓名] = \'" & r1 & "\'"
Dim Filter1 As String = "[订单日期] = \'" & r & "\' And [姓名] = \'" & r1 & "\'"
Dim Filter2 As String = "[过来日期] = \'" & r & "\' And [姓名] = \'" & r1 & "\'"
Dim Filter3 As String = "[访客日期] = \'" & r & "\' And [姓名] = \'" & r1 & "\'"


e.DataRow("四00Y") = DataTables("访客日期").Compute("sum(四00)", Filter3)
e.DataRow("离线宝Y") = DataTables("访客日期").Compute("sum(离线宝)", Filter3)
e.DataRow("商务通Y") = DataTables("访客日期").Compute("sum(商务通)", Filter3)
e.DataRow("咨询数") = DataTables("商务通参与接待").Compute("Sum(合计)", Filter0)
e.DataRow("邀请") = DataTables("商务通参与邀请").Compute("Sum(邀请客人数)", Filter0)
e.DataRow("有效信息") = DataTables("商务通有效").Compute("Sum(数量)", Filter0)
e.DataRow("D订单") = DataTables("易途订单").Compute("Sum(单数)", Filter1)
e.DataRow("D大人") = DataTables("易途订单").Compute("Sum(大人)", Filter1)
e.DataRow("D小孩") = DataTables("易途订单").Compute("Sum(小孩)", Filter1)
e.DataRow("D毛利") = DataTables("易途订单").Compute("Sum(预算利润t)", Filter1)
e.DataRow("D商务通百度") = DataTables("易途订单").Compute("Sum(商务通百度)", Filter1)
e.DataRow("D离线宝") = DataTables("易途订单").Compute("Sum(离线宝)", Filter1)
e.DataRow("D电话400") = DataTables("易途订单").Compute("Sum(电话400)", Filter1)
e.DataRow("G订单") = DataTables("易途过来").Compute("Sum(单数)", Filter2)
e.DataRow("G大人") = DataTables("易途过来").Compute("Sum(大人)", Filter2)
e.DataRow("G小孩") = DataTables("易途过来").Compute("Sum(小孩)", Filter2)
e.DataRow("G毛利") = DataTables("易途过来").Compute("Sum(预算利润t)", Filter2)
e.DataRow("G商务通百度") = DataTables("易途过来").Compute("Sum(商务通百度)", Filter2)
e.DataRow("G离线宝") = DataTables("易途过来").Compute("Sum(离线宝)", Filter2)
e.DataRow("G电话400") = DataTables("易途过来").Compute("Sum(电话400)", Filter2)
End Select
问题2
Dim idx As Integer = Tables("离线宝").FindRow(e.DataRow)
If idx = Tables("离线宝").rows.count-1 Then
DataTables("离线宝").addnew
End If

Select Case e.DataCol.Name
Case "开始时间"
Dim str As String = e.DataRow("开始时间")
Dim mc = System.Text.RegularExpressions.Regex.Matches(str,"\\d{4}-\\d{1,2}-\\d{1,2}")
Dim mc1 = System.Text.RegularExpressions.Regex.Matches(str,"\\d+?(?=:)")
If e.DataRow.IsNull("开始时间") Then
e.DataRow("时") = Nothing
e.DataRow("日期") = Nothing
Else
e.DataRow("日期") = mc(0).value
e.DataRow("时") = mc1(0).value
End If
End Select
Select Case e.DataCol.Name
Case "日期","主叫号码"
Dim dr As DataRow = e.DataRow
Dim filter1 As String
Dim drs As List(Of DataRow)
filter1 = "[主叫号码] = \'" & dr("主叫号码") & "\' And [日期] = #" & dr("日期") & "#"
drs = DataTables("离线宝").Select(filter1)
dr("数量") = 1/drs.Count
Dim Total As Single
Total = DataTables("离线宝").Compute("Min(数量)",filter1 )
DataTables("离线宝").ReplaceFor("数量", Total, filter1)
End Select

[此贴子已经被作者于2018/5/25 22:30:07编辑过]

--  作者：铜豌豆之首
--  发布时间：2018/5/25 22:28:00
--
老师你修改了什么地方能否给我标注一下，谢谢

--  作者：有点蓝
--  发布时间：2018/5/25 22:31:00
--
看4楼

--  作者：铜豌豆之首
--  发布时间：2018/5/25 22:32:00
--
谢谢