/****** Object:  Table [dbo].[DJBHWH_ERP_LQ]    Script Date: 2024-11-08 17:51:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DJBHWH_ERP_LQ]') AND type in (N'U'))
DROP TABLE [dbo].[DJBHWH_ERP_LQ]
GO

/****** Object:  Table [dbo].[DJBHWH_ERP_LQ]    Script Date: 2024-11-08 17:51:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DJBHWH_ERP_LQ](
	[BMFS] [varchar](50) NOT NULL,
	[DJMC] [varchar](50) NOT NULL,
	[DM] [varchar](50) NOT NULL,
	[DJH] [int] NULL,
	[BYZD1] [varchar](20) NULL,
	[BYZD2] [varchar](20) NULL,
	[BYZD3] [numeric](18, 4) NULL,
	[BYZD4] [numeric](18, 4) NULL,
 CONSTRAINT [PK_DJBHWH_ERP_LQ] PRIMARY KEY NONCLUSTERED 
(
	[BMFS] ASC,
	[DJMC] ASC,
	[DM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



INSERT INTO [DJBHWH_ERP_LQ] ([BMFS], [DJMC], [DM], [DJH], [BYZD1], [BYZD2], [BYZD3], [BYZD4]) VALUES ('LIQUAN', 'LIQUAN', 'XSQ', 1, NULL, NULL, NULL, NULL);



/****** Object:  StoredProcedure [dbo].[P_SendCoupon]    Script Date: 2024-11-08 18:07:04 ******/
DROP PROCEDURE [dbo].[P_SendCoupon]
GO

