📄 default.asp
字号:
<!-- #include virtual="/eB3KTransaction.asp" -->
<!-- #include virtual="/Server/ebo.asp" -->
<!-- #include virtual="/Server/cblib.asp" -->
<!-- #include file="../Common.asp" -->
<!-- #include virtual="/Server/SqlReport.asp" -->
<!-- #include virtual="/Server/ReportBD.asp" -->
<!-- #include virtual="/Server/rawdata.asp" -->
<HTML><HEAD><meta http-equiv="Content-Type" content="text/html; charset=gb2312"><LINK REL=stylesheet HREF="/Client/All.css" TYPE="text/css"><script src="/Client/ebComm.js"></script><title></title></HEAD>
<script src="/client/ebo.js"></script>
<script src="/client/Request.js"></script>
<script src="/client/IClient.js"></script>
<script src="/client/grid.js"></script>
<script src="/client/cblib.js"></script>
<script src="/client/print.js"></script>
<script src="/client/clipboard.js"></script>
<%
dim eBP,y
Set eBP = Server.CreateObject("NaFine.Privilege")
y=eBP.PrivilegeTest(0,Clng(1012),8,me)
if CStr(y)<>"0" then err.raise 10000,"InStock.Open", eBP.GetLastError()
dim defaultPrintRows
defaultPrintRows = 40
function GetPrintRows(iRows,defaultPrintRows)
if iRows > defaultPrintRows then
GetPrintRows = defaultPrintRows
else
GetPrintRows = iRows
end if
end function
dim siCount,sc, piCount,pc, ciCount,cc
siCount=CLng(GetForm("sDetail_rows"))
piCount=CLng(GetForm("pDetail_rows"))
ciCount=CLng(GetForm("cDetail_rows"))
sc=CLng(GetForm("stockCheck"))
pc=CLng(GetForm("productCheck"))
cc=CLng(GetForm("codeCheck"))
dim startNumber,endNumber,searchCode
startNumber=cint(GetForm("startNumber"))
endNumber=cint(GetForm("endNumber"))
searchCode=cstr(GetForm("searchCode"))
dim DateFrom,DateTo
DateFrom=GetForm("DateFrom")
DateTo=GetForm("DateTo")
if IsDate(DateFrom) then
DateFrom=DateSerial(year(datefrom), month(datefrom), day(datefrom))
else
err.Raise 10000, "StockStream", "请输入正确的起始日期！"
end if
if IsDate(DateTo) then
DateTo=DateSerial(year(DateTo), month(DateTo), day(DateTo))
else
DateTo=DateSerial(year(Now()), month(Now()), day(Now()))
end if
DateTo=DateADD("d",1,DateTo)
function SQLWhereOR(szField,szServerSideName,iTotalCount)
dim i, szWhere
szWhere = "("
for i = 0 to iTotalCount-1
if i > 0 then szWhere = szWhere & " OR "
szWhere = szWhere & szField & "=" & GetForm(szServerSideName & i)
next
szWhere = szWhere & ")"
SQLWhereOR=szWhere
end function
function RptProduct1(szSelect, szFrom, szWhere, szGroupBy)
SqlSelectAdd szSelect, "[biProduct].[Code] AS 产品编码, [biProduct].[Title] AS 产品名称"
SqlFromAdd szFrom, "[biProduct]"
SqlWhereAdd szWhere, "[biProduct].[ID] =[vl].[ProductID] "
SqlGroupByAdd szGroupBy, "[biProduct].[Code], [biProduct].[Title]"
end function
function RptProduct2(szSelect, szFrom, szWhere, szGroupBy)
SqlSelectAdd szSelect, "[biProduct].[Code] AS 产品编码, [biProduct].[Title] AS 产品名称"
SqlFromAdd szFrom, "[biProduct]"
SqlWhereAdd szWhere, "[biProduct].[ID] =[vt].[ProductID] "
SqlGroupByAdd szGroupBy, "[biProduct].[Code], [biProduct].[Title]"
end function
'=======================================================
dim ssSelect, ssGroupBy, SzSQL
dim szSelect, szFrom, szWhere, szGroupBy, szWhereOther,szWhereOther1
'szWhereOther用于退货单,szWhereOther1 用于其他,szWhere用于库存
szWhere=" [vt].[AccountID]=[biStock].[AccountID] AND [biStock].[AccountID]=1"
szWhereOther=" [vt].[AccountID]=[biStock].[AccountID] AND [biStock].[AccountID]=1 AND [vt].[AuditID]>0 "
szWhereOther1=" [vt].[AccountID]=[biStock].[AccountID] AND [biStock].[AccountID]=1 AND [vt].[AuditID]>0 "
RptStock nothing, szFrom, szWhere, nothing
RptStock nothing, nothing, szWhereOther1, nothing
RptMoveStock nothing, nothing, szWhereOther, nothing
if sc=1 or siCount>0 then
if sc=1 then
RptStock szSelect, nothing, nothing, szGroupBy
SqlSelectAdd ssSelect,"仓库"
SqlGroupByAdd ssGroupBy,"仓库"
end if
if siCount>0 then
SqlWhereAdd szWhere, SQLWhereOR("[vt].[Stock]","sDetail_0_",siCount)
SqlWhereAdd szWhereOther1, SQLWhereOR("[vt].[Stock]","sDetail_0_",siCount)
SqlWhereAdd szWhereOther, SQLWhereOR("[vt].[Organization]","sDetail_0_",siCount)
end if
end if
RptProduct1 nothing, szFrom, szWhereOther1, nothing
RptProduct1 nothing, nothing, szWhereOther, nothing
RptProduct2 nothing, nothing, szWhere, nothing
if pc=1 or piCount>0 then
if pc=1 then
RptProduct1 szSelect, nothing, nothing, szGroupBy
SqlSelectAdd ssSelect,"产品编码,产品名称"
SqlGroupByAdd ssGroupBy,"产品编码,产品名称"
end if
if piCount>0 then
SqlWhereAdd szWhere, SQLWhereOR("[vt].[ProductID]","pDetail_0_",piCount)
SqlWhereAdd szWhereOther1, SQLWhereOR("[vl].[ProductID]","pDetail_0_",piCount)
SqlWhereAdd szWhereOther, SQLWhereOR("[vl].[ProductID]","pDetail_0_",piCount)
end if
end if
if cc=1 or ciCount>0 then
if cc=0 then
RptGoodsCode nothing, szFrom, szWhere, nothing
RptGoodsCode nothing, nothing, szWhereOther1, nothing
RptGoodsCode nothing, nothing, szWhereOther, nothing
else
RptGoodsCode szSelect, szFrom, szWhere, szGroupBy
RptGoodsCode nothing, nothing, szWhereOther1, nothing
RptGoodsCode nothing, nothing, szWhereOther, nothing
SqlSelectAdd ssSelect,"货号"
SqlGroupByAdd ssGroupBy,"货号"
if pc=0 then
SqlSelectAdd szSelect,"biProduct.Title as 产品名称"
SqlGroupByAdd szGroupBy,"biProduct.Title"
SqlSelectAdd ssSelect,"产品名称"
SqlGroupByAdd ssGroupBy,"产品名称"
end if
end if
if ciCount>0 then
SqlWhereAdd szWhere, SQLWhereOR("[biProduct].[ProductID]","cDetail_0_",ciCount)
SqlWhereAdd szWhereOther1, SQLWhereOR("[biProduct].[ProductID]","cDetail_0_",ciCount)
SqlWhereAdd szWhereOther, SQLWhereOR("[biProduct].[ProductID]","cDetail_0_",ciCount)
end if
end if
'按产品编码搜索
dim startWildCard14,endStartCard14
dim startWildCard15,endStartCard15
dim endCount
if searchCode<>"" then
dim searchstr
While startNumber>1
startWildCard14=startWildCard14+"_"
startWildCard15=startWildCard15+"_"
startNumber=startNumber-1
wend
endCount=14-endNumber
while endCount>0
endStartCard14=endStartCard14+"_"
endCount=endCount-1
wend
endCount=15-endNumber
while endCount>0
endStartCard15=endStartCard15+"_"
endCount=endCount-1
wend
dim search14,search15
search14=startWildCard14 & searchCode & endStartCard14
search15=startWildCard15 & searchCode & endStartCard15
searchstr=" AND (([biProduct].[Code] Like '" & search14 & "' AND len([biProduct].[Code])=14) or ([biProduct].[Code] like '" & search15 & "' AND len([biProduct].[Code])=15))"
szWhere = szWhere & searchstr
szWhereOther1 = szWhereOther1 & searchstr
szWhereOther = szWhereOther & searchstr
end if
if len(szSelect)<>0 then szSelect=szSelect & ", "
if len(szFrom)<>0 then szFrom=", " & szFrom
if len(szGroupBy)<>0 then szGroupBy=szGroupBy & ", "
if len(szWhere)<>0 then szWhere="AND " & szWhere
if len(szWhereOther)<>0 then szWhereOther="AND " & szWhereOther
if len(szWhereOther1)<>0 then szWhereOther1="AND " & szWhereOther1
if len(ssSelect)<>0 then ssSelect=ssSelect & ", "
if len(ssGroupBy)<>0 then ssGroupBy=" Group by "&ssGroupBy
'条件： 采购入库 采购退货 销售入库 销售退货 调拨入库 调拨出库 期初
dim szqc,szscrk,szth,szph,szzx,szjmsph,szjmshr,szjmshc,szkc
dim szSQLqc,szSQLscrk,szSQLth,szSQLph,szSQLzx,szSQLjmsph,szSQLjmshr,szSQLjmshc,szSQLkc
dim szCgqcTj,szCgscrkTj,szCgthTj,szCgphTj,szCgzxTj,szCgjmsphTj,szCgjmshrTj,szCgjmshcTj
dim szZgs,szSQLzgs
dim szZgs1,szSQLzgs1
dim szPd,szSQLpd
dim szPd1,szSQLpd1
dim szqc1,szSQLqc1
'入库单 4 经销部入库单 15 期初 18 采购退货单35 出库单 14 生产厂出库单 1 销售退货单 37 调拨单7
'4 经销部入库单 入库
'50 退货单 入库
'54 加盟商换入 入库
'55 加盟商换出 出库
'53 加盟商配货 出库
'配货单 出库
szCgscrkTj = szCgscrkTj & " AND ( BillType = 4)"
szCgthTj = szCgthTj & " AND (BillType = 50)"
szCgphTj = szCgphTj & " AND (BillType = 43)"
szCgzxTj = szCgzxTj & " AND (BillType = 40)"
szZgs = szZgs & " AND (BillType = 71)"
szPd = szPd & " AND (BillType = 9)"
szqc1 = szqc1 & " AND (BillType = 18)"
szCgjmsphTj = szCgjmsphTj & " AND (BillType = 53)"
szCgjmshrTj = szCgjmshrTj & " AND (BillType = 54)"
szCgjmshcTj = szCgjmshcTj & " AND (BillType = 55)"
'期初录入数量
szSQLqc1 = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],SUM([vl].[Quantity]) AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [bdFirstInStock] AS [vt],[bdFirstInStockList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ( [vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') " & szqc1 & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'生产入库单
szSQLscrk = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],SUM([vl].[Quantity]) AS [scrkQu],SUM([vl].[Amount]) AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [BdInStock] AS [vt],[BdInStockList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ( [vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') " & szCgscrkTj & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'退货单
szSQLth = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],SUM([vl].[Quantity]) AS [thQu],SUM([vl].[Amount]) AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [BdUnStock] AS [vt],[BdUnStockList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') " & szCgthTj & szWhereOther &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'配货单
szSQLph = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], SUM([vl].[Quantity]) AS [phQu],SUM([vl].[Amount]) AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [BdMoveOutStock] AS [vt],[BdMoveOutStockList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') " & szCgphTj & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'总公司调拨单
szSQLzgs = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], SUM([vl].[Quantity]) AS [zgsQu],SUM([vl].[Amount]) AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [bdAllMoveStock] AS [vt],[bdAllMoveStockList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') " & szZgs & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
szSQLzgs1 = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], SUM([vl].[Quantity]) AS [zgsQu1],SUM([vl].[Amount]) AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [bdAllMoveStock] AS [vt],[bdAllMoveStockList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') " & szZgs & szWhereOther &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'盘点单
szSQLpd = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount], SUM([vl].[Quantity]) as [pdQu],SUM([vl].[Amount]) as [pdAmount],0 as [pdQu1],0 as [pdAmount1],0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [bdStockCheck] AS [vt],[bdStockCheckList] AS [vl] "& szFrom &_
" WHERE vl.Quantity>0 and vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') " & szPd & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
szSQLpd1 = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount], SUM(-[vl].[Quantity]) as [pdQu1],SUM([vl].[Amount]) as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount],0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [bdStockCheck] AS [vt],[bdStockCheckList] AS [vl] "& szFrom &_
" WHERE vl.Quantity<0 and vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') " & szPd & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'直销单
szSQLzx = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],SUM([vl].[Quantity]) AS [zxQu],SUM([vl].[Amount]) AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [BdDirectSale] AS [vt],[BdDirectSaleList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') AND [vt].[AccountID]=1 " & szCgzxTj & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'加盟商配货
szSQLjmsph = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],SUM([vl].[Quantity]) AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [BdLeagueFixGoods] AS [vt],[BdLeagueFixGoodsList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') AND [vt].[AccountID]=1 " & szCgjmsphTj & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'加盟商换入
szSQLjmshr = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],SUM([vl].[Quantity]) AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [BdLeagueInGoods] AS [vt],[BdLeagueInGoodsList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') AND [vt].[AccountID]=1 " & szCgjmshrTj & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'加盟商换出
szSQLjmshc = "SELECT "& szSelect &" [vl].[ProductID] AS [ProductID], 0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],SUM([vl].[Quantity]) AS [jmshcQu],0 AS [kcQu],0 AS [kcAmount]" &_
" FROM [BdLeagueOutGoods] AS [vt],[BdLeagueOutGoodsList] AS [vl] "& szFrom &_
" WHERE vt.AuditID<>0 and [vt].[id]=[vl].[id] AND ([vt].[UpdateDate]>='"& DateFrom &"' AND [vt].[UpdateDate]<'"& DateTo &"') AND [vt].[AccountID]=1 " & szCgjmshcTj & szWhereOther1 &_
" GROUP BY "& szGroupBy &" [vl].[ProductID]"
'期末数量
szSQLkc = "SELECT "& szSelect &" [vt].[ProductID] AS [ProductID],0 AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],SUM([vt].[Quantity]) AS [kcQu],SUM([vt].[Amount]) AS [kcAmount] " &_
" FROM [resProductStock] AS [vt] "& szFrom &_
" WHERE vt.AuditID<>0 AND (([vt].[CreateDate]<='"& GetDateString(DateTo) &"' AND [vt].[UpdateDate]>'"& GetDateString(DateTo) &"') OR ([vt].[IsDeleted]=0 and [vt].[CreateDate]<='"& GetDateString(DateTo) &"' ))" & szWhere &_
" GROUP BY "& szGroupBy &" [vt].[ProductID]"
'期初数量
szSQLqc = "SELECT "& szSelect &" [vt].[ProductID] AS [ProductID],SUM([vt].[Quantity]) AS [FirstIn],0 AS [FirstAmount],0 AS [scrkQu],0 AS [scrkAmount],0 AS [thQu],0 AS [thAmount], 0 AS [phQu],0 AS [phAmount],0 as [pdQu],0 as [pdAmount],0 as [pdQu1],0 as [pdAmount1], 0 AS [zgsQu],0 AS [zgsQuAmount], 0 AS [zgsQu1],0 AS [zgsQuAmount1],0 AS [zxQu],0 AS [zxAmount],0 AS [jmsphQu],0 AS [jmshrQu],0 AS [jmshcQu],0 AS [kcQu],SUM([vt].[Amount]) AS [kcAmount] " &_
" FROM [resProductStock] AS [vt] "& szFrom &_
" WHERE vt.AuditID<>0 AND (([vt].[CreateDate]<='"& GetDateString(DateFrom) &"' AND [vt].[UpdateDate]>'"& GetDateString(DateFrom) &"') OR ([vt].[IsDeleted]=0 and [vt].[CreateDate]<='"& GetDateString(DateFrom) &"' ))" & szWhere &_
" GROUP BY "& szGroupBy &" [vt].[ProductID]"
szSQL="( "& szSQLscrk &" UNION "& szSQLth & " UNION " & szSQLph & " UNION " & szSQLpd & " UNION " & szSQLpd1 & " UNION " & szSQLzgs & " UNION " & szSQLzgs1 & " UNION " & szSQLzx & " UNION " & szSQLjmsph & " UNION " & szSQLjmshr & " UNION " & szSQLjmshc & " UNION "& szSQLkc & "UNION "& szSQLqc & "UNION "& szSQLqc1 & " )"
szSQL="SELECT "& ssSelect &" SUM([nv].[FirstIn]) AS [期初库存],SUM([nv].[FirstAmount]) AS [期初录入], SUM([nv].[scrkQu]) AS [生产入库数量],sum([nv].[thQu]) AS [退货数量], SUM([nv].[phQu]) AS [配货数量],SUM([nv].[phAmount]) AS [配货金额],SUM([nv].[pdQu]) AS [盘赢数量],SUM([nv].[pdAmount]) AS [盘赢金额],SUM([nv].[pdQu1]) AS [盘亏数量],SUM([nv].[pdAmount1]) AS [盘亏金额],SUM([nv].[zgsQu]) AS [总公司调拨出库数量],SUM([nv].[zgsQuAmount]) AS [总公司调拨出库金额],SUM([nv].[zgsQu1]) AS [总公司调拨入库数量],SUM([nv].[zgsQuAmount1]) AS [总公司调拨入库金额], SUM([nv].[zxQu]) AS [直销数量],SUM([nv].[zxAmount]) AS [直销金额],SUM([nv].[jmsphQu]) AS [加盟商配货数量],SUM([nv].[jmshrQu]) AS [加盟商换入数量],SUM([nv].[jmshcQu]) AS [加盟商换出数量], SUM([nv].[kcQu]) AS [结存数量],'' AS [备注]" &_
" FROM "& szSQL &" AS [nv] " & ssGroupBy
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -