"进销存"为数据表,后台统计其它表数据

1,"进销存"窗口按钮代码(填充数据)

Dim stardate As WinForm.DateTimePicker

Dim Enddate As WinForm.DateTimePicker

Dim yuyue As WinForm.DateTimePicker

stardate = e.Form.Controls("DateTimePicker1")

Enddate = e.Form.Controls("DateTimePicker2")

yuyue = e.Form.Controls("DateTimePicker3")

    DataTables("产品进销存").DataRows.Clear()

    Dim f As New Filler

    f.SourceTable = DataTables("产品设置")

    f.SourceCols = "产品状态,商品代码,类别,系列,品名,规格,型号,包装率,计量系数,计量单位,价格_成本,价格_批发,价格_零售,价格_标价,安全库存量,产品状态日志,产品图片,产品效果,产品特点简介" \'指定数据来源列

    f.DataTable = DataTables("产品进销存")

    f.DataCols = "产品状态,商品代码,类别,系列,品名,规格,型号,包装率,计量系数,计量单位,价格_成本,价格_批发,价格_零售,价格_标价,安全库存量,产品状态日志,产品图片,产品效果,产品特点简介" \'指定数据接收列

    f.ExcludeExistValue=True

    f.Fill() 

    DataTables("产品进销存").dataCols("商品代码").RaiseDataColChanged()

2,"进销存"DataColChanged代码(后台统计其它表数据)

Dim da As DataRow =e.DataRow

