门店进销存明细表

--进销存产品流水账
if object_id('tempdb..#RPT4201_DATA') <> 0
drop table #RPT4201_DATA
CREATE TABLE #RPT4201_DATA
(
ERI VARCHAR(20),
SERIAL INT,
SHOP VARCHAR(40),
SHOPNAME VARCHAR(40),
PROD VARCHAR(40),
PRODNAME VARCHAR(40),
PRODSPEC VARCHAR(40),
UNIT VARCHAR(20),
SDATE VARCHAR(40),
BILCODE VARCHAR(40),
BILTYPE VARCHAR(20),
GWN VARCHAR(20),
GWNNAME VARCHAR(40),
VPNO VARCHAR(40),
REMARK VARCHAR(200),
IQTY DECIMAL(30,10),
OQTY DECIMAL(30,10),
QTY DECIMAL(30,10),
INCOST DECIMAL(30,10),
INAMT DECIMAL(30,10),
OUTCOST DECIMAL(30,10),
OUTAMT DECIMAL(30,10),
COST DECIMAL(30,10),
TAMT DECIMAL(30,10)
)
DECLARE @SHOP VARCHAR(20)
DECLARE @SHOPNAME VARCHAR(40)
DECLARE @STAFF VARCHAR(40)
DECLARE @BDATE DATETIME
DECLARE @EDATE DATETIME
DECLARE @PROD VARCHAR(40)
DECLARE @PRODNAME VARCHAR(40)
DECLARE @PRODSPEC VARCHAR(40)
DECLARE @UNIT VARCHAR(20)
DECLARE @OUUNIT VARCHAR(20)
DECLARE @SDATE DATETIME
DECLARE @BILCODE VARCHAR(40)
DECLARE @BILTYPE VARCHAR(20)
DECLARE @VPNO VARCHAR(40)
DECLARE @REMARK VARCHAR(200)
DECLARE @IQTY DECIMAL(30,10)
DECLARE @IOUQTY DECIMAL(30,10)
DECLARE @OQTY DECIMAL(30,10)
DECLARE @OOUQTY DECIMAL(30,10)
DECLARE @QTY DECIMAL(30,10)
DECLARE @OUQTY DECIMAL(30,10)
DECLARE @COST DECIMAL(30,10)
DECLARE @AMT DECIMAL(30,10)
DECLARE @TAMT DECIMAL(30,10)
DECLARE @SERIAL INT
DECLARE @FQTY DECIMAL(30,10)
DECLARE @FOUQTY DECIMAL(30,10)
DECLARE @FAMT DECIMAL(30,10)
DECLARE @TMPQTY DECIMAL(30,10)
DECLARE @TMPOUQTY DECIMAL(30,10)
DECLARE @TMPAMT DECIMAL(30,10)
DECLARE @INCOST DECIMAL(30,10)
DECLARE @INAMT DECIMAL(30,10)
DECLARE @OUTCOST DECIMAL(30,10)
DECLARE @OUTAMT DECIMAL(30,10)
DECLARE @SORTID VARCHAR(20)
DECLARE @GWN VARCHAR(20)
DECLARE @GWNNAME VARCHAR(40)
DECLARE @PRODTMP VARCHAR(20)

SET @BDATE='2015-01-01'
SET @EDATE='2015-01-30'
SET @STAFF='CK01'


DECLARE PROD_ITEMS CURSOR FOR
SELECT PROD,GWN,FQTY FROM PRODQTY WHERE GWN IN (SELECT GWN FROM ORDSHOP WHERE CODE=(SELECT SHOP FROM SHOPSTAFF WHERE STAFF=@STAFF)) ORDER BY PROD
OPEN PROD_ITEMS
FETCH NEXT FROM PROD_ITEMS
INTO @PROD,@GWN,@FQTY
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @PRODNAME=CNAME,@PRODSPEC=SPEC,@UNIT=(SELECT CNAME FROM PRODUNIT WHERE CODE=PRODUCT.UNIT),@OUUNIT=OTHERUNIT FROM PRODUCT WHERE CODE=@PROD
SELECT @GWNNAME=NAME FROM GODOWN WHERE CODE=@GWN
SET @QTY = 0
SET @OUQTY = 0
SET @TAMT = 0
SET @SERIAL = 0

--期初
SELECT @TMPQTY=A.FQTY,@TMPOUQTY=A.FOUQTY,@TMPAMT=A.FQTY*B.FCOST FROM PRODQTY A
LEFT JOIN
PRODUCT B ON A.PROD= B.CODE
WHERE A.GWN=@GWN AND A.PROD=@PROD
SET @QTY=@QTY+@TMPQTY
SET @OUQTY=@OUQTY+@TMPOUQTY
SET @TAMT=@TAMT+@TMPAMT
SET @SERIAL=@SERIAL+1

--期前
SELECT @TMPQTY=ISNULL(SUM(QTY*QTYOP),0),@TMPOUQTY=ISNULL(SUM(OUQTY*QTYOP),0),@TMPA

MT=ISNULL(SUM(CASE SORTID WHEN '1C' THEN AMT*-1 WHEN '1W' THEN AMT
ELSE CASE COSTTRAN WHEN 1 THEN AMT*QTYOP ELSE QTY*COST*QTYOP END END),0) FROM PRODTRAN WHERE PROD=@PROD AND GWN=@GWN
AND SDATE<@BDATE AND SORTID<>'5C'
SET @QTY=@QTY+@TMPQTY
SET @OUQTY=@OUQTY+@TMPOUQTY
SET @TAMT=@TAMT+@TMPAMT

