

/*

功能说明：      多账户写入listtable和扣减atype_total 

作    者：       冯波

创建日期：       2004-6-10

修改日期:		 2014-05-04

返回结果：  

描    述：
		--循环过账（要特别处理预收，预付，会员储值）
		--如果用会员储值的，还要对会员卡先进行判断，判断单张的金额是否足够，合计的金额是否够会员储值
		--因为可以多选帐户，所以需要合计后来判断     

*/

ALTER PROCEDURE [dbo].[GP_Auditing_MulAccount]
(
	@nBillID			NUMERIC(10,0),			--单据
	@intBillType		INT,
	@NewPosBill			TINYINT,
	@StypeID			VARCHAR(100),			--分支机构
	@strMsg				VARCHAR(200) OUTPUT
)
--@encode$--        
AS

/*

对VIP卡是否金额够的判断，需要全部改掉

对于成本价销售，还要特殊处理  GP_Auditing_SaleListTable

会员储值可以选多张会员卡，但积分是按表头的积分


*/

--没有多账户信息
IF NOT EXISTS(SELECT BillNumberID FROM BillMulAccount WHERE BillNumberID=@nBillID AND billtype=@intBillType)
BEGIN
	RETURN 0
END

DECLARE
		@PrePay_ID			varchar(50),@AAID			varchar(50)='999999',@AAID1			varchar(50)='888888',@AAID2			varchar(50)='777777',
		@PreGathering_ID	varchar(50),
		@VIPSaving_ID		varchar(50),
		@nRet				INT,
		@syscid				INT,
		@YhSaving_ID		varchar(50),
		@DJSaving_ID		varchar(50)
		
	SELECT @PrePay_ID = nId	FROM Ac_Items WHERE UserCode='1151' --预付账款
	SELECT @PreGathering_ID = nId  FROM Ac_Items WHERE UserCode='2131' --预收账款	
	SELECT @VIPSaving_ID = nId   FROM Ac_Items WHERE UserCode='2181' --会员储值
	SELECT  @YhSaving_ID= nId  FROM  Ac_Items WHERE UserCode='2191' --优惠券
	SELECT @DJSaving_ID=nId FROM Ac_Items WHERE UserCode='044597' --市场活动费用 处理代金券
	SELECT @AAID=nId FROM Ac_Items WHERE UserCode='999999' --市场活动费用 处理代金券
	SELECT @AAID1=nId FROM Ac_Items WHERE UserCode='888888' --市场活动费用 处理代金券
	
	SELECT @AAID2=nId FROM Ac_Items WHERE UserCode='777777' --市场活动费用 处理代金券
	
	SELECT @syscid=CId FROM Ac_Currency WHERE Sysrow = 1

	DECLARE
		@chvATypeID			VARCHAR(50),
		@numTotal			 NUMERIC(24, 10) ,
		@intReturnCode		INT,
		@datBillDate		DATETIME,
		@chvBTypeID			VARCHAR(50),
		@chvETypeID			VARCHAR(50),
		@chvDTypeID			VARCHAR(50),
		@chvSTypeID			VARCHAR(50),
		@CID				BIGINT,
		@Ntotal				 NUMERIC(24, 10) ,
		@NumRate			 NUMERIC(24, 10),
		@vipBillStoredByStypeId INT--会员储值结算使用单据所选分支机构进行储值余额判断
	
	SELECT @vipBillStoredByStypeId=Value FROM Userconfig WHERE ConfigName='vipBillStoredByStypeId'
		
		
	SELECT
			@datBillDate=BillDate,
			@chvBTypeID=BTypeID,
			@chvETypeID=etypeid,
			@chvDTypeID=DTypeId,
			@chvSTypeID=Stypeid,
			@CID=CID,
			@NumRate=Rate
	FROM BillIndex
	WHERE BillNumberId=@nBillID AND @intBillType NOT  IN  (423,424,425,426)

	SELECT 
			@datBillDate=BillDate,
			@chvBTypeID=BTypeID,
			@chvETypeID=etypeid,
			@chvDTypeID=DTypeId,
			@chvSTypeID=Stypeid,
			@CID=CID,
			@NumRate=Rate
	FROM dbo.Coupon_BillIndex
	WHERE BillNumberId=@nBillID AND @intBillType   IN  (423,424,425,426)
	
	DECLARE
		@intVIPCardID		INT,
		@chrIfVipJF			char(1),					-- 卡类型是否积分标识
		@intVipGridJF		int,						-- 单据GRID每行商品的积分
		@intVipJF			int,						-- 单据总积分
		@chvVipIsCz			varchar(1),					-- 是否VIP会员储值
		--@numVipTotalMoney	 NUMERIC(24, 10) ,				-- 单据消费金额 = 单据金额
		@numVipCzTotalInMoney  NUMERIC(24, 10) ,			-- 单据VIP会员储值对应的实收/付金额
		@numVipcZyeMoney	 NUMERIC(24, 10) ,				-- VIP会员储值余额
		@StypeVipcZyeMoney	 NUMERIC(24, 10) ,				-- VIP会员分支机构储值余额
		@Couponcode			VARCHAR(max),					--优惠券编码
		@yhtype				INT	,							--优惠券类型
		@VipTotal			NUMERIC(24, 10) 	
	DECLARE Account_CURSOR CURSOR FAST_FORWARD FOR
	SELECT	ATypeID,Total,NTotalMoney,VipCardID,CouponCode,yhType,VipTotal
	FROM	BillMulAccount
	WHERE	BillNumberID = @nBillID AND billtype=@intBillType
	ORDER BY ID

	OPEN Account_CURSOR
	FETCH NEXT FROM Account_CURSOR INTO	@chvATypeID,@numTotal,@ntotal,@intVipCardID,@Couponcode,@yhtype,@VipTotal
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--写入ListTable
		--要区分ATypeID和单据类型
		IF @intBillType IN (11,12,305,160,141,167,168,178,196,179,182,195,213,167,168,172,181,312,424,93,78,84,85,213,212,214) AND @chvATypeID IN (@PreGathering_ID,@VIPSaving_ID,@YhSaving_ID,@AAID) 
		BEGIN
			--只有为预收，会员储值时为负
			SELECT @numTotal=-@numTotal,@Ntotal = -@Ntotal
			--SELECT @chvATypeID, @numTotal 
		END
		ELSE IF @intBillType IN (84,85,78,213,212,214) AND @chvATypeID =@PrePay_ID
		BEGIN 
			SELECT @numTotal=-@numTotal,@Ntotal = -@Ntotal
		END 
		ELSE IF @intBillType IN (6,157)
		BEGIN
			--金额始终不变
			SELECT @numTotal=@numTotal,@Ntotal = @Ntotal
		END
		ELSE IF @intBillType IN (45,46,148,149,215,426) AND @chvATypeID NOT IN (@PreGathering_ID,@VIPSaving_ID,@YhSaving_ID,@AAID)
		BEGIN
			SELECT @numTotal=-@numTotal,@Ntotal = -@Ntotal
		END
		ELSE IF @intBillType IN (34,145,161,170,212,36,158,77,414,423,84)  --增加费用单,现金费用单
		BEGIN
			--始终金额为负
			SELECT @numTotal=-@numTotal,@Ntotal = -@Ntotal
		END
		
		--具体扣减余额
		IF @chvATypeID = @VIPSaving_ID
		BEGIN
			SELECT	
					@chvVipIsCz = a.IsCz,
					@numVipcZyeMoney = a.cZyeMoney,
					@StypeVipcZyeMoney = ISNULL(ss.cZyeMoney,0)
			FROM	nVipCardSign a
			INNER JOIN nVipCardSet b ON a.VipCardTypeID = b.VipCardTypeID AND a.VipCardID = @intVIPCardID AND a.Deleted = 0 AND B.deleted=0 AND a.EndDate >= @datBillDate
			LEFT JOIN nVipCardSignStype ss ON SS.VipCardID=A.VipCardID AND ss.StypeId=@StypeID
					
			-- 1)无此VIP卡 2)过了有效期 3)VIP卡是未启用状态 4)VIP卡类型不存在或已删除
			IF @@ROWCOUNT = 0 OR @@ERROR <> 0 
			BEGIN
				CLOSE Account_CURSOR
				DEALLOCATE Account_CURSOR
				RETURN 701
			END
			
			IF @chvVipIsCz <> '1' 
			BEGIN
				CLOSE Account_CURSOR
				DEALLOCATE Account_CURSOR
				RETURN 702
			END
			
			IF @numTotal > 0 AND @intBillType NOT IN (45,46,215)  
			BEGIN
				CLOSE Account_CURSOR
				DEALLOCATE Account_CURSOR
				RETURN 703
			END
			
			IF @intBillType IN (11,12,305,6,167,168,172) AND abs(@numTotal) > @numVipcZyeMoney AND @NewPosBill=0 
			BEGIN
				CLOSE Account_CURSOR
				DEALLOCATE Account_CURSOR
				RETURN 704
			END
			
			IF @vipBillStoredByStypeId=1 AND @intBillType IN (305,11,12,167,168,172,4) AND abs(@numTotal) > @StypeVipcZyeMoney AND @NewPosBill=0 
			BEGIN
				CLOSE Account_CURSOR
				DEALLOCATE Account_CURSOR
				RETURN 707
			END
			
			IF (@NewPosBill=0 OR @chvATypeID<>@VIPSaving_ID)
			BEGIN
			
				UPDATE	nVipCardSign  
				SET 
					cZyeMoney = cZyeMoney + @numTotal
				WHERE VipCardID = @intVipCardID
				
				IF EXISTS(SELECT TOP 1 1 FROM nVipCardSignStype WHERE VipCardID=@intVipCardID AND StypeId=@STypeID)
				BEGIN
					UPDATE dbo.nVipCardSignStype
					SET 
						cZyeMoney=cZyeMoney+@numTotal,
						lJxFczMoney=lJxFczMoney-@numTotal,
						bNxFczMoney=bNxFczMoney-@numTotal
					WHERE VipCardID=@intVIPCardID AND StypeId=@STypeID
				END
				ELSE
				BEGIN
					INSERT INTO dbo.nVipCardSignStype
					        ( VipCardID ,
					          StypeId ,
					          qCczMoney ,
					          lJczMoney ,
					          lJxFczMoney ,
					          bNczMoney ,
					          bNxFczMoney ,
					          cZyeMoney ,
					          lJxFMoney ,
					          bNxFMoney
					        )
					VALUES  ( @intVipCardID , -- VipCardID - int
					          @STypeID , -- StypeId - varchar(50)
					          0 , -- qCczMoney - numeric
					          0 , -- lJczMoney - numeric
					          -1*@numTotal , -- lJxFczMoney - numeric
					          0 , -- bNczMoney - numeric
					          -1*@numTotal , -- bNxFczMoney - numeric
					          @numTotal , -- cZyeMoney - numeric
					          0 , -- lJxFMoney - numeric
					          0  -- bNxFMoney - numeric
					        )
				END
			END
			 
			IF @numTotal<>0
			BEGIN
				EXEC @intReturnCode = GP_CreateMX_InsertList @nBillID,@datBillDate,@intBillType,@chvATypeID,@chvBTypeID,@chvETypeID,
					@numTotal,'',NULL,@chvDTypeID,@chvSTypeID,@syscid,@numTotal,1
			END
		END
		ELSE IF @chvATypeID = @YhSaving_ID
		BEGIN
			DECLARE @ceYhtotal NUMERIC(24, 10) ,@YhMztotal NUMERIC(24, 10)
		
			SELECT  @YhMztotal= CASE WHEN @intBillType IN (215,45,46) THEN @viptotal ELSE @viptotal*-1 END ,
					@ceYhtotal=-1*CASE WHEN ISNULL(ABS(@viptotal),0)>ABS(@numTotal) THEN SUM (ISNULL(ABS(@viptotal),0))-  ABS(@numTotal) 
									ELSE 0 
									END 
									
			SET  @ceYhtotal=CASE WHEN @intBillType IN (215,45,46) THEN @ceYhtotal*-1 ELSE @ceYhtotal END 
		
			IF @yhtype=1 ---出库 --代金券
			BEGIN
				--优惠券面值>结算金额值 差额写进项目 @DJSaving_ID
				IF @ceYhtotal<>0
				EXEC @intReturnCode = GP_CreateMX_InsertList @nBillID,@datBillDate,@intBillType,@DJSaving_ID,@chvBTypeID,@chvETypeID,
					@ceYhtotal,'',NULL,@chvDTypeID,@chvSTypeID,@CID,@ceYhtotal,@NumRate
			END 
			IF @YhMztotal<>0
			BEGIN
				EXEC @intReturnCode = GP_CreateMX_InsertList @nBillID,@datBillDate,@intBillType,@chvATypeID,@chvBTypeID,@chvETypeID,
					@YhMztotal,'',NULL,@chvDTypeID,@chvSTypeID,@CID,@Ntotal,@NumRate
			END
		
		END 
		ELSE
		BEGIN
			IF @numTotal<>0
			BEGIN
				EXEC @intReturnCode = GP_CreateMX_InsertList @nBillID,@datBillDate,@intBillType,@chvATypeID,@chvBTypeID,@chvETypeID,
					@numTotal,'',NULL,@chvDTypeID,@chvSTypeID,@CID,@Ntotal,@NumRate
			END
		END
		
		
		FETCH NEXT FROM Account_CURSOR INTO	@chvATypeID,@numTotal,@ntotal,@intVipCardID,@Couponcode,@yhtype,@VipTotal
	END
	CLOSE Account_CURSOR
	DEALLOCATE Account_CURSOR


