--V2.5 sql 18.0.111.1513

--是否启用会员赠送金额
if not exists(select * from T_SYSTEM where F_ID = 'EnableClientPresentMoney')
BEGIN
  INSERT INTO T_SYSTEM (F_ID, F_VALUE, F_REMARK, F_TYPE) VALUES ('EnableClientPresentMoney', '0', '是否启用会员赠送金额', '会员');  
END
GO

--会员赠送金额是否只允许本店消费
if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsClientPresentMoneyOnlyUsedCurShop') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE
) VALUES ('IsClientPresentMoneyOnlyUsedCurShop', '0', NULL, '会员赠送金额是否只允许本店消费', 0, '会员','18.0.110','2017-06-26');
END

GO

--会员加入赠送金额字段
IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_CLIENT') and Name='F_PRESENTMONEY')
BEGIN  
        ALTER TABLE T_CLIENT ADD F_PRESENTMONEY decimal(9,2) NOT NULL DEFAULT 0 
END

GO

--V_CLIENT视图加入赠送金额字段
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_CLIENT')
BEGIN   
  DROP View V_CLIENT;
END

GO

CREATE VIEW [dbo].[V_CLIENT] AS 
SELECT
	T_CLIENT1.F_AUTOID,
	T_CLIENT1.F_ID,
	T_CLIENT1.F_NAME,
	T_CLIENT1.F_NAMEABC,
	T_CLIENT1.F_SEX,
	T_CLIENT1.F_TELEPHONE,
	T_CLIENT1.F_ADDRESS,
	T_CLIENT1.F_CANCOSTMONEY,
	T_CLIENT1.F_REBATE,
	T_CLIENT1.F_REMARK,
	T_CLIENT1.F_PASSWORD,
	T_CLIENT1.F_DATE,
	T_CLIENT1.F_BORNDATE,
	T_CLIENT1.F_GOODSREBATE,
	T_CLIENT1.F_WORKERID,
	T_WORKER.F_NAME AS F_WORKERNAME,
	T_CLIENT1.F_CARDTYPEID,
	T_CARDTYPE.F_NAME AS F_CARDTYPENAME,
	T_CLIENT1.F_SERVEREBATE,
	T_CLIENT1.F_INTEGRAL,
	T_CLIENT1.F_TRANSMITGROUP,
	T_CLIENT1.F_STARTDATE,
	T_CLIENT1.F_ENDDATE,
	T_CLIENT1.F_FIRSTDATE,
	T_CLIENT1.F_LASTDATE,
	T_CLIENT1.F_INTRODUCERID,
	T_CLIENT.F_NAME AS F_INTRODUCERNAME,
	T_CLIENT1.F_STATE,
	T_CLIENT1.F_HAIRMASS,
	T_CLIENT1.F_ANAPHYLAXIS,
	T_CLIENT1.F_PHOTOPATH,
	T_CLIENT1.F_MOVETEL,
	T_CLIENT1.F_SHOPID,
	T_CLIENT1.F_SOURCEID,
	T_SOURCE.F_NAME AS F_SOURCENAME,
	T_CLIENT1.F_AREAID,
	T_AREA.F_NAME AS F_AREANAME,
	T_CLIENT1.F_LUNARYBORNDATE,
	T_CLIENT1.F_LASTVISITCONNECT,
	T_CLIENT1.F_ARREARAGEMONEY,
	T_CLIENT1.F_GOODSREBATE1,
	T_CLIENT1.F_SERVEREBATE1,
	T_CLIENT1.F_SERVEREBATE2,
	T_CLIENT1.F_SERVEREBATE3,
	T_CLIENT1.F_ISSMSAWOKE,
	T_CLIENT1.F_MASTERPOINT,
	T_CLIENT1.F_COSTARPOINT,
	T_CLIENT1.F_ADJUSTPOINT,
	T_CLIENT1.F_ADVANCEDPOINT,
	T_CLIENT1.F_SUPPLEMENTTOTALMONEY,
	T_CLIENT1.F_SUPPLEMENTTOTALTIME,
	T_CLIENT1.F_CONSUMETOTALMONEY,
	T_CLIENT1.F_CONSUMETOTALTIME,
	T_CLIENT1.F_CREDITMONEY,
	T_CLIENT1.F_CANDEBTMONEY,
	T_CLIENT1.F_INTERNALCARDID,
	T_CARDTYPE.F_ISVIPPRICE,
	T_CARDTYPE.F_CANGOODSTYPE AS F_CANGODDSTYPE,
  right(CONVERT(varchar,T_CLIENT1.F_BornDate,112),4) AS F_BORNDATEMMDD,
  right(CONVERT(varchar,T_CLIENT1.F_LunaryBornDate,112),4)  AS F_LUNARYBORNDATEMMDD,
	T_CLIENT1.F_LASTSUPPLEMENTDATETIME,
	T_CLIENT1.F_QQ,
	T_CLIENT1.F_CARID,
	T_CLIENT1.F_LIMITMONEY,
	T_CLIENT1.F_ISSMSMONTHFEE,
	T_CLIENT1.F_LASTMONTHFEEDATETIME,
	T_CLIENT1.F_IsAllowWeiXinCheckConsume,
	T_CLIENT1.F_PRESENTMONEY