Select Case e.DataCol.Name

    Case "商品代码"

        DataTables("期初数据").Save()

        Dim cmd1 As New SQLCommand

        Dim dt1 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd1.CommandText = "Select SUM(计量_数量) as 期初数量,SUM(无税金额) As 期初无税金额,SUM(含税金额) As 期初含税金额  From {期初数据} Where 商品代码 =\'" & e.DataRow("商品代码") & "\'"

        dt1 = cmd1.ExecuteReader()

        DataTables("入库明细").Save()

        Dim cmd2 As New SQLCommand

        Dim dt2 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd2.CommandText = "Select SUM(计量_数量) as 入库数量,SUM(无税金额) as 入库无税金额,SUM(含税金额) as 入库含税金额  From {入库明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 入库日期 < # "& Vars("stardate") & "#"

        dt2 = cmd2.ExecuteReader()

        DataTables("出库明细").Save()

        Dim cmd3 As New SQLCommand

        Dim dt3 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd3.CommandText = "Select SUM(计量_数量) as 出库数量,SUM(成本_无税单价) As 出库无税金额,SUM(成本_含税金额) As 出库含税金额  From {出库明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 出库日期 < # "& Vars("stardate") & "#"

        dt3 = cmd3.ExecuteReader()

        For Each dr1 As DataRow In dt1.DataRows

        For Each dr2 As DataRow In dt2.DataRows

        For Each dr3 As DataRow In dt3.DataRows

            da("期初_数量")=dr1("期初数量")+dr2("入库数量")-dr3("出库数量")

            da("期初_无税金额")=dr1("期初无税金额")+dr2("入库无税金额")-dr3("出库无税金额")

            da("期初_含税金额")=dr1("期初含税金额")+dr2("入库含税金额")-dr3("出库含税金额")

        Next

        Next

        Next

        DataTables("入库明细").Save()

        Dim cmd4 As New SQLCommand

        Dim dt4 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd4.CommandText = "Select SUM(计量_数量) as 入库数量,SUM(无税金额) as 入库无税金额,SUM(含税金额) as 入库含税金额  From {入库明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 入库日期 >= # "& Vars("stardate") & "# And 入库日期 <= # " & Vars("enddate") & "#"

        dt4 = cmd4.ExecuteReader()

        For Each dr4 As DataRow In dt4.DataRows

            da("本期入库_数量")=dr4("入库数量")

            da("本期入库_无税金额")=dr4("入库无税金额")

            da("本期入库_含税金额")=dr4("入库含税金额")

        Next

        Dim Filter As String = "[商品代码] = \'" & e.DataRow("商品代码") & "\'"

        Dim drs As List(Of DataRow) = DataTables("出库明细").Select(Filter)

        For Each dr As DataRow In drs

        If dr("结帐标识")= False \'如果此行没有锁定

            DataTables("出库明细").DataCols("商品代码").RaiseDataColChanged(dr)

        End If

        Next

        DataTables("出库明细").Save()

        Dim cmd5 As New SQLCommand

        Dim dt5 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd5.CommandText = "Select SUM(计量_数量) as 出库数量,SUM(成本_无税金额) As 出库无税金额,SUM(成本_含税金额) As 出库含税金额  From {出库明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 出库日期 >= # "& Vars("stardate") & "# And 出库日期 <= # " & Vars("enddate") & "#"

        dt5 = cmd5.ExecuteReader()

        For Each dr5 As DataRow In dt5.DataRows

            da("本期出库_数量")=dr5("出库数量")

            da("本期出库_无税金额")=dr5("出库无税金额")

            da("本期出库_含税金额")=dr5("出库含税金额")

        Next

            da("本期结存_数量")=da("期初_数量")+da("本期入库_数量")-da("本期出库_数量")

            da("本期结存_无税金额")=da("期初_无税金额")+da("本期入库_无税金额")-da("本期出库_无税金额")

            da("本期结存_含税金额")=da("期初_含税金额")+da("本期入库_含税金额")-da("本期出库_含税金额")

            da("本期结存_无税单价")=da("本期结存_无税金额")/da("本期结存_数量")

            da("本期结存_含税单价")=da("本期结存_含税金额")/da("本期结存_数量")

        DataTables("销售订单明细").Save()

        Dim cmd6 As New SQLCommand

        Dim dt6 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd6.CommandText = "Select SUM(计量_数量) as 订单数量,SUM(无税金额) As 无税金额,SUM(含税金额) As 含税金额  From {销售订单明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 核销_标记 = False "

        dt6 = cmd6.ExecuteReader()

        For Each dr6 As DataRow In dt6.DataRows

            da("销售订单_数量")=dr6("订单数量")

            da("销售订单_无税金额")=dr6("无税金额")

            da("销售订单_含税金额")=dr6("含税金额")

        Next

        Dim cmd7 As New SQLCommand

        Dim dt7 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd7.CommandText = "Select SUM(计量_数量) as 订单数量,SUM(无税金额) As 无税金额,SUM(含税金额) As 含税金额  From {销售订单明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 核销_标记 = False And 预出日期 >= # "& Date.Today() & "# And 预出日期 <= # " & Vars("yuyuedata") & "#"

        dt7 = cmd7.ExecuteReader()

        For Each dr7 As DataRow In dt7.DataRows

            da("预约出库_数量")=dr7("订单数量")

            da("预约出库_无税金额")=dr7("无税金额")

            da("预约出库_含税金额")=dr7("含税金额")

        Next

        DataTables("采购明细").Save()

        Dim cmd8 As New SQLCommand

        Dim dt8 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd8.CommandText = "Select SUM(计量_数量) as 采购数量,SUM(无税金额) As 无税金额,SUM(含税金额) As 含税金额  From {采购明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 核销_标记 = False "

        dt8 = cmd8.ExecuteReader()

        For Each dr8 As DataRow In dt8.DataRows

            da("采购产品_数量")=dr8("采购数量")

            da("采购产品_无税金额")=dr8("无税金额")

            da("采购产品_含税金额")=dr8("含税金额")

        Next

            da("存销余缺_数量")=da("本期结存_数量")-da("销售订单_数量")

            da("存销余缺_无税金额")=da("本期结存_无税金额")-da("销售订单_无税金额")

            da("存销余缺_含税金额")=da("本期结存_含税金额")-da("销售订单_含税金额")

            If da("存销余缺_数量")>=0 AndAlso da("存销余缺_数量")>=da("安全库存量") Then

               da("警示_库存情况")="安全销售量,安全库存量"

            Else If da("存销余缺_数量")>=0 AndAlso da("存销余缺_数量")<da("安全库存量") Then

               da("警示_库存情况")="安全销售量,低于安全库存量"

            Else If da("存销余缺_数量")<0 AndAlso da("存销余缺_数量")>=da("安全库存量") Then

               da("警示_库存情况")="低于安全销售量,安全库存量"

            Else If da("存销余缺_数量")<0 AndAlso da("存销余缺_数量")<da("安全库存量") Then

               da("警示_库存情况")="低于安全销售量,低于安全库存量"

            End If

            If da("预约出库_数量")>0 AndAlso da("本期结存_数量")>=da("预约出库_数量") Then

               da("警示_预出情况")="安全出货量"

            Else If da("预约出库_数量")>0 AndAlso da("本期结存_数量")<da("预约出库_数量") Then

               da("警示_预出情况")="不足出货量"

            End If

        Dim cmd9 As New SQLCommand

        Dim dt9 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd9.CommandText = "Select SUM(计量_数量) as 入库数量,SUM(无税金额) as 入库无税金额,SUM(含税金额) as 入库含税金额  From {入库明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 入库日期 <= # " & Vars("enddate") & "#"

        dt9 = cmd9.ExecuteReader()

        For Each dr9 As DataRow In dt9.DataRows

            da("累计入库_数量")=dr9("入库数量")

            da("累计入库_无税金额")=dr9("入库无税金额")

            da("累计入库_含税金额")=dr9("入库含税金额")

        Next

        Dim cmd10 As New SQLCommand

        Dim dt10 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd10.CommandText = "Select SUM(计量_数量) as 出库数量,SUM(成本_无税金额) As 出库无税金额,SUM(成本_含税金额) As 出库含税金额  From {出库明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 出库日期 <= # " & Vars("enddate") & "#"

        dt10 = cmd10.ExecuteReader()

        For Each dr10 As DataRow In dt10.DataRows

            da("累计出库_数量")=dr10("出库数量")

            da("累计出库_无税金额")=dr10("出库无税金额")

            da("累计出库_含税金额")=dr10("出库含税金额")

        Next

        Dim cmd11 As New SQLCommand

        Dim dt11 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd11.CommandText = "Select SUM(计量_数量) as 订单数量,SUM(无税金额) As 无税金额,SUM(含税金额) As 含税金额  From {销售订单明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 销售订单日期 <= # " & Vars("enddate") & "#"

        dt11 = cmd11.ExecuteReader()

        For Each dr11 As DataRow In dt11.DataRows

            da("累计订单_数量")=dr11("订单数量")

            da("累计订单_无税金额")=dr11("无税金额")

            da("累计订单_含税金额")=dr11("含税金额")

        Next

        Dim cmd12 As New SQLCommand

        Dim dt12 As DataTable

        \'如果使用外部数据源需在些增加cmd.C

        cmd12.CommandText = "Select SUM(计量_数量) as 采购数量,SUM(无税金额) As 无税金额,SUM(含税金额) As 含税金额  From {采购明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 采购日期 <= # " & Vars("enddate") & "#"

        dt12 = cmd12.ExecuteReader()

        For Each dr12 As DataRow In dt12.DataRows

            da("累计采购_数量")=dr12("采购数量")

            da("累计采购_无税金额")=dr12("无税金额")

            da("累计采购_含税金额")=dr12("含税金额")

        Next