GO


/*
	作者：谢吉康
	日期：2009-9-8
	描述：获取所有管辖现金银行-线性表
	9.7 增加预收/预付
*/
ALTER FUNCTION [dbo].[F_LineA]
(
	@chvOperatorId			varchar(100)
)
RETURNS @Temp TABLE(nTypeId varchar(100),nid INT)
--@Encode$--
AS
BEGIN
	IF EXISTS(SELECT TOP 1 1 FROM LoginUser WHERE ETypeId = @chvOperatorId AND MangerATypeID = '0000')
		INSERT INTO @Temp(nTypeId,nid) SELECT nTypeID,nId FROM Ac_Items WHERE SonNum = 0 
		AND (TypeName='B' OR UserCode IN('1151','2131','2191') or UserCode='999999' or UserCode='888888' or  UserCode='777777') --AND (nTypeId LIKE '0000100004%' OR nTypeId = '0000100003')
	ELSE	
		INSERT INTO @Temp(nTypeId,nid) 
		SELECT DISTINCT nTypeId,nId FROM(
			SELECT	a.nTypeId ,a.nId
			FROM	AType_L L
			INNER JOIN  Ac_Items a ON ( (L.flag=0 AND a.nTypeId=L.typeid) OR (L.Flag=1 AND a.nTypeId LIKE L.TypeId + '%'))
			WHERE	L.etypeid = @chvOperatorId
					AND L.type = '0' 
					AND A.sonnum = 0
					AND (TypeName='B' OR UserCode IN ('1151','2131'))
			UNION ALL
			SELECT nTypeId,nId FROM Ac_Items WHERE UserCode in('2191') AND Sysrow=1 or UserCode='999999' or UserCode='888888' or UserCode='777777'
		) F
	RETURN
END




GO