金蝶核销逻辑研究



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[qk_Verification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[qk_Verification]
GO
create proc qk_Verification
@list  as nvarchar(max)--FDInterID, FDEntryID, FSInterID,FSEntryID, FQty, FAmount, FDate, FUserID | FDInterID, FDEntryID, FSInterID,FSEntryID, FQty, FAmount, FDate, FUserID....
as
begin
set nocount on
declare @VerGroup bigint--核销ID
declare @AmountSum money--本次核销的总金额(有时候，一个入库单要核销多个出库单,本变量保存所核销的多个出库单的总金额)
declare @sql      nvarchar(max)
--1解析核销数据
create table #tempVer(FDInterID bigint, FDEntryID bigint, FSInterID bigint,FSEntryID bigint, FQty float, FAmount money, FDate datetime, FUserID bigint)
set @sql=REPLACE(@list,'|',' union all select ')
set @sql ='insert into #tempVer select ' +@sql
exec(@sql)
--2求核销总金额
select @AmountSum=isnull(SUM(famount),0) from #tempVer
begin try
    begin tran
     
        Update t_CheckLockTable Set FInterID=-FInterID--锁表，防止多人核销时公用一个核销ID的情况
        SELECT @VerGroup=ISNULL(MAX(FVerGroup),0) FROM ICClientVer
        set @VerGroup=@VerGroup+1
        SET IDENTITY_INSERT ICCLIENTVER ON
        INSERT INTO ICClientVer(FVerGroup,FDInterID, FDEntryID, FSInterID,FSEntryID, FQty, FAmount, FDate, FUserID)
        select @VerGroup,FDInterID, FDEntryID, FSInterID,FSEntryID, FQty, FAmount, FDate, FUserID from #tempVer
       
  Update t1 SET FCheckStatus=(CASE WHEN t1.FQty=t3.FQty THEN 1 ELSE 2 END)
  From icstockbillEntry t1
  Inner join icstockbill t2 on t1.FInterID=t2.FInterID
  Inner join
  (Select t4.FSInterID,t4.FSEntryID,Sum(t4.FQty) AS FQty From ICClientVer t4
  Inner join (Select Distinct FSInterID,FSEntryID From ICClientVer Where FVerGroup=@VerGroup) t5 On t4.FSInterID=t5.FSInterID And t4.FSEntryID=t5.FSEntryID
  Group by t4.FSInterID,t4.FSEntryID) t3 ON t1.FInterID=t3.FSInterID AND t1.FEntryID=t3.FSEntryID
  WHERE  t2.FTranType =28 
  Update t1 SET FCheckStatus=(CASE WHEN t1.FQty=t3.FQty THEN 1 ELSE 2 END)
  From icstockbillEntry t1
  Inner join icstockbill t2 on t1.FInterID=t2.FInterID
  Inner join
  (Select t4.FDInterID,t4.FDEntryID,Sum(t4.FQty) AS FQty From ICClientVer t4
  Inner join (Select FDInterID,FDEntryID From ICClientVer Where FVerGroup=@VerGroup) t5 On t4.FDInterID=t5.FDInterID And t4.FDEntryID=t5.FDEntryID
  Group by t4.FDInterID,t4.FDEntryID) t3 ON t1.FInterID=t3.FDInterID AND t1.FEntryID=t3.FDEntryID
  WHERE  t2.FTranType=5
  
  
  CREATE TABLE #Temp (
  FInterID      INT,
  FEntryID       INT,
  FMaterialCost DECIMAL(28,10))
  INSERT INTO #Temp(FInterID,FEntryID,FMaterialCost)
  select top 1 FDInterID,FDEntryID,@AmountSum from #tempVer
  
  update t1 set t1.FMaterialCost=t2.FMaterialCost
  FROM ICStockBillEntry t1
  INNER JOIN #Temp t2 ON t1.FInterID=t2.FInterID AND t1.FEntryID=t2.FEntryID
  update t1 set t1.FAmount=CASE WHEN (ISNULL(t3.FTranType,0) = 5 AND ISNULL(t3.FPurposeID,0) = 14191) THEN isnull(t1.FMaterialCost,0) ELSE isnull(t1.FProcessCost,0)+isnull(t1.FMaterialCost,0) END
  FROM ICStockBillEntry t1
  INNER JOIN #Temp t2 ON t1.FInterID=t2.FInterID AND t1.FEntryID=t2.FEntryID
  INNER JOIN ICStockBill t3 ON t1.FInterID=t3.FInterID
  update t1 set t1.FPrice=Round(t1.FAmount/t1.FQty,t2.FPriceDecimal)
  ,t1.FMaterialCostPrice=Round(t1.FMaterialCost/t1.FAuxQty,t2.FPriceDecimal)
  , t1.FAuxPrice=Round(t1.FAmount/t1.FQty,t2.FPriceDecimal)*(isnull(t3.FCoefficient,1)+isnull(t3.FScale,0))
  From IcStockbillEntry t1
  Inner Join t_ICItem t2 On t1.FItemID=t2.FItemID
  Inner Join t_MeasureUnit t3 On t3.FItemID=t1.FUnitID
  INNER JOIN #Temp t4 ON t1.FInterID=t4.FInterID AND t1.FEntryID=t4.FEntryID
  where t1.FQty<>0
  DROP TABLE #Temp
  SET IDENTITY_INSERT ICCLIENTVER OFF
        commit
  select fflag=1,fmsg='ok'