End Select

根据窗口中指定的开始日期Vars("stardate")和结束日期Vars("enddate")动态统计"进销存"数据

1、红色这一行应该去掉，因为第一行已经清除数据，不存在排除已存在数据的问题：

    DataTables("产品进销存").DataRows.Clear()

    Dim f As New Filler

    f.SourceTable = DataTables("产品设置")

    f.SourceCols = "产品状态,商品代码,类别,系列,品名,规格,型号,包装率,计量系数,计量单位,价格_成本,价格_批发,价格_零售,价格_标价,安全库存量,产品状态日志,产品图片,产品效果,产品特点简介" \'指定数据来源列

    f.DataTable = DataTables("产品进销存")

    f.DataCols = "产品状态,商品代码,类别,系列,品名,规格,型号,包装率,计量系数,计量单位,价格_成本,价格_批发,价格_零售,价格_标价,安全库存量,产品状态日志,产品图片,产品效果,产品特点简介" \'指定数据接收列

    f.ExcludeExistValue=True

    f.Fill() 

    DataTables("产品进销存").dataCols("商品代码").RaiseDataColChanged()

2、既然前面是用Filler填充，使用的是已经加载的数据，那么后面为啥用SQL语句，直接用DataTable的Compute计算就行了啊，当然如果有些参与统计的表没有加载，就只能用SQL语句了。

