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
加好友 
发短信
Post By：2021/5/10 9:42:00 [只看该作者]