end try
begin catch
    rollback
    SET IDENTITY_INSERT ICCLIENTVER OFF
    select fflag=-1,fmsg=  ERROR_MESSAGE()
end catch

set nocount off
end
 -------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[qk_getVerOutBill]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[qk_getVerOutBill]
GO
create proc qk_getVerOutBill
@FOrderInterID bigint
,@FOrderEntryID int
as
begin
set nocount on
  SELECT u1.FDetailID AS FListEntryID,0  AS FSel,t14.FName AS FPlanVchTplName,t15.FName AS FActualVchTplName,v1.FPlanVchTplID,v1.FActualVchTplID,v1.FVchInterID,v1.FTranType,v1.FInterID,u1.FEntryID,t4.FNumber AS FSupplyNumber,v1.Fdate,
case  when v1.FCheckerID>0 then 'Y' when v1.FCheckerID<0 then 'Y' else '' end AS FCheck,case when v1.FCancellation=1 then 'Y' else '' end AS FCancellation,t4.FName AS FSupplyIDName,
v1.FBillNo,t632.FName AS FPurposeID,tVMI.FName AS FIsVMI,tSup.FName AS FEntrySupplyName,t8.FName AS FDCStockIDName,t13.FShortNumber AS FItemIDName,
t13.FNumber AS FFullNumber,t13.Fname AS FItemName,t13.Fmodel AS FItemModel,t16.FName AS FUnitIDName,u1.FBatchNo,u1.Fauxqty,u1.Fauxprice,
u1.Famount,u1.FNote,v1.Fnote AS FNeed,t9.FName AS FSManagerIDName,t10.FName AS FFManagerIDName,t11.FName AS FuserName,
t24.FName AS FCheckerName,CASE WHEN v1.FVchInterID>0 THEN 'Y' WHEN v1.FVchInterID<0 THEN 'Y' ELSE '' END  AS FVoucherStatus,
(SELECT (SELECT FName FROM t_VoucherGroup WHERE FGroupID=t_Voucher.FGroupID)+'-'+CONVERT(Varchar(30),FNumber)  
FROM  t_Voucher  WHERE  FVoucherid=v1.FVchInterID)  AS FVoucherNumber,CASE WHEN v1.FHookInterID>0 THEN 'Y' WHEN v1.FHookInterID<0 THEN 'Y' ELSE '' END AS FHookStatus,u1.FOrderBillNo,u1.FSourceBillNo,
t70.FName AS FSourceTranType,Case u1.FCheckStatus when 1 then 'Y'  when 2 then 'P'  else 'N' end  AS FArapStatus
,v1.FCheckDate, (CASE t103.FName WHEN '*' THEN '' ELSE t103.FName END)  AS FAuxPropIDName,t103.FNumber AS FAuxPropIDNumber,t13.FQtyDecimal,
t13.FPriceDecimal,t30.FName AS FBaseUnitID,u1.FQty AS FBaseQty,u1.FQtyMust,u1.FAuxPlanPrice,u1.FPlanAmount
,Case WHEN t13.FStoreUnitID=0 THEN '' Else  t500.FName end AS FCUUnitName
,Case WHEN t13.FStoreUnitID=0 THEN '' Else  u1.FQty/t500.FCoefficient end AS FCUUnitQty,u1.FAuxQtyMust,
Case When v1.FCurrencyID is Null Or v1.FCurrencyID='' then (Select FScale From t_Currency Where FCurrencyID=1) else t503.FScale end   AS FAmountDecimal,
 (CASE t510.FName WHEN '*' THEN '' ELSE t510.FName END)  AS FSPName,u1.FKFPeriod,u1.FKFDate,u1.FPeriodDate,
 case when (v1.FROB <> 1) then 'Y' else '' end AS FRedFlag,u1.FMapName,u1.FMapNumber,t8.FNumber AS FDCStockIDNumber,t512.FName AS FSecUnitName,
 u1.FSecCoefficient,u1.FSecQty,t621.FName AS FManageType,t123.FName AS FPlanMode,u1.FMTONo,t550.FName AS FRelateBrID,v1.FPOOrdBillNo,
 t560.FName AS FBrID,CASE WHEN v1.FTranStatus=1 THEN 'Y' ELSE '' END AS FTranStatus,TStockBill.FBillNo AS FInStockID,
 case   when u1.FRowClosed=1 then 'Y' else '' end AS FRowClosed,u1.FPositionNO,u1.FItemSize,u1.FItemSuite,
 CASE WHEN u1.FClosing=1 THEN 'Y' ELSE '' END AS FClosing,v1.FPrintCount,t1107.FName AS FHeadSelfB0836,
 t1108.FName AS FEntrySelfB0850,u1.FOrderInterID,u1.FOrderEntryID,t200.FMRPClosed as ICMOStatus,t200.FItemID as FCastItemID,IDENTITY(INT) AS FRow,
 u1.FItemID,u1.FPrice,u1.FQty-isNull(k1.FQty,0) as FPreQty,0.0 as FCurQty
