ckStr = dcck.Text &","& drck.Text
SqlKcb ="[t_im_barcode_stock].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[stock_qty]) As [数量],CONVERT(INT,[sale_price]) As [吊牌价],[branch_name] As [仓库]From [t_im_barcode_stock] Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = [t_im_barcode_stock].[branch_no]Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_im_barcode_stock].[item_no]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right([t_im_barcode_stock].[item_barcode],5),1,3) Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right([t_im_barcode_stock].[item_barcode],2)Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info].[item_clsno] Where [t_im_barcode_stock].[branch_no] not in (000101,000801,999801,999801,999901)and [stock_qty]<>0 and [item_brand]='01' and [t_im_barcode_stock].[item_no] Like '"& kt.Text &"%'and [item_name] Like '"& pl.Text &"%'"
SqlWqs =" [t_im_sheet_barcode_qty].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[qty]) As [数量],CONVERT(INT,[sale_price]) As [吊牌价],[branch_name] As [仓库] From [EFSG].[dbo].[t_im_sheet_barcode_qty]Left JOIN [t_im_sheet_master] ON [t_im_sheet_master].[sheet_no] = [t_im_sheet_barcode_qty].[sheet_no]Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = REPLACE([t_im_sheet_master].[d_branch_no]+'01',' ','') Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_im_sheet_barcode_qty] .[item_no]Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info].[item_clsno]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right([EFSG].[dbo].[t_im_sheet_barcode_qty].[item_barcode],5),1,3)Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right([t_im_sheet_barcode_qty].[item_barcode],2) Where [t_im_sheet_master].trans_no='DO' and approve_flag = '1'and order_status = '0' and [t_im_sheet_barcode_qty].[item_no] Like '"& kt.Text &"%'and [item_name] Like '"& pl.Text &"%'"
SqlKcb = SqlKcb & "and [branch_name] Like '%"& dcck.Text &"%'"
SqlWqs = SqlWqs & "and [branch_name] Like '%"& dcck.Text &"%'"
Dim bd1 As New GroupTableBuilder("库存表",SqlKcb,"EFSG")
Dim dt1 As fxDataSource
bd1.Groups.AddDef("款号") '根据型号分组
bd1.Groups.AddDef("品类")
bd1.Groups.AddDef("品名")
bd1.Groups.AddDef("季节")
bd1.Groups.AddDef("花色")
bd1.Groups.AddDef("尺码")
bd1.Groups.AddDef("吊牌价")
bd1.Groups.AddDef("仓库")
bd1.Totals.AddDef("数量","现有库存_数量")
dt1 = bd1.BuildDataSource()
Dim bd2 As New GroupTableBuilder("未签收",SqlWqs,"EFSG")
Dim dt2 As fxDataSource
bd2.Groups.AddDef("款号") '根据型号分组
bd2.Groups.AddDef("品类")
bd2.Groups.AddDef("品名")
bd2.Groups.AddDef("季节")
bd2.Groups.AddDef("花色")
bd2.Groups.AddDef("尺码")
bd2.Groups.AddDef("吊牌价")
bd2.Groups.AddDef("仓库")
bd2.Totals.AddDef("数量","未签收_数量")
dt2 = bd2.BuildDataSource()
Dim nms As String() = {"款号","花色","尺码","季节","仓库","品类","品名","吊牌价"} '指定连接列
dt1.Combine(nms,dt2,nms) '将销售统计数据组合到进货统计数据
Tables("动态调货_动态调货").DataSource =dt1
With DataTables("动态调货_动态调货").DataCols '用表达式列计算库存数据
.Add("总库存数量",Gettype(Integer), "IsNull([现有库存_数量],0)+IsNull([未签收_数量],0)")
.Add("最后上货天数",Gettype(Integer),"")
.Add("调出仓库_进",Gettype(Integer),"")
.Add("调出仓库_销",Gettype(Integer),"")
.Add("调出仓库_存",Gettype(Integer),"")
.Add("调入仓库_进",Gettype(Integer),"")
.Add("调入仓库_销",Gettype(Integer),"")
.Add("调入仓库_存",Gettype(Integer),"")
.Add("备注",Gettype(String),100)
End With
Dim SqlZdckd As String
Dim SqlZdrkd As String
Dim SqlCgshd As String
Dim SqlCgthd As String
Dim SqlXsmx As String
Dim SqlKctzr As String
Dim SqlKctzc As String
Dim SqlPdcyd As String
SqlZdckd ="[t_im_sheet_barcode_qty].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[qty]) As [数量],CONVERT(INT,[sale_price]) As [吊牌价],[branch_name] As [仓库] From [EFSG].[dbo].[t_im_sheet_barcode_qty]Left JOIN [t_im_sheet_master] ON [t_im_sheet_master].[sheet_no] = [t_im_sheet_barcode_qty].[sheet_no]Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = [t_im_sheet_master].[branch_no] Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_im_sheet_barcode_qty] .[item_no]Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info].[item_clsno]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right([EFSG].[dbo].[t_im_sheet_barcode_qty].[item_barcode],5),1,3)Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right([t_im_sheet_barcode_qty].[item_barcode],2) Where [t_im_sheet_master].trans_no='DO' and approve_flag = '1'and [item_brand]='01'"
SqlZdrkd ="[t_im_sheet_barcode_qty].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[qty]) As [数量],CONVERT(INT,[sale_price]) As [吊牌价],[branch_name] As [仓库] From [EFSG].[dbo].[t_im_sheet_barcode_qty]Left JOIN [t_im_sheet_master] ON [t_im_sheet_master].[sheet_no] = [t_im_sheet_barcode_qty].[sheet_no]Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = REPLACE([t_im_sheet_master].[d_branch_no]+'01',' ','') Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_im_sheet_barcode_qty] .[item_no]Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info].[item_clsno]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right([EFSG].[dbo].[t_im_sheet_barcode_qty].[item_barcode],5),1,3)Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right([t_im_sheet_barcode_qty].[item_barcode],2) Where [t_im_sheet_master].trans_no='DO' and approve_flag = '1'and [item_brand]='01'"
SqlCgshd ="[t_im_sheet_barcode_qty].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[qty]) As [数量],CONVERT(INT,[sale_price]) As [吊牌价],[branch_name] As [仓库]From [t_im_sheet_barcode_qty]Left JOIN [t_pm_sheet_master] ON [t_pm_sheet_master].[sheet_no] = [t_im_sheet_barcode_qty].[sheet_no] Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = [t_pm_sheet_master].[branch_no]Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_im_sheet_barcode_qty} .[item_no]Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info}.[item_clsno]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right([t_im_sheet_barcode_qty].[item_barcode],5),1,3) Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right([t_im_sheet_barcode_qty].[item_barcode],2) Where [t_im_sheet_barcode_qty].[trans_no]='PI' and [item_brand]='01'"
SqlCgthd ="[t_im_sheet_barcode_qty].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[qty]) As [数量],CONVERT(INT,[sale_price]) As [吊牌价],[branch_name] As [仓库]From [t_im_sheet_barcode_qty]Left JOIN [t_pm_sheet_master] ON [t_pm_sheet_master].[sheet_no] = [t_im_sheet_barcode_qty].[sheet_no] Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = [t_pm_sheet_master].[branch_no]Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_im_sheet_barcode_qty} .[item_no]Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info}.[item_clsno]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right([t_im_sheet_barcode_qty].[item_barcode],5),1,3) Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right([t_im_sheet_barcode_qty].[item_barcode],2) Where [t_im_sheet_barcode_qty].[trans_no]='RO' and [item_brand]='01'"
SqlKctzr = "[t_im_sheet_barcode_qty].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[qty]) As [数量],CONVERT(INT,[sale_price]) As [吊牌价],[branch_name] As [仓库] From [t_im_sheet_barcode_qty] Left JOIN [t_im_sheet_master] ON [t_im_sheet_master].[sheet_no] = [t_im_sheet_barcode_qty}.[sheet_no]Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = [t_im_sheet_master}.[branch_no]Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_im_sheet_barcode_qty}.[item_no]Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info}.[item_clsno]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right({t_im_sheet_barcode_qty}.[item_barcode],5),1,3) Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right({t_im_sheet_barcode_qty}.[item_barcode],2) Where [t_im_sheet_barcode_qty].[trans_no]='oo'and [db_no]='+' and [approve_flag]='1' and [item_brand]='01'"
SqlKctzc = "[t_im_sheet_barcode_qty].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[qty]) As [数量],CONVERT(INT,[sale_price]) As [吊牌价],[branch_name] As [仓库] From [t_im_sheet_barcode_qty] Left JOIN [t_im_sheet_master] ON [t_im_sheet_master].[sheet_no] = [t_im_sheet_barcode_qty}.[sheet_no]Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = [t_im_sheet_master}.[branch_no]Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_im_sheet_barcode_qty}.[item_no]Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info}.[item_clsno]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right({t_im_sheet_barcode_qty}.[item_barcode],5),1,3) Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right({t_im_sheet_barcode_qty}.[item_barcode],2) Where [t_im_sheet_barcode_qty].[trans_no]='oo'and [db_no]='-' and [approve_flag]='1' and [item_brand]='01'"
SqlPdcyd ="[t_im_check_sum].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[balance_qty]) As [数量],CONVERT(INT,[t_im_check_sum].sale_price) As [吊牌价],[branch_name] As [仓库] From [t_im_check_sum] Left JOIN [t_im_check_init] ON [t_im_check_init].[sheet_no] = [t_im_check_sum].[sheet_no] Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = [t_im_check_sum].[branch_no]Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_im_check_sum].[item_no]Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info].[item_clsno]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right([t_im_check_sum].[item_barcode],5),1,3) Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right([t_im_check_sum].[item_barcode],2) where [balance_qty]<>'0' and [approve_flag]='1'and [item_brand]='01'"
SqlXsmx = "[t_rm_saleflow].[item_no] As [款号],[item_name] As [品类],[item_subname] As [品名],[item_clsname] As [季节],[color_name] As [花色],[size_name] As [尺码],CONVERT(INT,[sale_qnty]) As [数量],CONVERT(INT,[t_bd_item_info].[sale_price]) As [吊牌价],[sell_way] As [销售类型码],[branch_name] As [仓库] From [t_rm_saleflow] Left JOIN [t_bd_branch_info] ON [t_bd_branch_info].[branch_no] = [t_rm_saleflow].[branch_no]Left JOIN [t_bd_item_info] ON [t_bd_item_info].[item_no] = [t_rm_saleflow].[item_no]Left JOIN [t_bd_item_cls] ON [t_bd_item_cls].[item_clsno] = [t_bd_item_info].[item_clsno]Left JOIN [t_bd_color_code] ON [t_bd_color_code].[color_id] = substring(right([t_rm_saleflow].[item_barcode],5),1,3) Left JOIN [t_bd_size_code] ON [t_bd_size_code].[size_id] = right([t_rm_saleflow].[item_barcode],2) where [item_brand]='01'"
SqlZdckd = SqlZdckd & "and [t_im_sheet_barcode_qty].[item_no] like '" & kt.Value & "%'And [branch_name] in ('" & ckStr.replace(",", "','") & "')"
SqlZdrkd = SqlZdrkd & "and [t_im_sheet_barcode_qty].[item_no] like '" & kt.Value & "%'And [branch_name] in ('" & ckStr.replace(",", "','") & "')"
SqlCgshd = SqlCgshd & "and [t_im_sheet_barcode_qty].[item_no] like '" & kt.Value & "%'And [branch_name] in ('" & ckStr.replace(",", "','") & "')"
SqlCgthd = SqlCgthd & "and [t_im_sheet_barcode_qty].[item_no] like '" & kt.Value & "%'And [branch_name] in ('" & ckStr.replace(",", "','") & "')"
SqlKctzr = SqlKctzr & "and [t_im_sheet_barcode_qty].[item_no] like '" & kt.Value & "%'And [branch_name] in ('" & ckStr.replace(",", "','") & "')"
SqlKctzc = SqlKctzc & "and [t_im_sheet_barcode_qty].[item_no] like '" & kt.Value & "%'And [branch_name] in ('" & ckStr.replace(",", "','") & "')"
SqlPdcyd = SqlPdcyd & "and [t_im_check_sum].[item_no] like '" & kt.Value & "%'And [branch_name] in ('" & ckStr.replace(",", "','") & "')"
SqlXsmx = SqlXsmx & "and [t_rm_saleflow].[item_no] like '" & kt.Value & "%'And [branch_name] in ('" & ckStr.replace(",", "','") & "')"
Dim b1 As New GroupTableBuilder("直调出库",SqlZdckd,"EFSG")
Dim d1 As fxDataSource
b1.Groups.AddDef("款号") '根据型号分组
b1.Groups.AddDef("花色")
b1.Groups.AddDef("尺码")
b1.Groups.AddDef("仓库")
b1.Totals.AddDef("数量","直调出库单_数量")
d1 = b1.BuildDataSource()
Dim b2 As New GroupTableBuilder("直调入库",SqlZdrkd,"EFSG")
Dim d2 As fxDataSource
b2.Groups.AddDef("款号") '根据型号分组
b2.Groups.AddDef("花色")
b2.Groups.AddDef("尺码")
b2.Groups.AddDef("仓库")
b2.Totals.AddDef("数量","直调入库单_数量")
d2 = b2.BuildDataSource()
Dim b3 As New GroupTableBuilder("采购收货",SqlCgshd,"EFSG")
Dim d3 As fxDataSource
b3.Groups.AddDef("款号") '根据型号分组
b3.Groups.AddDef("花色")
b3.Groups.AddDef("尺码")
b3.Groups.AddDef("仓库")
b3.Totals.AddDef("数量","采购收货_数量")
d3 = b3.BuildDataSource()
Dim b4 As New GroupTableBuilder("采购退货",SqlCgthd,"EFSG")
Dim d4 As fxDataSource
b4.Groups.AddDef("款号") '根据型号分组
b4.Groups.AddDef("花色")
b4.Groups.AddDef("尺码")
b4.Groups.AddDef("仓库")
b4.Totals.AddDef("数量","采购退货_数量")
d4 = b4.BuildDataSource()
Dim b5 As New GroupTableBuilder("销售",SqlXsmx,"EFSG")
Dim d5 As fxDataSource
b5.Filter="销售类型码<>'B'"
b5.Groups.AddDef("款号") '根据型号分组
b5.Groups.AddDef("花色")
b5.Groups.AddDef("尺码")
b5.Groups.AddDef("仓库")
b5.Totals.AddDef("数量","销售_数量")
d5 = b5.BuildDataSource()
Dim b6 As New GroupTableBuilder("退货",SqlXsmx,"EFSG")
Dim d6 As fxDataSource
b6.Filter="销售类型码='B'"
b6.Groups.AddDef("款号") '根据型号分组
b6.Groups.AddDef("花色")
b6.Groups.AddDef("尺码")
b6.Groups.AddDef("仓库")
b6.Totals.AddDef("数量","退货_数量")
d6 = b6.BuildDataSource()
Dim b7 As New GroupTableBuilder("库存调整入",SqlKctzr,"EFSG")
Dim d7 As fxDataSource
b7.Groups.AddDef("款号") '根据型号分组
b7.Groups.AddDef("花色")
b7.Groups.AddDef("尺码")
b7.Groups.AddDef("仓库")
b7.Totals.AddDef("数量","库存调整入_数量")
d7 = b7.BuildDataSource()
Dim b8 As New GroupTableBuilder("库存调整出",SqlKctzc,"EFSG")
Dim d8 As fxDataSource
b8.Groups.AddDef("款号") '根据型号分组
b8.Groups.AddDef("花色")
b8.Groups.AddDef("尺码")
b8.Groups.AddDef("仓库")
b8.Totals.AddDef("数量","库存调整出_数量")
d8 = b8.BuildDataSource()
Dim b9 As New GroupTableBuilder("盘点差异",SqlPdcyd,"EFSG")
Dim d9 As fxDataSource
b9.Groups.AddDef("款号") '根据型号分组
b9.Groups.AddDef("花色")
b9.Groups.AddDef("尺码")
b9.Groups.AddDef("仓库")
b9.Totals.AddDef("数量","盘点差异_数量")
d9 = b9.BuildDataSource()
Dim nm As String() = {"款号","花色","尺码","仓库"} '指定连接列
d1.Combine(nm,d2,nm) '将销售统计数据组合到进货统计数据
d1.Combine(nm,d3,nm) '将销售统计数据组合到进货统计数据
d1.Combine(nm,d4,nm) '将销售统计数据组合到进货统计数据
d1.Combine(nm,d5,nm) '将销售统计数据组合到进货统计数据
d1.Combine(nm,d6,nm) '将销售统计数据组合到进货统计数据
d1.Combine(nm,d7,nm) '将销售统计数据组合到进货统计数据
d1.Combine(nm,d8,nm) '将销售统计数据组合到进货统计数据
d1.Combine(nm,d9,nm) '将销售统计数据组合到进货统计数据
Tables("动态调货_出入库明细").DataSource =d1
With DataTables("动态调货_出入库明细").DataCols '用表达式列计算库存数据
.Add("实入数量",Gettype(Integer), "IsNull([库存调整入_数量],0)+IsNull([直调入库单_数量],0)+IsNull([采购收货_数量],0)+IsNull([盘点差异_数量],0)-IsNull([库存调整出_数量],0)-IsNull([直调出库单_数量],0)-IsNull([采购退货_数量],0)")
.Add("销售数量",Gettype(Integer), "IsNull([销售_数量],0)-IsNull([退货_数量],0)")
.Add("剩余",Gettype(Integer), "IsNull([实入数量],0)-IsNull([销售数量],0)")
End With
For Each r As DataRow In DataTables("动态调货_动态调货").Select("")
Dim dr As DataRow = DataTables("直调入库单明细").Find("款号='" & r("款号") & "' and 花色='" & r("花色") & "'and 尺码='" & r("尺码") & "'and 仓库='" & r("仓库") & "'","日期 desc")
If dr IsNot Nothing Then
Dim t1 As TimeSpan = Date.Today - CDate(dr("日期"))
r("最后上货天数") = t1.TotalDays
End If
Dim dcMx As DataRow = DataTables("动态调货_出入库明细").Find("款号='" & r("款号") & "' and 花色='" & r("花色") & "'and 尺码='" & r("尺码") & "'and 仓库='" & r("仓库") & "'")
If dcMx IsNot Nothing Then
r("调出仓库_进") = dcMx("实入数量")
r("调出仓库_销") = dcMx("销售数量")
r("调出仓库_存") = dcMx("剩余")
End If
Dim drMx As DataRow = DataTables("动态调货_出入库明细").Find("款号='" & r("款号") & "' and 花色='" & r("花色") & "'and 尺码='" & r("尺码") & "'and 仓库='" & drck.Text & "'")
If drMx IsNot Nothing Then
r("调入仓库_进") = drMx("实入数量")
r("调入仓库_销") = drMx("销售数量")
r("调入仓库_存") = drMx("剩余")
End If
Next
Dim SqlDrKcb As String
Dim SqlDrWqs As String
Dim bdDr As New GroupTableBuilder("库存表",SqlDrKcb,"EFSG")
Dim dtDr As fxDataSource
bdDr.Groups.AddDef("款号") '根据型号分组
bdDr.Groups.AddDef("品类")
bdDr.Groups.AddDef("品名")
bdDr.Groups.AddDef("季节")
bdDr.Groups.AddDef("花色")
bdDr.Groups.AddDef("尺码")
bdDr.Groups.AddDef("吊牌价")
bdDr.Groups.AddDef("仓库")
bdDr.Totals.AddDef("数量","现有库存_数量")
dtDr = bdDr.BuildDataSource()
Dim bdDr2 As New GroupTableBuilder("未签收",SqlDrWqs,"EFSG")
Dim dtDr2 As fxDataSource
bdDr2.Groups.AddDef("款号") '根据型号分组
bdDr2.Groups.AddDef("品类")
bdDr2.Groups.AddDef("品名")
bdDr2.Groups.AddDef("季节")
bdDr2.Groups.AddDef("花色")
bdDr2.Groups.AddDef("尺码")
bdDr2.Groups.AddDef("吊牌价")
bdDr2.Groups.AddDef("仓库")
bdDr2.Totals.AddDef("数量","未签收_数量")
dtDr2 = bdDr2.BuildDataSource()
Dim n As String() = {"款号","花色","尺码","季节","仓库","品类","品名","吊牌价"} '指定连接列
dtDr.Combine(n,dtDr2,n) '将销售统计数据组合到进货统计数据
Tables("动态调货_调入仓库库存").DataSource =dtDr
With DataTables("动态调货_调入仓库库存").DataCols '用表达式列计算库存数据
.Add("数量",Gettype(Integer), "IsNull([现有库存_数量],0)+IsNull([未签收_数量],0)")
End With
Dim SqlZdckd_dr As String
Dim b1_dr As New GroupTableBuilder("调入仓库明细",SqlZdckd_dr,"EFSG")
b1_dr.Groups.AddDef("款号") '根据型号分组
b1_dr.Groups.AddDef("花色")
b1_dr.Groups.AddDef("尺码")
b1_dr.Groups.AddDef("仓库")
b1_dr.Totals.AddDef("数量")
Tables("动态调货_调出仓库出库单明细").DataSource = b1_dr.BuildDataSource
For Each nbr As DataRow In DataTables("动态调货_动态调货").Select("")
If DataTables("动态调货_调入仓库库存").Compute("Sum(数量)","款号='" & nbr("款号") & "' and 花色='" & nbr("花色") & "'and 尺码='" & nbr("尺码") & "'")>0 Then
nbr("备注")= "有库存"
End If
If DataTables("买断款").Compute("Sum(数量)","款号='" & nbr("款号") & "'and 花色='" & nbr("花色") & "'and 尺码='" & nbr("尺码") & "'and 仓库='" & nbr("仓库") & "'")>0 Then
nbr("备注")= nbr("备注")&"|买断款"
End If
If DataTables("销售明细").Compute("Sum(数量)","款号='" & nbr("款号") & "'and 花色='" & nbr("花色") & "'and 尺码='" & nbr("尺码") & "'and 仓库='" & drck.Value & "'")>0 Then
nbr("备注")= nbr("备注")&"|曾销售"
End If
If DataTables("动态调货_调出仓库出库单明细").Compute("Sum(数量)","款号='" & nbr("款号") & "'and 花色='" & nbr("花色") & "'and 尺码='" & nbr("尺码") & "'and 仓库='" & drck.Value & "'")>0 Then
nbr("备注")= nbr("备注")&"|曾调出"
End If
Next