3、这个不影响性能， 但是看上去不舒服，就是没必要定义这么对变量，变量可以重用的：

Dim cmd As New SQLCommand
Dim dt As DataTable
cmd.CommandText = "Select SUM(计量_数量) as 入库数量,SUM(无税金额) as 入库无税金额,SUM(含税金额) as 入库含税金额  From {入库明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 入库日期 <= # " & Vars("enddate") & "#"
dt = cmd.ExecuteReader()
For Each dr As DataRow In dt.DataRows
    da("累计入库_数量")=dr("入库数量")
    da("累计入库_无税金额")=dr("入库无税金额")
    da("累计入库_含税金额")=dr("入库含税金额")
Next
cmd.CommandText = "Select SUM(计量_数量) as 出库数量,SUM(成本_无税金额) As 出库无税金额,SUM(成本_含税金额) As 出库含税金额  From {出库明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 出库日期 <= # " & Vars("enddate") & "#"
dt = cmd.ExecuteReader()
For Each dr As DataRow In dt.DataRows
    da("累计出库_数量")=dr("出库数量")
    da("累计出库_无税金额")=dr("出库无税金额")
    da("累计出库_含税金额")=dr("出库含税金额")
Next
cmd.CommandText = "Select SUM(计量_数量) as 订单数量,SUM(无税金额) As 无税金额,SUM(含税金额) As 含税金额  From {销售订单明细} Where 商品代码 =\'" & e.DataRow("商品代码") & "\' And 销售订单日期 <= # " & Vars("enddate") & "#"
dt = cmd.ExecuteReader()
For Each dr As DataRow In dt.DataRows
    da("累计订单_数量")=dr("订单数量")
    da("累计订单_无税金额")=dr("无税金额")
    da("累计订单_含税金额")=dr("含税金额")
Next

1,红色部分是该去掉

2,用Filler填充的只是"产品设置"内容,这个是已经全部加载了的,其它的表只加载了一部分,所以只能用SQL语句

3,变量设置我再优化一下

主要就是看看这个代码的性能和思路有没有问题,因为我只是用了几行数据进行了测试,不知道上万条数据执行起来怎么样?再加上如果用网络操作性能会怎么样?谢绝狐爸!

因为使用SQL统计，性能不会有啥问题的。

另外这样的代码：

For Each dr As DataRow In dt.DataRows
    da("累计出库_数量")=dr("出库数量")
    da("累计出库_无税金额")=dr("出库无税金额")
    da("累计出库_含税金额")=dr("出库含税金额")
Next

应该改为：

If dt.DataRows.Count > 0 Then

    da("累计出库_数量") = dt.DataRows(0)("出库数量")
    da("累计出库_无税金额") = dt.DataRows(0)("出库无税金额")
    da("累计出库_含税金额") = dt.DataRows(0)("出库含税金额")
End If

要不改,会对性能有影响吗?

真不好意思!上面打错字了,我本是要"谢谢狐爸"居然打成"谢谢狐爸"了...

不改，也没有影响

您这里还是打错了！