,u1.FAmount-isNull(k1.FAmount,0) as FPreAmount,0.0 as FCurAmount into #temp

 FROM ICStockBill v1 Inner Join ICStockBillEntry u1 on v1.FInterID=u1.FInterID
 left outer join t_Supplier t4 on v1.FSupplyID=t4.FItemID
 left outer join t_Stock t8 on u1.FDCStockID=t8.FItemID
 left outer join t_Emp t9 on v1.FSManagerID=t9.FItemID
 left outer join t_Emp t10 on v1.FFManagerID=t10.FItemID
 left outer join t_User t11 on v1.FBillerID=t11.FUserID
 Inner Join t_ICItem t13 on u1.FItemID=t13.FItemID
 left outer join t_MeasureUnit t16 on u1.FUnitID=t16.FItemID
 left outer join t_User t24 on v1.Fcheckerid=t24.FUserID
 left outer join t_MeasureUnit t30 on t13.FUnitID=t30.FMeasureUnitID
 left outer join v_ICTransType t70 on u1.FSourceTranType=t70.FID
 left outer join ICVoucherTpl t14 on v1.FPlanVchTplID=t14.FInterID
 left outer join ICVoucherTpl t15 on v1.FActualVchTplID=t15.FInterID
 left outer join t_AuxItem t103 on u1.FAuxPropID=t103.FItemid
 left outer join t_MeasureUnit t500 on t13.FStoreUnitID=t500.FItemID
 left outer join t_Currency t503 on v1.FCurrencyID=t503.FCurrencyID
 left outer join t_StockPlace t510 on u1.FDCSPID=t510.FSPID
 left outer join t_MeasureUnit t512 on t13.FSecUnitID=t512.FItemID
 left outer join t_BaseBondedManageType t621 on v1.FManageType=t621.FID
 Inner Join t_SubMessage t123 on u1.FPlanMode=t123.FInterID
 left outer join t_SonCompany t550 on v1.FRelateBrID=t550.FItemID
 left outer join t_SonCompany t560 on v1.FBrID=t560.FItemID
 left outer join t_Submessage t632 on v1.FPurposeID=t632.FInterID
 left outer join ICStockBill TStockBill on u1.FInStockID=TStockBill.FInterID
 left outer join t_SubMessage tVMI on u1.FIsVMI=tVMI.FInterID
 left outer join t_Supplier tSup on u1.FEntrySupply=tSup.FItemID
 left outer join t_SubMessage t1107 on v1.FHeadSelfB0836=t1107.FInterID
 left outer join t_SubMessage t1108 on u1.FEntrySelfB0850=t1108.FInterID
 left Join
 (SELECT FSInterID,FSEntryID,Sum(FQty) as FQty,Sum(FAmount) as  FAmount
 FROM ICClientVer Group By FSInterID,FSEntryID) k1
 ON (v1.FInterID=k1.FSInterID AND u1.FEntryID=k1.FSEntryID)
 LEFT JOIN ICSubContractEntry t200 ON u1.FOrderInterID = t200.FInterID AND u1.FOrderEntryID = t200.FEntryID

 WHERE 1=1 AND v1.FTranType=28 And (v1.FStatus>=1  AND u1.FRowClosed<>1) And v1.FHookInterID=0
 --And v1.FDate<'2016/04/01'
 and u1.FQty<>IsNull(k1.FQty,0) AND ISNull(u1.FOrderInterID,0)=@FOrderInterID AND ISNULL(u1.FOrderEntryID,0)=@FOrderEntryID And v1.FTranType=28
 order by v1.FRob,v1.FDate ,v1.FBillNo,u1.FEntryID
 select * from #temp