--插入期初
INSERT INTO #RPT4201_DATA (ERI,SERIAL,PROD,PRODNAME,PRODSPEC,UNIT,GWN,GWNNAME,SDATE,BILCODE,BILTYPE,TAMT)
SELECT '',@SERIAL,@PROD,@PRODNAME,@PRODSPEC,@UNIT,@GWN,@GWNNAME,SUBSTRING(CONVERT(VARCHAR(20),@BDATE,120),1,10)+'之前','期初结存','',@TAMT

DECLARE @ERI VARCHAR(20)
DECLARE @OP INT
DECLARE @COSTOP INT
DECLARE @TRANQTY DECIMAL(30,10)
DECLARE @TRANOUQTY DECIMAL(30,10)
DECLARE @TRANAMT DECIMAL(30,10)

DECLARE TRAN_ITEMS CURSOR FOR
SELECT SRCERI AS ERI,VCHRCODE,SDATE,dbo.TRANNAME(SORTID) AS BILTYPE,QTY,OUQTY,COST,AMT,QTYOP AS OP,COSTTRAN AS COSTOP,VPNO,SORTID FROM
PRODTRAN WHERE PROD=@PROD AND SORTID<>'5C' AND SDATE BETWEEN @BDATE AND @EDATE AND GWN =@GWN
ORDER BY SDATE

OPEN TRAN_ITEMS
FETCH NEXT FROM TRAN_ITEMS
INTO @ERI,@BILCODE,@SDATE,@BILTYPE,@TRANQTY,@TRANOUQTY,@COST,@TRANAMT,@OP,@COSTOP,@VPNO,@SORTID
WHILE @@FETCH_STATUS = 0

BEGIN
IF @SORTID='1Z'
SELECT @ERI=(SELECT ERI FROM STKALLT2 WHERE INERI=@ERI)
IF @SORTID='51'
SELECT @ERI=(SELECT ERI FROM STKALLT2 WHERE OUTERI=@ERI)
IF @OP=1 OR (@SORTID='1W') OR (@SORTID='1C')
BEGIN
SET @QTY=@QTY+@TRANQTY*@OP
SET @OUQTY=@OUQTY+@TRANOUQTY*@OP
IF @SORTID='1C'
SET @TAMT=@TAMT+@TRANAMT*-1
ELSE IF @SORTID='1W'
SET @TAMT=@TAMT+@TRANAMT
ELSE
BEGIN
IF @COSTOP=1
SET @TAMT=@TAMT+@TRANAMT
ELSE
SET @TAMT=@TAMT+@TRANQTY*@COST
END
SET @INCOST=@COST
IF @SORTID='1C'
SET @INAMT=@TRANAMT*-1
ELSE IF @SORTID='1W'
SET @INAMT=@TRANAMT
ELSE
BEGIN
IF @COSTOP=1
SET @INAMT=@TRANAMT
ELSE
SET @INAMT=@TRANQTY*@COST
END
SET @SERIAL=@SERIAL+1
INSERT INTO #RPT4201_DATA (ERI,SERIAL,PROD,PRODNAME,PRODSPEC,UNIT,GWN,GWNNAME,SDATE,BILCODE,BILTYPE,VPNO,IQTY,OQTY,QTY,INCOST,INAMT,COST,TAMT)
SELECT @ERI,@SERIAL,@PROD,@PRODNAME,@PRODSPEC,@UNIT,@GWN,@GWNNAME,SUBSTRING(CONVERT(VARCHAR(20),@SDATE,120),1,10),@BILCODE,@BILTYPE,@VPNO,@TRANQTY*@OP,@TRANOUQTY*@OP,@QTY,@INCOST,@INAMT,@COST,@TAMT
END
ELSE
BEGIN
SET @QTY=@QTY-@TRANQTY
SET @OUQTY=@OUQTY-@TRANOUQTY
SET @OUTCOST=@COST
IF @QTY=0 --数量为0的处理
BEGIN
SET @OUTAMT=@TAMT
SET @TAMT=@TAMT-@OUTAMT
END
ELSE
BEGIN
SET @OUTAMT=@TRANQTY*@COST
SET @TAMT=@TAMT-@TRANQTY*@COST
END
SET @SERIAL=@SERIAL+1
INSERT INTO #RPT4201_DATA (ERI,SERIAL,PROD,PRODNAME,PRODSPEC,UNIT,GWN,GWNNAME,SDATE,BILCODE,BILTYPE,VPNO,IQTY,OQTY,QTY,INCOST,INAMT,COST,TAMT)
SELECT @ERI,@SERIAL,@PROD,@PRODNAME,@PRODSPEC,@UNIT,@GWN,@GWNNAME,SUBSTRING(CONVERT(VARCHAR(20),@SDATE,120),1,10),@BILCODE,@BILTYPE,@VPNO,@TRANQTY*@OP,@TRANOUQTY*@

OP,@QTY,@INCOST,@INAMT,@COST,@TAMT
END

FETCH NEXT FROM TRAN_ITEMS
INTO @ERI,@BILCODE,@SDATE,@BILTYPE,@TRANQTY,@TRANOUQTY,@COST,@TRANAMT,@OP,@COSTOP,@VPNO,@SORTID
END
CLOSE TRAN_ITEMS
DEALLOCATE TRAN_ITEMS


FETCH NEXT FROM PROD_ITEMS
INTO @PROD,@GWN,@FQTY
END
CLOSE PROD_ITEMS
DEALLOCATE PROD_ITEMS

SELECT * FROM #RPT4201_DATA ORDER BY PROD,GWNNAME,SERIAL