执行代码：
Db::query('exec Update_Auditing_Order @idstr='.$id.' ,@zdr='.$adminInfo['oper_name']);返回-1即成功
sqlserver存储过程函数：
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Update_Auditing_Order]
-- Add the parameters for the stored procedure here
@idstr nvarchar(max),
@zdr nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @kg22 bit
declare @sqlstr nvarchar(max)
SET NOCOUNT ON
Set XACT_ABORT ON;
begin try
BEGIN TRANSACTION
create table #idtable --创建临时表#Tmp
(
id bigint , --创建列ID,并且每次新增一条记录就会加1
djlx smallint,
primary key (id) --定义spbm为临时表#Tmp的主键
);
set @sqlstr='insert into #idtable (id,djlx) select ID,djlx from prindx where DJLX in (1,2,4,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,28,29,30) and id in ('+@idstr+') and auditing_flag=0 '
exec(@sqlstr)
--将选中的单据，将审核标志改了，因为后面计算库存，要审核的才出来，所以这里先付值，针对销售单还要计算重量与真实金额
update prindx set Auditing_Flag=1,auditing_ZDR=@zdr,ye=case when DJLX in (1,2,4,5) then JE-SFJE else ye end where ID in (select ID from #idtable )
--针对销售单还要计算重量与真实金额
update t2 set TRUE_JE=t1.HSJE,all_weight=t1.all_weight from (select t1.id,SUM(t2.HSJE) hsje,SUM(t2.pack_order_sl*t3.weight) all_weight from prindx t1 inner join xsd t2 on t1.ID=t2.id inner join PACK_UNIT t3 on t2.SPBM=t3.SPBM and t2.id_unit=t3.pack_id where t1.ID in (select ID from #idtable where djlx=4 ) group by t1.id) t1 inner join prindx t2 on t1.ID=t2.id
--计算所有单的库存
--如果是进货单则看否有批号，没有自动产生批号
update t1 set PH=t1.spbm+'_'+cast(t1.id as varchar)+'_'+cast(t1.HH as varchar) from JHD t1 inner join prindx t2 on t1.id=t2.ID where isnull(t1.ph,'')='' and t2.DJLX=1
-----------------------------------------------------------------
--这是蔬菜软件要执行的
select @kg22=KG22 from XTPZ
if @kg22=1 --只有库存模块才减库存
begin
insert into SPTZ ( spbm,CKBM,SL,QCSL,sptz_packingSL ) select spbm,ckbm,0,0,0 from View_CalcKCSL_KcslSplit where ID in (select ID from #idtable ) and CKBM+'$'+spbm not in (select CKBM+'$'+spbm from SPTZ) group by CKBM,spbm
update sptz set sl=isnull(v1.sumsl,0)+v2.sl,sptz_packingSL=ISNULL(v1.sum_packingSL,0)+isnull(v2.sptz_packingSL,0) from sptz v2 inner join (select spbm,ckbm,SUM(signed_sl) as sumsl,SUM(Signed_packingNum) as sum_packingSL from View_CalcKCSL_KcslSplit where ID in (select ID from #idtable ) and ID not in (select t1.ID from #idtable t1 inner join prindx t2 on t1.id=t2.ID where t2.djlx=4 and IsDel_kcsl=1 ) group by spbm,ckbm) v1 on v2.CKBM=v1.CKBM and v2.SPBM=v1.SPBM inner join spbm v3 on v2.SPBM=v3.spbm
where v2.spbm=v1.spbm and v2.ckbm=v1.ckbm and isnull(v3.stock_bz,0)=0
-- 处理批次商品，
insert into sppctz ( spbm,CKBM,SL,QCSL,ph,sppctz_packingSL ) select t1.SPBM,ckbm,0,0,ph,0 from View_CalcKCSL_KcslSplit t1 inner join spbm t2 on t1.SPBM=t2.spbm and t2.CBSF='指定批次法' where CKBM+'$'+t1.spbm+'$'+ph not in (select CKBM+'$'+spbm+'$'+ph from SPpcTZ) group by CKBM,t1.spbm,ph
update sppctz set sl=isnull(v1.sumsl,0)+v2.sl,sppctz_packingSL=isnull(v1.sum_packingSL,0)+isnull(v2.sppctz_packingSL,0) from sppctz v2 inner join (select spbm,ckbm,ph,cbjj,SUM(signed_sl) as sumsl,SUM(Signed_packingNum) as sum_packingSL from View_CalcKCSL_KcslSplit where ID in (select ID from #idtable ) and ID not in (select t1.ID from #idtable t1 inner join prindx t2 on t1.id=t2.ID where t2.djlx=4 and IsDel_kcsl=1 ) group by ckbm,spbm,ph,cbjj) v1 on v2.CKBM=v1.CKBM and v2.SPBM=v1.SPBM and v2.PH=v1.PH and v2.JJ=v1.cbjj inner join spbm v3 on v2.SPBM=v3.spbm
where isnull(v3.stock_bz,0)=0 and v3.CBSF='指定批次法'
update spbm set kcsl=isnull(v1.sumsl,0)+kcsl,kcje=(isnull(v1.sumsl,0)+kcsl)*case when CBSF='每天平均法' then EVERDAY_TRUECBJJ else cbjj end,spbm_packingSL=isnull(v1.sum_packingSL,0)+isnull(spbm_packingSL,0) from spbm v2 inner join (select spbm,SUM(signed_sl) as sumsl,SUM(Signed_packingNum) as sum_packingSL from View_CalcKCSL_KcslSplit where ID in (select ID from #idtable ) and ID not in (select t1.ID from #idtable t1 inner join prindx t2 on t1.id=t2.ID where t2.djlx=4 and IsDel_kcsl=1 ) group by spbm) v1 on v2.spbm=v1.spbm where isnull(v2.stock_bz,0)=0
end else
begin
insert into SPTZ ( spbm,CKBM,SL,QCSL,sptz_packingSL ) select spbm,ckbm,0,0,0 from view_calckcsl where ID in (select ID from #idtable ) and CKBM+'$'+spbm not in (select CKBM+'$'+spbm from SPTZ) group by CKBM,spbm
update sptz set sl=isnull(v1.sumsl,0)+v2.sl,sptz_packingSL=ISNULL(v1.sum_packingSL,0)+isnull(v2.sptz_packingSL,0) from sptz v2 inner join (select spbm,ckbm,SUM(signed_sl) as sumsl,SUM(Signed_packingNum) as sum_packingSL from view_calckcsl where ID in (select ID from #idtable ) and ID not in (select t1.ID from #idtable t1 inner join prindx t2 on t1.id=t2.ID where t2.djlx=4 and IsDel_kcsl=1 ) group by spbm,ckbm) v1 on v2.CKBM=v1.CKBM and v2.SPBM=v1.SPBM inner join spbm v3 on v2.SPBM=v3.spbm
where v2.spbm=v1.spbm and v2.ckbm=v1.ckbm and isnull(v3.stock_bz,0)=0
-- 处理批次商品，
insert into sppctz ( spbm,CKBM,SL,QCSL,ph,sppctz_packingSL ) select t1.SPBM,ckbm,0,0,ph,0 from View_CalcKCSL t1 inner join spbm t2 on t1.SPBM=t2.spbm and t2.CBSF='指定批次法' where CKBM+'$'+t1.spbm+'$'+ph not in (select CKBM+'$'+spbm+'$'+ph from SPpcTZ) group by CKBM,t1.spbm,ph
update sppctz set sl=isnull(v1.sumsl,0)+v2.sl,sppctz_packingSL=isnull(v1.sum_packingSL,0)+isnull(v2.sppctz_packingSL,0) from sppctz v2 inner join (select spbm,ckbm,ph,cbjj,SUM(signed_sl) as sumsl,SUM(Signed_packingNum) as sum_packingSL from view_calckcsl where ID in (select ID from #idtable ) and ID not in (select t1.ID from #idtable t1 inner join prindx t2 on t1.id=t2.ID where t2.djlx=4 and IsDel_kcsl=1 ) group by ckbm,spbm,ph,cbjj) v1 on v2.CKBM=v1.CKBM and v2.SPBM=v1.SPBM and v2.PH=v1.PH and v2.JJ=v1.cbjj inner join spbm v3 on v2.SPBM=v3.spbm
where isnull(v3.stock_bz,0)=0 and v3.CBSF='指定批次法'
update spbm set kcsl=isnull(v1.sumsl,0)+kcsl,kcje=(isnull(v1.sumsl,0)+kcsl)*case when CBSF='每天平均法' then EVERDAY_TRUECBJJ else cbjj end,spbm_packingSL=isnull(v1.sum_packingSL,0)+isnull(spbm_packingSL,0) from spbm v2 inner join (select spbm,SUM(signed_sl) as sumsl,SUM(Signed_packingNum) as sum_packingSL from view_calckcsl where ID in (select ID from #idtable ) and ID not in (select t1.ID from #idtable t1 inner join prindx t2 on t1.id=t2.ID where t2.djlx=4 and IsDel_kcsl=1 ) group by spbm) v1 on v2.spbm=v1.spbm where isnull(v2.stock_bz,0)=0
end
/*--修正净菜商品,但其实不用处理，因为上面视图中已有处理
update t2 set KCSL=t1.kc+KCSL,KCJE=(t1.kc+KCSL)*CBJJ from (select processing_spbm as spbm,KCSL as kc from spbm where Processing_type=1 and del_upSPBMKCSL=1) t1 inner join spbm t2 on t1.SPBM=t2.spbm
update t2 set sl=t1.kc+sl from (select spbm.processing_spbm as spbm,ckbm,sl as kc from sptz inner join spbm on SPTZ.SPBM=spbm.spbm and spbm.Processing_type=1 and spbm.del_upSPBMKCSL=1) t1 inner join sptz t2 on t1.SPBM=t2.spbm and t1.CKBM=t2.CKBM
update spbm set KCSL=0,KCJE=0,QCKCSL=0,QCKCJE=0 where Processing_type=1 and del_upSPBMKCSL=1
*/
--针对已付款的云端订单，更新现金银行信息
update XJYHBM set QMYE=ISNULL(t2.SFJE,0)+t1.QMYE from XJYHBM t1 inner join (select XJYHBM,sum(SFJE) SFJE from (select XJYHBM,SUM(SFJE) SFJE from prindx WHERE djlx=4 and ISNULL(is_remote,0)=1 AND isnull(online_pay,0)=1 and Auditing_Flag=1 and HCBZ<>1 and ID in (select ID from #idtable ) group by XJYHBM union all select XJYHBM,SUM(SFJE)*-1 SFJE from prindx WHERE djlx=5 and ISNULL(is_remote,0)=1 AND isnull(online_pay,0)=1 AND Auditing_Flag=1 and HCBZ<>1 and ID in (select ID from #idtable ) group by XJYHBM) tt group by XJYHBM ) t2 on t1.bm=t2.XJYHBM
--如果是在销售单界面上进行了付款金额的填写，则也要加现金银行信息
update XJYHBM set QMYE=ISNULL(t2.SFJE,0)+t1.QMYE from XJYHBM t1 inner join (select XJYHBM,sum(SFJE) SFJE from (select XJYHBM,SUM(SFJE) SFJE from prindx WHERE djlx=4 and isnull(XJYHBM,'')<>'' AND isnull(sfje,0)<>0 and Auditing_Flag=1 and HCBZ<>1 and ID in (select ID from #idtable ) group by XJYHBM union all select XJYHBM,SUM(SFJE)*-1 SFJE from prindx WHERE djlx=5 and isnull(XJYHBM,'')<>'' AND isnull(sfje,0)<>0 AND Auditing_Flag=1 and HCBZ<>1 and ID in (select ID from #idtable ) group by XJYHBM) tt group by XJYHBM ) t2 on t1.bm=t2.XJYHBM
--------------------------------------------------------------------
--下面计算应收应付
update wldwbm set QMYS=ISNULL(t2.ye,0)+t1.QMYS from wldwbm t1 inner join (select dwbm,sum(ye) ye from (select dwbm,SUM(ye) ye from prindx WHERE djlx IN (4,26) and Auditing_Flag=1 and HCBZ<>1 and ID in (select ID from #idtable ) group by DWBM union all select dwbm,SUM(ye)*-1 ye from prindx WHERE djlx in (5,27) and Auditing_Flag=1 and HCBZ<>1 and ID in (select ID from #idtable ) group by DWBM) tt group by dwbm ) t2 on t1.DWBM=t2.DWBM
update wldwbm set QMYf=ISNULL(t2.ye,0)+t1.QMYf from wldwbm t1 inner join (select dwbm,sum(ye) ye from (select dwbm,SUM(ye) ye from prindx WHERE djlx in (1,24) and Auditing_Flag=1 and HCBZ<>1 and ID in (select ID from #idtable ) group by DWBM union all select dwbm,SUM(ye)*-1 ye from prindx WHERE djlx in (2,25) and Auditing_Flag=1 and HCBZ<>1 and ID in (select ID from #idtable ) group by DWBM) tt group by dwbm ) t2 on t1.DWBM=t2.DWBM
COMMIT TRAN
select 1 as rec
end try
begin catch
rollback transaction
select 0 as rec
end catch
SET NOCOUNT OFF
END