/****** Object:  StoredProcedure [dbo].[P_SendCoupon]    Script Date: 2024-11-08 18:07:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


 
CREATE proc [dbo].[P_SendCoupon]
               @DJBH varchar(20), --单据编号
               @Cards VARCHAR(MAX)
AS
DECLARE @StartDate DATETIME, @EndDate DATETIME, @YXQX INT, @YXQFS INT;
                    SELECT  @DJBH=a.DJBH,@StartDate = a.RQ_S, @EndDate = a.RQ_E, @YXQX = a.YXQX, @YXQFS = a.ZDYXQFS
                    FROM dbo.LQZCD a WITH (NOLOCK)
                    WHERE a.DJBH = @DJBH;
DECLARE @TabCoupn Table
(
	Id int,
	VPDM VARCHAR(50),
	GKDM VARCHAR(50),
	LQBH VARCHAR(50),
	CODE VARCHAR(2),
	MSG VARCHAR(100)
);
BEGIN
	IF @DJBH=''
    GOTO FAIL
    DECLARE @ERROR INT =0;
	--生成随机数
	DECLARE @CODE VARCHAR(50);
	SELECT  @CODE=NEWID();
    BEGIN TRAN
	DECLARE @DJH INT=1,@DM VARCHAR(20)='',@LQBHMAX int;
	SELECT @DJH=DJH,@DM=DM FROM DJBHWH_ERP_LQ WHERE BMFS='LIQUAN'
	SELECT  @LQBHMAX=MAX( CONVERT(INT, REPLACE(LQBH,@DM,'')) )  FROM LIQUAN WHERE LQBH LIKE @DM+'%'  
	
	IF @LQBHMAX>@DJH
    BEGIN

    UPDATE DJBHWH_ERP_LQ SET  DJH=@LQBHMAX  WHERE BMFS='LIQUAN'  
    SELECT @DJH=DJH,@DM=DM FROM DJBHWH_ERP_LQ WHERE BMFS='LIQUAN'
  
   END
 
	
	--插入数据到临时表
	INSERT INTO @TabCoupn(VPDM,Id,CODE) SELECT *,'0' FROM FT_GetSplitTable(@Cards);
	--券号15位 固定前缀@DM
	UPDATE A SET A.CODE='1',LQBH=@DM+SUBSTRING('000000000000',1,12-LEN(CAST(ID+@DJH AS VARCHAR(20))))+ CAST(ID+@DJH AS VARCHAR(20)),GKDM=B.GKDM
	FROM @TabCoupn A INNER JOIN V_VIPSET B ON A.VPDM=B.DM
	-- 过滤调VPDM不存在的数据
	UPDATE A SET CODE='9',MSG='VIP不存在' FROM @TabCoupn A WHERE CODE='0';
	 SET @ERROR+=@@ERROR
	--数据发券数量
	DECLARE @CouponNum INT=0;
    SELECT  @CouponNum=COUNT(1) FROM @TabCoupn WHERE CODE='1';
	--更新流水号表发券标记
	UPDATE DJBHWH_ERP_LQ SET DJH=DJH+@CouponNum WHERE  BMFS='LIQUAN'
	--插入礼券制成单明细表
    INSERT INTO LQZCDMX(DJBH, LQBH, STATE)
    SELECT @DJBH,LQBH,'2' FROM @TabCoupn
    SET @ERROR+=@@ERROR
	--插入会员表
    INSERT INTO wx_user_coupon(lqbh, cardno, createddate)
    SELECT LQBH,VPDM,GETDATE() FROM @TabCoupn A WHERE CODE='1'
    SET @ERROR+=@@ERROR
    --插入礼券表
    DECLARE @QDDM NVARCHAR(100),@CKDM NVARCHAR(100),@SJ NVARCHAR(20)
    IF @YXQFS = 2
    BEGIN
        SET @StartDate = GETDATE();
        SET @EndDate = DATEADD(DAY, -1, DATEADD(MONTH, @YXQX, @StartDate));
    END;
    IF @YXQFS = 3
    BEGIN
        SET @StartDate = GETDATE();
        SET @EndDate = DATEADD(DAY, @YXQX - 1, @StartDate);
    END;
    INSERT INTO LIQUAN ( LQBH,LBDM,QDDM,CKDM,QYBJ,TYBJ,ZS,XFJE,BZJE,FSJE,SYJE,BYZD1,RQ_S,RQ_E,status,LQLX,LQZK,LQTOWM,SPZK,VPDM,SPFW)
    SELECT  A.LQBH,LQZCD.LBDM, B.QDDM,B.CKDM,'1','0','0',LQLB.XFJE,LQZCD.BZJE,LQLB.FSJE,LQLB.FSJE ,ISNULL(LQZCD.BYZD1,'0'),
                    CONVERT(VARCHAR(50), @StartDate, 23) + ' 00:00:00' ,
                    CONVERT(VARCHAR(50), @EndDate, 23) + ' 23:59:59' ,
                    '1' ,
                    LQLB.LQLX ,
                    LQZCD.lqzk,
                    '1' ,LQZCD.SPZK,A.VPDM,SPFW
            FROM    LQZCD
                    INNER JOIN LQLB ON LQLB.LBDM = LQZCD.LBDM
					INNER JOIN @TabCoupn A ON A.CODE='1'
					INNER JOIN V_VIPSET B WITH (NOLOCK) ON A.VPDM=B.DM
            WHERE   LQZCD.DJBH=@DJBH 
    SET @ERROR+=@@ERROR
    IF (@ERROR>0)
    BEGIN
    ROLLBACK TRAN
    GOTO FAIL 
    END
    ELSE
    BEGIN
    COMMIT;
    GOTO OK
    END 
    FAIL:
    SELECT CODE,LQBH,VPDM,GKDM,MSG,@StartDate StartDate,@EndDate EndDate,@DJBH DJBH FROM @TabCoupn;
    RETURN;
    OK:
    SELECT CODE,LQBH,VPDM,GKDM,MSG,@StartDate StartDate,@EndDate EndDate,@DJBH DJBH FROM @TabCoupn ;
END
GO



DROP PROCEDURE [dbo].[xb_sjsx]
GO

/****** Object:  StoredProcedure [dbo].[xb_sjsx]    Script Date: 2024-11-08 18:07:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
















--零售销货单满额赠券
CREATE PROCEDURE [dbo].[xb_sjsx] @dh varchar(100) --零售单号
AS
declare @rq varchar(100)
declare @dp varchar(50)
declare @je numeric(18, 4)
declare @zcdh1 varchar(50)
declare @zcdh2 varchar(50)
declare @zcdh3 varchar(50)
declare @djbh varchar(100)
declare @kbcxdh varchar(100)
declare @Sql VARCHAR(MAX)
declare @sql1 VARCHAR(MAX)
declare @hdje numeric(18, 4)
declare @lqzs int
declare @i int
declare @j int
declare @k int
declare @vpdm varchar(50)
    IF EXISTS(SELECT 1
              FROM LSXHD
              WHERE XPBH = @DH)
        BEGIN
            begin
                set @i = 1 --制成单1变量
                set @j = 1 --制成单2变量
                set @k = 1 --制成单3变量
                set @zcdh1 = 'LPH0000041' --制成单1-800-200，11.08-11.12   312门店,409门店
                set @zcdh2 = 'LPH00000027' --制成单2----------------
				set @zcdh3 = 'LPH00000028' --制成单3----------------
                set @hdje = '800' --活动金额

                select @rq = CONVERT(varchar(100), rq, 23), @dp = dm1, @djbh = DJBH ,@vpdm=dm6
                from LSXHD
                where XPBH = @dh --取日期，店铺，零售单号
                select @je = SUM(je) from LSXHDJS where djbh = @djbh and JSFS not in ('001', '005', '066') --查有效金额
                set @lqzs = @je / @hdje
                if not exists(select 1 from lsxhdjs where djbh = @djbh and JSFS = '001') --有礼券不参与返券
                    begin
                        if @dp in (select sddm from lqzcdfw where djbh = @zcdh1) and @rq >= '2024-11-08' and
                           @rq <= '2024-11-12'
                            ---判断店铺和日期是否符合-礼券制成单1   exec P_SendCoupon @zcdh1 ,@vpdm
                            begin

 							DECLARE @zs INT = 1;
							WHILE @zs <= @lqzs
							BEGIN
							exec P_SendCoupon @zcdh1 ,@vpdm
							SET @zs = @zs + 1;
							END

                            end
						else
						if @dp in (select sddm from lqzcdfw where djbh = @zcdh2) and @rq >= '2023-06-17' and
                           @rq <= '2023-06-28'
                            ---判断店铺和日期是否符合-礼券制成单2
                            begin
                               select 1

                            end
                        else
                        if @dp in (select sddm from lqzcdfw where djbh = @zcdh3) and @rq >= '2023-06-22' and
                           @rq <= '2023-06-29'
                            ---判断店铺和日期是否符合-礼券制成单3
                            begin
                                select 1

                            end
                    end
            END
            print (@sql)
            EXEC (@sql)
        end

GO




/****** Object:  StoredProcedure [dbo].[P_API_IPOS_SAVE_ZP]    Script Date: 2024-11-08 18:09:48 ******/
DROP PROCEDURE [dbo].[P_API_IPOS_SAVE_ZP]
GO

