SELECT BANKNAME,QKIND,SUM(BSUM-CASH-CARD-ORDCASH-ORDCARD-ATM) from qhead where TR<>'Y2' group by BANKNAME,QKIND

delete FROM `qpandn` WHERE PADNADAY <='2020-03-09'

create table qpandnn as SELECT distinct * FROM `qpandn`

SELECT * FROM `qitems` WHERE GOODSNO='UY0974179112' and (DEPTNO='B' or USER='B')

SELECT QKIND,FORMDATE,UNIT10,UNIT20,UNIT30,UNIT40 FROM `qitems` WHERE GOODSNO='UY0974179112' and (DEPTNO='B' or USER='B') order by QKIND,FORMDATE

SELECT GOODSNOS,QKIND,FORMDATE,UNIT10,UNIT20,UNIT30,UNIT40 FROM `qitems` WHERE GOODSNO='UY0974179112' and (DEPTNO='B' or USER='B') order by GOODSNOS,QKIND,FORMDATE

SELECT DEPTNO,USER,GOODSNOS,QKIND,FORMDATE,UNIT10,UNIT20,UNIT30,UNIT40 FROM `qitems` WHERE GOODSNO='UY0974179112' and (DEPTNO='B' or USER='B') order by GOODSNOS,QKIND,FORMDATE

SELECT distinct GOODSNO,COLORNO,SIZE FROM `qdetail` order by GOODSNO


所以若要調整實際庫存量,要用這一次的實際盤點量去反算系統的差量+後來補進貨的量

關鍵原因就要追究SERVER_REPEAT 清除後所產生的差額



將最後盤點日的 每一商品的上存量 比較同期的 伺服器實際 上存量 算出差額

再將差額 存入一張期初的調整單Qitems (UNIT20)

例如 盤表的上存為 4 伺服器統計的上存為 2 這時候還要存入調整量+2

06,橄綠,F,0,0,0,0,0,0,0,0，21,梅紅,F,0,0,-3,3,0,0,0,3，53,藍,F,0,0,0,0,0,0,0,0，55,深駝,F,0,0,-3,3,0,0,0,3，83,灰,F,0,0,0,0,0,0,0,0，93,黑,F,0,0,0,0,0,0,0,0，

建立B 2021-06-22 在庫量 修正值

insert into qmtrs(GOODSNO,ID) select a.GOODSNOS,sum(a.UNIT10)-sum(a.UNIT20)-sum(a.UNIT30)-sum(a.UNIT40) from qitems as a where a.DEPTNO='B' and a.ISCONFIRM='Y' and a.FORMDATE<='2021-06-22' group by a.GOODSNOS

insert into qmtrs(GOODSNO,ID) select a.GOODSNOS,sum(a.UNIT20) from qitems as a where a.USER ='B' and (a.QKIND='20' or a.QKIND='21') and a.ISCONFIRM='Y' and a.FORMDATE<='2021-06-22' group by a.GOODSNOS


如何 讀取 qpandn B門市 2021-06-22 盤表 取得 DATA 並分析 每一個貨號的 在庫量 是否 與修正的在庫量差
進而 修正 過帳的 qitems UNIT20 SUBPRICE


RATEPRICE 存入修正的在庫量
REALSUM 存入當時的盤表在庫量

update qitems set RATEPRICE =(select sum(ID) from qmtrs where GOODSNO=qitems.GOODSNOS group by GOODSNO) where FORMNO='AAB221000002'


check in LASTSTK

SELECT * FROM `qitems` WHERE FORMNO='AAB221000002' and GOODSNO like 'UA002%'

SELECT * FROM `qpandn` WHERE PNDNADAY='2021-06-22' and GOODSNO like 'UA002%'

SELECT sum(ID) FROM `qmtrs` WHERE GOODSNO ='UA002-00-F' group by GOODSNO

SELECT * FROM `qitems` WHERE (DEPTNO='B' or USER='B') and GOODSNO='UA002'