FROM
	T_CLIENT AS T_CLIENT1
LEFT OUTER JOIN T_CARDTYPE ON T_CLIENT1.F_CARDTYPEID = T_CARDTYPE.F_ID
LEFT OUTER JOIN T_WORKER ON T_CLIENT1.F_WORKERID = T_WORKER.F_ARCHIVESID
LEFT OUTER JOIN T_CLIENT ON T_CLIENT1.F_INTRODUCERID = T_CLIENT.F_ID
LEFT OUTER JOIN T_SOURCE ON T_CLIENT1.F_SOURCEID = T_SOURCE.F_ID
LEFT OUTER JOIN T_AREA ON T_CLIENT1.F_AREAID = T_AREA.F_ID

GO


IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_SUPPLEMENT') and Name='F_PRESENTMONEY')
BEGIN  
        ALTER TABLE T_SUPPLEMENT ADD F_PRESENTMONEY decimal(9,2) NOT NULL DEFAULT 0 
END

GO

IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_SUPPLEMENT_H') and Name='F_PRESENTMONEY')
BEGIN  
        ALTER TABLE T_SUPPLEMENT_H ADD F_PRESENTMONEY decimal(9,2) NOT NULL DEFAULT 0 
END

GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_SUPPLEMENT')
BEGIN   
  DROP View V_SUPPLEMENT;
END

GO


CREATE VIEW [dbo].[V_SUPPLEMENT]
AS
SELECT     dbo.T_SUPPLEMENT.F_ID, dbo.T_SUPPLEMENT.F_CLIENTID, dbo.T_CLIENT.F_NAME, dbo.T_SUPPLEMENT.F_DATETIME, dbo.T_SUPPLEMENT.F_MONEY, 
                      dbo.T_SUPPLEMENT.F_CANCOSTMONEY, dbo.T_SUPPLEMENT.F_REMARK, dbo.T_SUPPLEMENT.F_USERID, dbo.T_SUPPLEMENT.F_SHOPID, 
                      dbo.T_SUPPLEMENT.F_WORKERID, dbo.T_WORKER.F_NAME AS F_WORKERNAME, dbo.T_SUPPLEMENT.F_CARDTYPEID, 
                      dbo.T_CARDTYPE.F_NAME AS F_CARDTYPENAME, dbo.T_SUPPLEMENT.F_DEDUCT, dbo.T_SUPPLEMENT.F_TRANSMITGROUP, dbo.T_SUPPLEMENT.F_TYPE, 
                      dbo.T_SUPPLEMENT.F_CLIENTCANCOSTMONEY, dbo.T_SUPPLEMENT.F_SUPPLEMENTTYPE, dbo.T_SUPPLEMENT.F_WORKERDEDUCT, 
                      dbo.T_SUPPLEMENT.F_FACTMONEY, dbo.T_SUPPLEMENT.F_CREDITMONEY, dbo.T_CLIENT.F_SHOPID AS F_CLIENTSHOPID, dbo.T_SUPPLEMENT.F_CARDMONEY, 
                      dbo.T_SUPPLEMENT.F_CARDCANCOSTMONEY, dbo.T_SUPPLEMENT.F_UNSUPPLEMENTIID, dbo.T_SUPPLEMENT.F_INTEGRAL, 
                      dbo.T_SUPPLEMENT.F_TRADENO, dbo.T_CLIENT.F_MOVETEL,dbo.T_SUPPLEMENT.F_PRESENTMONEY
FROM         dbo.T_SUPPLEMENT INNER JOIN
                      dbo.T_CLIENT ON dbo.T_SUPPLEMENT.F_CLIENTID = dbo.T_CLIENT.F_ID LEFT OUTER JOIN
                      dbo.T_WORKER ON dbo.T_SUPPLEMENT.F_CLIENTID = dbo.T_WORKER.F_ARCHIVESID LEFT OUTER JOIN
                      dbo.T_CARDTYPE ON dbo.T_SUPPLEMENT.F_CARDTYPEID = dbo.T_CARDTYPE.F_ID