set nocount off
end
 ----

insert into t_MenuToolBar ( FToolID,FName,FCaption,FCaption_CHT,FCaption_EN,FImageName,FToolTip,FToolTip_CHT,FToolTip_EN,FControlType,FVisible,FEnable,FChecked,FShortCut,FCBList,FCBList_CHT,FCBList_EN,FCBStyle,FCBWidth,FIndex,FToolCaption,FToolCaption_CHT,FToolCaption_EN)
values (10002,'FGDMenuBatchVeri','批量核销','批量核销','批量核销','39','批量核销','批量核销','批量核销',0,0,1,0,0,'','','',0,0,0,'批量核销','批量核销','批量核销')
--执行下面的代码才能在菜单上看到我们需要的效果，要不然，菜单是隐藏状态的(如果里面有"|V",则只能在后面加菜单项)，可支持多个自定义菜单
--iclisttemplate.fbilltemplateid=ictransactiontype.fid
 
 --H:ModifyQty,PlanConfirmed,UnPlanConfirmed,OrderAffirm,ViewRefuseInfo,OrderRefuse,OrderAppend,ViewContract,HookBill,UnHookBill,ReHookBill,UnReHookBill,Union,MakeDown,ReMakeDown,Complete,ReComplete,MakeMaterialGet,MakeLowerBills,CheckBOM,ViewMaterialDiff,ViewMaterial,LockStock,UnLockStock,Diminution,UnDiminution,ATPQuery,LockQuery|V:ItemConsume,ItemConsumeChange,SubsVer,UndoSubsVer,VerType|FModule:1089|FModelDetail:3
--begin tran
Update IclistTemplate set FLogicStr='H:ModifyQty,PlanConfirmed,UnPlanConfirmed,OrderAffirm,ViewRefuseInfo,OrderRefuse,OrderAppend,ViewContract,HookBill,UnHookBill,ReHookBill,UnReHookBill,Union,MakeDown,ReMakeDown,Complete,ReComplete,MakeMaterialGet,MakeLowerBills,CheckBOM,ViewMaterialDiff,ViewMaterial,LockStock,UnLockStock,Diminution,UnDiminution,ATPQuery,LockQuery|V:ItemConsume,ItemConsumeChange,SubsVer,UndoSubsVer,VerType,FGDMenuBatchVeri|FModule:1089|FModelDetail:3'
where FMenuID =82 and FID=5
--commit
--加到菜单头上，可以不用的
--select * from t_BandToolMapping where fid=82 and ftoolid=10002--外购入库
--delete from t_BandToolMapping where fid=82 and ftoolid=10002
--select b.fname,fcaption,a.*--该语句反应外购入库单的序时薄菜单信息
--from t_BandToolMapping a inner join t_MenuToolBar b on a.ftoolid=b.ftoolid
--where fid=82 --and  fcaption='序事簿菜单'--外购入口单叙事薄菜单

--begin tran
--update t_BandToolMapping set fbandid=2,fsubbandid=0,findex=17 where fid=82 and ftoolid=10002
--commit
--FBandID 是菜单的位置，1表示放到1级栏目下，2表示放在文件下面，3表示放在编辑下面，4表示放在查看下面，5表示放在格式下面，53表示放在工具栏上
--其他信息可以根据查询出来的数据进行判断
--insert into t_BandToolMapping(FID,FBandID,FToolID,FSubBandID,FIndex,FComName,FBeginGroup) 
--values(82,1,10002,6,6,'|ICMOListBill.ListBill',0)
----可以加到多个位置
----加到文件菜单下面
--insert into t_BandToolMapping(FID,FBandID,FToolID,FSubBandID,FIndex,FComName,FBeginGroup) 
--values(82,2,10002,0,17,'|ICMOListBill.ListBill',0) -
--加到工具栏下面 select * from t_BandToolMapping where fid=82 and fbandid=53 order by findex
insert into t_BandToolMapping(FID,FBandID,FToolID,FSubBandID,FIndex,FComName,FBeginGroup) 
values(82,53,10002,0,192,'|GDBatchVerification.List',0)