THYS补记录

--查漏入库
SELECT a.is_zx,a.is_gsp,a.djbh,a.dj_sn,a.spid,a.hw,a.pihao,a.pici,a.shl,a.baozhiqi,a.sxrq,a.baozhshl,a.lingsshl
      ,b.shl,b.spid,b.hw,b.pihao,b.pici
FROM jzorder_mx AS a
     LEFT JOIN pf_ckmx AS b ON a.djbh = b.xgdjbh AND a.dj_sn = b.recnum AND a.spid = b.spid AND a.pihao = b.pihao
WHERE a.djbh='XSTZDB00019013'
ORDER BY b.dj_sn
--未验收
SELECT xgdjbh,recnum,spid,hw,pihao,shl,djbh,dj_sn FROM GSP_THYS WHERE xgdjbh='XSTZDB00019013' ORDER BY recnum
--开票状态
--SELECT is_zx,is_gsp,shenhe,* FROM jzorder_hz WHERE djbh='XSTZDB00017544'
--入库情况
--SELECT COUNT(*) FROM pf_ckmx WHERE xgdjbh ='XSTZDB00012481' 
--插入记录
--INSERT INTO GSP_THYS
--            (djbh,dj_sn,djlx,djbs,rq,ontime,kaiprq,gsp_hege,dwbh,spid,hw,pihao,shl,hegshl,buhgshl,sxrq,baozhiqi,miejph,baozhshl,lingsshl,jlgg,dw,pizhwh,waig,baozh,jiel,bm,yanshr,jingren,jianren,jinhpz,dieyshr,jianyriqi,choujshl,jianybz,jianyqk,jianyyyj,jianyzyj,zhilyj,guomcm,baogshbh,jinkzczh,yuany,chulqk,xgdjbh,recnum,is_fucha,is_jush,is_bhgcl,isdj,username,bendian,beizhu,shouhsj,shousypwd,djbh_JHF,djbh_JHS,dhrq,is_yyjbg,hegeshl,bhegeshl,is_lc,dangqzht)
--SELECT  djbh
--       ,6 dj_sn,djlx,djbs,rq,ontime,kaiprq,gsp_hege,dwbh
--       ,'SPH00002157' spid,hw
--       ,'450A'pihao
--       ,10 shl,hegshl,buhgshl
--       ,'2023-09-02' sxrq,'2018-09-03'baozhiqi
--       ,miejph
--       ,0 baozhshl,10 lingsshl
--       ,jlgg,dw,pizhwh,waig,baozh
--       ,'验收合格数量10,通过保管员入库.'jiel
--       ,bm,yanshr,jingren,jianren,jinhpz,dieyshr,jianyriqi
--       ,10 choujshl,jianybz,jianyqk,jianyyyj,jianyzyj,zhilyj,guomcm,baogshbh,jinkzczh,yuany,chulqk,xgdjbh
--       ,22 recnum,is_fucha,is_jush,is_bhgcl,isdj,username,bendian,beizhu,shouhsj,shousypwd,djbh_JHF,djbh_JHS,dhrq,is_yyjbg
--       ,10 hegeshl
--       ,0 bhegeshl,is_lc,dangqzht
--FROM GSP_THYS
--WHERE djbh='GTHZDB00019113' AND dj_sn = 2

--DELETE GSP_THYS WHERE djbh='GTHZDB00019113  ' AND dj_sn = 2
--UPDATE jzorder_mx SET is_gsp='是' WHERE djbh='XSTZDB00019013' AND dj_sn =22
--UPDATE GSP_THYS SET recnum =2 WHERE djbh='GTHZDB00017653 ' AND dj_sn =13



--验证
select 1 as xuanze,a.djbh as xgdjbh,a.dj_sn as recnum ,0 as canssl,0 as  quxsl,
 (a.shl-isnull(a.yiwchsl,0)) as shl,a.dj,a.hshj,a.dj as tmpdj,a.hshj as tmphshj,
 a.koul,a.isjs,(a.je-isnull(a.yiwchje,0)) as je,
 a.jlgg,a.dj_sn as recnum,a.baozhshl,a.lingsshl,(a.shl-isnull(a.yiwchsl,0))
 as weiwchshl,(a.je-isnull(a.yiwchje,0)) as weiwchje,
 (a.hsje-a.yiwchsje) as hsje,((a.hsje-a.yiwchsje)-(a.je-isnull(a.yiwchje,0))) as she
  ,a.*,b.spbh,b.spmch,b.dw,b.shpgg,b.shpchd,b.chbjs,b.pfpj,c.hwbh,c.huowname,b.*
 from jzorder_mx a (nolock)
  join spkfk b (nolock) on a.spid = b.spid
  left join huoweizl c (nolock) on a.hw = c.hw
  join gsp_thys d (nolock) on a.spid = d.spid
 where a.djbh='XSTZDB00019013' and a.is_zx='否' and a.is_gsp='是'
 and d.isdj='否' and a.djbh like 'xst%' and a.dj_sn=d.recnum and a.djbh=d.xgdjbh
 order by a.djbh,a.dj_sort