GO


IF NOT EXISTS(
   SELECT * FROM T_CHECKOUTTYPE WHERE F_ID = 'W23')
BEGIN  
   INSERT T_CHECKOUTTYPE
           ( F_ID ,
             F_NAME ,
             F_OrderID ,
             F_IsStop ,
             F_IsSupplent ,
             F_IsCheckout ,
             F_IsNeedAuthorize
           )
   VALUES  ( 'W23' , -- F_ID - varchar(10)
             '储值卡赠送' , -- F_NAME - varchar(20)
             '9999' , -- F_OrderID - varchar(10)
             0 , -- F_IsStop - smallint
             0 , -- F_IsSupplent - smallint
             0 , -- F_IsCheckout - smallint
             0
           )
END
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_SUPPLEMENTPAYMENT')
BEGIN   
  DROP View V_SUPPLEMENTPAYMENT;
END

GO

CREATE VIEW [dbo].[V_SUPPLEMENTPAYMENT](
    "F_CLIENTID",
    "F_NAME",
    "F_DATETIME",
    "f_MONEYSUM",
    "F_CANCOSTMONEY",
    "F_USERID",
    "F_SHOPID",
    "F_WORKERID",
    "F_WORKERNAME",
    "F_CARDTYPEID",
    "F_CARDTYPENAME",
    "F_REMARK",
    "F_DEDUCT",
    "F_TRANSMITGROUP",
    "F_TYPE",
    "F_CLIENTCANCOSTMONEY",
    "F_SUPPLEMENTTYPE",
    "F_WORKERDEDUCT",
    "F_FACTMONEY",
    "F_CREDITMONEY",
    "F_CLIENTSHOPID",
    "F_CARDMONEY",
    "F_CARDCANCOSTMONEY",
    "F_UNSUPPLEMENTIID",
    "F_INTEGRAL",
    "F_SUPPLEMENTID",
    "F_PAYMENTTYPE",
    "F_MONEY",
    "F_TRADENO",
    "F_MOVETEL","F_PRESENTMONEY")
AS
select
  V_SUPPLEMENT.F_CLIENTID,
  V_SUPPLEMENT.F_NAME,
  V_SUPPLEMENT.F_DATETIME,
  V_SUPPLEMENT.F_MONEY AS f_MONEYSUM,
  V_SUPPLEMENT.F_CANCOSTMONEY,
  V_SUPPLEMENT.F_USERID,
  V_SUPPLEMENT.F_SHOPID,
  V_SUPPLEMENT.F_WORKERID,
  V_SUPPLEMENT.F_WORKERNAME,
  V_SUPPLEMENT.F_CARDTYPEID,
  V_SUPPLEMENT.F_CARDTYPENAME,
  V_SUPPLEMENT.F_REMARK,
  V_SUPPLEMENT.F_DEDUCT,
  V_SUPPLEMENT.F_TRANSMITGROUP,
  V_SUPPLEMENT.F_TYPE,
  V_SUPPLEMENT.F_CLIENTCANCOSTMONEY,
  V_SUPPLEMENT.F_SUPPLEMENTTYPE,
  V_SUPPLEMENT.F_WORKERDEDUCT,
  V_SUPPLEMENT.F_FACTMONEY,
  V_SUPPLEMENT.F_CREDITMONEY,
  V_SUPPLEMENT.F_CLIENTSHOPID,
  V_SUPPLEMENT.F_CARDMONEY,
  V_SUPPLEMENT.F_CARDCANCOSTMONEY,
  V_SUPPLEMENT.F_UNSUPPLEMENTIID,
  V_SUPPLEMENT.F_INTEGRAL,
  T_SUPPLEMENTPAYMENT.F_SUPPLEMENTID,
  T_SUPPLEMENTPAYMENT.F_TYPE AS F_PAYMENTTYPE,
  T_SUPPLEMENTPAYMENT.F_MONEY,
  V_SUPPLEMENT.F_TRADENO,
  V_SUPPLEMENT.F_MOVETEL ,
  V_SUPPLEMENT.F_PRESENTMONEY
from T_SUPPLEMENTPAYMENT
INNER JOIN V_SUPPLEMENT ON (T_SUPPLEMENTPAYMENT.F_SUPPLEMENTID = V_SUPPLEMENT.F_ID)
; 

GO