/****** Object:  StoredProcedure [dbo].[P_API_IPOS_SAVE_ZP]    Script Date: 2024-11-08 18:09:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[P_API_IPOS_SAVE_ZP] (
	@XPBH VARCHAR (50) -- 小票号
) AS
BEGIN

DECLARE @XHDH VARCHAR(50)=''; -- 零售销货单单号
DECLARE @THDH VARCHAR(50)=''; -- 零售退货单单号
DECLARE @ERRMSG NVARCHAR(4000) = N'',@ERRS INT
SET @ERRS=0
SELECT @XHDH=DJBH FROM LSXHD WITH(NOLOCK) WHERE XPBH=@XPBH
SELECT @THDH=DJBH FROM LSTHD WITH(NOLOCK) WHERE XPBH=@XPBH AND @XHDH=''

IF @XHDH='' AND @THDH=''
BEGIN
  SELECT 0
  RETURN
END

BEGIN TRAN
BEGIN TRY 
IF @XHDH <> ''
BEGIN
  INSERT INTO LSXHDZP (DJBH,SPDM,GG1DM,GG2DM,SL,SL_1,CKJ,ZK,DJ,DJ_1,JE,JE_1,BYZD1,BYZD2,BYZD3,BYZD4,BYZD5,BYZD6,BYZD7,BYZD8,BZ,DJ_3) 
  SELECT DJBH,SPDM,GG1DM,GG2DM,SL,SL_1,CKJ,ZK,DJ,DJ_1,JE,JE_1,BYZD1,BYZD2,BYZD3,BYZD4,XPBH,BYZD6,BYZD7,BYZD8,BZ,DJ_3
  FROM LSXHDMX WITH(NOLOCK) WHERE DJBH = @XHDH AND SP_Status=3 
  SET @ERRS=@ERRS+@@ERROR
  DELETE FROM LSXHDMX WHERE DJBH = @XHDH AND SP_Status=3 
  SET @ERRS=@ERRS+@@ERROR
END
ELSE IF @THDH <> ''
BEGIN
  INSERT INTO LSTHDZP (DJBH,SPDM,GG1DM,GG2DM,SL,SL_1,CKJ,ZK,DJ,DJ_1,JE,JE_1,BYZD1,BYZD2,BYZD3,BYZD4,BYZD5,BYZD6,BYZD7,BYZD8,BZ,DJ_3) 
  SELECT DJBH,SPDM,GG1DM,GG2DM,SL,SL_1,CKJ,ZK,DJ,DJ_1,JE,JE_1,BYZD1,BYZD2,BYZD3,BYZD4,XPBH,BYZD6,BYZD7,BYZD8,BZ,DJ_3
  FROM LSTHDMX WITH(NOLOCK) WHERE DJBH = @THDH AND SP_Status=3 
  SET @ERRS=@ERRS+@@ERROR
  DELETE FROM LSTHDMX WHERE DJBH = @THDH AND SP_Status=3 
  SET @ERRS=@ERRS+@@ERROR
END

EXEC xb_sjsx @XPBH

END TRY    

BEGIN CATCH    
    SET @ERRMSG = N'出现异常, 错误编号: ' + CONVERT(varchar, ERROR_NUMBER()) 
				+ N', 错误行号：' + CONVERT(varchar, ERROR_LINE()) 
				+ N', 错误消息：'    + ERROR_MESSAGE();    
    SET @ERRS=@ERRS+@@ERROR
END CATCH; 

IF @ERRS=0
BEGIN
 COMMIT TRAN
 SELECT 0
END
ELSE BEGIN
 ROLLBACK TRAN
 SELECT -1
END
   
IF @ERRMSG<>''
BEGIN
	INSERT INTO dbo.ProERRMSG( ProName ,FMID ,ERRMSG,BYZD1 )
	VALUES  ('P_API_IPOS_SAVE_ZP' ,@XPBH ,ISNULL(@ERRMSG,''),'')
	RETURN;
END
END	 
GO