Dim d1 As Date = e.Form.Controls("DateTimePicker1").value
Dim d2 As Date = e.Form.Controls("DateTimePicker2").value
Dim md As String = e.Form.Controls("门店").text
Dim qy As String = e.Form.Controls("区域").text
Dim filter1 As String = "1=1 "
Dim filter2 As String = "1=1 "
If qy > "" Then
filter1 += " and 区域 in ('" & qy.Replace(",", "','") & "')"
End If
If md > "" Then
filter1 += " and 门店 in ('" & md.Replace(",", "','") & "')"
filter2 += " and 门店 in ('" & md.Replace(",", "','") & "')"
End If
If d1 <> Nothing Then
filter1 += " and 购买日期 >= #" & d1 & "# "
filter2 += " and 日期 >= #" & d1 & "# "
End If
If d2 <> Nothing Then
filter1 += " and 购买日期 <= #" & d2 & "# "
filter2 += " and 日期 <= #" & d2 & "# "
End If
Dim qry = "select sum(金额) as VIP客户销售额, sum(数量) as VIP客户购买的产品数量, (select count(*) from (select DISTINCT vip客户卡号 from {VIP客户消费明细} where " & filter1 & ")) As 购买过的VIP客户人数, VIP客户购买的产品数量 / 购买过的VIP客户人数 as 每个VIP客户平均购买多少件, VIP客户销售额 / 购买过的VIP客户人数 as 平均每个VIP客户买多少钱, VIP客户销售额 / VIP客户购买的产品数量 as 平均每件衣服多少钱, (select sum(包含非VIP客户的总销售) from {每日总销售额} where " & filter2 & ") As 包含非VIP客户的总销售额,(VIP客户销售额/包含非VIP客户的总销售额*100) & '%' as VIP客户的销售占比 from {VIP客户消费明细} where " & filter1
e.Form.Controls("销售额查询").Table.fill(qry, True)