SELECT * FROM TMember 
SELECT * FROM TMGWMember 
WHERE 当前状态 

SELECT * FROM  TMGWMember 
WHERE 1=1
 --AND 表面号 = ''
 --AND FDeleted = 0
 AND 会员生日!= '1月-1日[公历]'
 
WHERE 当前状态 = '注销'
SELECT 当前状态 FROM TMGWMember
GROUP BY 当前状态


--
IF OBJECT_ID('TMGWCompany') IS NULL
BEGIN
CREATE TABLE TMGWCompany
(
    FCompName VARCHAR(50),
    FCompNo VARCHAR(50),
    FCompId BIGINT
)
END

SELECT FName FROM TCompany

SELECT 店面 FROM TMGWMember 
group by 店面

DELETE TMGWCompany

INSERT TMGWCompany(FCompName,FCompNo)
SELECT '不见不散（公园店）','001'

INSERT TMGWCompany(FCompName,FCompNo)
SELECT '不见不散（向阳路店）','002'

INSERT TMGWCompany(FCompName,FCompNo)
SELECT '不见不散（大崂路店）','003'

UPDATE A 
   SET A.FCompId = B.FId
 FROM TMGWCompany AS A,TCompany AS B
 WHERE A.FCompNo = B.FNo
 
--
ALTER TABLE TMGWMember ADD FDeleted BIT
ALTER TABLE TMGWMember ADD FState INT
ALTER TABLE TMGWMember ADD FMemId BIGINT
ALTER TABLE TMGWMember ADD FMemNo  VARCHAR(50)
ALTER TABLE TMGWMember ADD FName VARCHAR(50)
ALTER TABLE TMGWMember ADD FMobilePhone VARCHAR(50)
ALTER TABLE TMGWMember ADD FBirthday DATETIME
ALTER TABLE TMGWMember ADD FBirthdayType CHAR
ALTER TABLE TMGWMember ADD FCompId BIGINT
ALTER TABLE TMGWMember ADD FMemo VARCHAR(2222)
ALTER TABLE TMGWMember ADD FCreateDate DATETIME
ALTER TABLE TMGWMember ADD FPoint DECIMAL(18,6)

--
UPDATE TMGWMember SET FDeleted = 1 WHERE 当前状态 = '注销'
UPDATE TMGWMember SET FDeleted = 1 WHERE 当前状态 = '退卡'
UPDATE TMGWMember SET FDeleted = 0 WHERE 当前状态 = '正常'
UPDATE TMGWMember SET FDeleted = 0 WHERE FDeleted IS NULL


SELECT * FROM TMGWMember WHERE 当前状态 = '挂失'


SELECT 当前状态 FROM TMGWMember
GROUP BY 当前状态


--会员ID
UPDATE TMGWMember SET FMemId = dbo.FNNewId() WHERE FMemId IS NULL
--会员主卡号
UPDATE TMGWMember SET FMemNo = 表面号
--姓名
UPDATE TMGWMember SET FName = 会员姓名
--手机
UPDATE TMGWMember SET FMobilePhone = 联系电话
--开户时间
UPDATE TMGWMember SET FCreateDate = 开户时间
--状态
UPDATE TMGWMember SET FState = 1 WHERE 当前状态='正常'
UPDATE TMGWMember SET FState = 5 WHERE 当前状态='挂失'
UPDATE TMGWMember SET FState = 1 WHERE 当前状态='过期'

SELECT * FROM TMGWMember WHERE 当前状态 = '挂失'

--备注
UPDATE TMGWMember SET FMemo = 备注信息
--积分
UPDATE TMGWMember SET FPoint = 当前积分
--公司
UPDATE A
   SET A.FCompId = B.FCompId
  FROM TMGWMember AS A,TMGWCompany AS B
 WHERE A.店面 = B.FCompName
 
 --生日
 UPDATE TMGWMember SET FBirthdayType = 'G' WHERE 会员生日 LIKE '%公历%'
 UPDATE TMGWMember SET FBirthdayType = 'L' WHERE 会员生日 LIKE '%农历%'
 UPDATE A SET FBirthday = 
 
  -- SELECT
     CAST( '9999-' + 
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(会员生日,'月',''),'日',''),'[农历]',''),'[公历]','') AS DATETIME)
                    FROM TMGWMember AS A
WHERE 会员生日 LIKE '%公历%'
AND 会员生日 != '1月-1日[公历]'

--删除重复
UPDATE A
  SET FDeleted = 1
  
  FROM TMGWMember AS A,(
SELECT FMemNo,MIN(FCreateDate) AS FCreateDate
FROM TMGWMember
WHERE FMemNo IN
(
SELECT FMemNo FROM TMGWMember WHERE FDeleted = 0  GROUP BY FMemNo HAVING(COUNT(1) > 1)
)
GROUP BY FMemNo
) AS B
WHERE A.FMemNo = B.FMemNo
AND A.FCreateDate = B.FCreateDate


-----导入基本资料
ALTER TABLE TMember ADD FData VARCHAR(1000)

SELECT * FROM TMGWMember WHERE FState IS NULL


INSERT INTO TMember(
    FMemId,
    FMemNo,
    FName,
    FBriefCode,
    FTypeId,
    FCompId,
    FCreateDate,
    FGender,
    FBirthdayType,
    FBirthday,
    FTelephone,
    FMobilePhone,
    FQQ,
    FWeChat,
    FIdCard,
    FEmail,
    FAddress,
    FProId,
    FCPW,
    FQPW,
    FChargeEmpId,FPoint,FRecvConsSMS,FRecvBulkSMS,FMemo,FCustom1,FCustom2,FCustom3,FCustom4,FCustom5,FState,FData)
SELECT FMemId AS FMemId,
      FMemNo AS FMemNo,
      FName AS FName,
      dbo.FNGetFirstPinYin(FName) AS FBriefCode,
      0 AS FTypeId,
      FCompId AS FCompId,
      FCreateDate AS FCreateDate,
      '0' AS FGender,
      FBirthdayType AS FBirthdayType,
      FBirthday AS FBirthday,
      '' AS FTelephone,
      FMobilePhone AS FMobilePhone,
       '' AS FQQ,
       '' AS FWeChat,
       '' AS FIdCard,
       '' AS FEmail,
       '' AS FAddress,
       0 AS FProId,
       '' AS FCPW,
       '' AS FQPW,
       0 AS FChargeEmpId,
       0 AS FPoint,
       1 AS FRecvConsSMS,
       1 AS FRecvBulkSMS,
       FMemo AS FMemo,
       '' AS FCustom1,'' AS FCustom2,'' AS FCustom3,'' AS FCustom4,'' AS FCustom5,FState,'MGW'
       from TMGWMember
 WHERE FDeleted = 0 




------------------------------------------------

SELECT 套餐名称 FROM TMGWCard 
GROUP BY 套餐名称

--初始化对应关系
CREATE TABLE TMGWCardType
(
    FCardTypeName VARCHAR(50),
    FCardTypeId BIGINT,
    FCardTypeNo VARCHAR(30)
)

INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('400送400','04')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('498营养送500','06')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('存1000送400','09')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('存500送150','07')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('498营养赠额','11')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('300送300','03')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('存1000送499烫发或染发一次','10')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('存800送300','08')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('500送500','05')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('498营养包套送500','06')

UPDATE A
   SET A.FCardTypeId = B.FId
  FROM TMGWCardType AS A,TMemberCardType AS B
 WHERE A.FCardTypeNo = B.FNo 

疗程和储值卡
ALTER TABLE TMGWCard ADD FDeleted BIT
ALTER TABLE TMGWCard ADD FState INT
ALTER TABLE TMGWCard ADD FMemId BIGINT
ALTER TABLE TMGWCard ADD FCardId BIGINT
ALTER TABLE TMGWCard ADD FTypeId BIGINT
ALTER TABLE TMGWCard ADD FCourseId BIGINT
ALTER TABLE TMGWCard ADD FItemId BIGINT 

ALTER TABLE TMGWCard ADD FCompId BIGINT
ALTER TABLE TMGWCard ADD FMemo VARCHAR(2222)
ALTER TABLE TMGWCard ADD FCardNo VARCHAR(33)
ALTER TABLE TMGWCard ADD FCreateDate DATETIME
ALTER TABLE TMGWCard ADD FExpiredDate DATETIME
ALTER TABLE TMGWCard ADD FBalance DECIMAL(18,6)
ALTER TABLE TMGWCard ADD FUsedPurTimes DECIMAL(18,6)


--状态
UPDATE TMGWCard SET FDeleted = 1 WHERE 状态='已删除'
UPDATE TMGWCard SET FDeleted = 0 WHERE 状态!='已删除'
UPDATE TMGWCard SET FDeleted = 1 WHERE 储值卡号= ''
UPDATE TMGWCard SET FDeleted = 1 WHERE 可用余额=0

UPDATE A SET A.FDeleted = B.FDeleted 
FROM TMGWCard AS A,TMGWMember AS B
WHERE A.储值卡号 = B.表面号
AND B.FDeleted = 1




--卡类别
UPDATE A
   SET A.FTypeId = B.FCardTypeId
  FROM TMGWCard AS A,TMGWCardType AS B
 WHERE 套餐名称 = B.FCardTypeName
  
  
UPDATE A
   SET A.FTypeId = 0
 FROM TMGWCard AS A
 WHERE A.FTypeId IS NULL
   
--公司
UPDATE A
   SET A.FCompId = B.FCompId
  FROM TMGWCard AS A,TMGWCompany AS B
 WHERE A.包卡属所店 = B.FCompName
 
--卡ID
UPDATE TMGWCard SET FCardId = dbo.FNNewId() WHERE FCardId IS NULL

--卡号
UPDATE TMGWCard SET FCardNo = 储值卡号

--会员ID
UPDATE A
   SET A.FMemId = B.FMemId
  FROM TMGWCard AS A,TMGWMember AS B
 WHERE A.FCardNo = B.FMemNo

--日期
UPDATE A
   SET A.FCreateDate = 包卡时间,
       A.FExpiredDate = 到期时间
  FROM TMGWCard AS A

--状态
UPDATE TMGWCard SET FState = 1 WHERE 状态!= '已删除'
UPDATE A
   SET A.FState = B.FState
   FROM TMGWCard AS A,TMGWMember AS B
   WHERE A.FMemId = B.FMemId
     AND B.FState = 5 --挂失的
     

--储值卡余额
UPDATE TMGWCard SET FBalance = 可用余额 

--处理多个储值卡的问题
ALTER TABLE TMGWCard ADD FIndex INT 

UPDATE A
   SET A.FIndex = 0
  FROM TMGWCard AS A
  
UPDATE A
   SET A.FIndex = (SELECT COUNT(1)+1 FROM TMGWCard AS B WHERE B.储值卡号 = A.储值卡号 AND A.包卡时间 > B.包卡时间  AND A.FDeleted = 0 AND ISNULL(A.FTypeId,0) != 0 AND ISNULL(B.FTypeId,0) != 0 AND B.FDeleted = 0)
  FROM TMGWCard AS A
  WHERE A.FDeleted = 0
   
   
SELECT * FROM TMGWCard

SELECT * FROM TMGWCard WHERE FCardNo = '289806759'

UPDATE TMGWCard SET FCardNo = 储值卡号 + '.' + CAST(FIndex AS VARCHAR(10))
WHERE ISNULL(FTypeId,0) != 0 
AND FIndex != 1 
AND FDeleted = 0

--导入
 INSERT INTO TMemberCard(
        FCardId,
        FCardNo,
        FCardFaceNo,
        FTypeId,
        FValueType,
        FCompId,
        FMemId,
        FState,
        FCreateDate,
        FExpiredDate,
        FTotalTimes,
        FUsedTimes,
        FSaleCompId,
        FSaleBillId
        )
    SELECT 
        FCardId AS FCardId,
        FCardNo AS FCardNo,
        FCardNo AS FFaceNo,
        FTypeId AS FTypeId,    
        (SELECT FValueType FROM TMemberCardType WHERE FId = FTypeId) AS FValueType,    
        FCompId AS FCompId,
        FMemId AS FMemId,
        FState AS FState,
        FCreateDate AS FCreateDate,
        FExpiredDate AS FExpiredDate,
        0 AS FTotalTimes,
        0 AS FUsedTimes,
        FCompId AS FSaleCompId,
        0 AS FSaleBillId
   FROM TMGWCard 
  WHERE ISNULL(FTypeId,0) != 0
    AND FDeleted = 0 
    
    
     INSERT TMemberCardAccount(FId,FMemId,FCardId,FAcctId,FDeposit,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo)
    SELECT dbo.FNNewId(),FMemId,FCardId AS FCardId,3,FBalance,FBalance,0,FCreateDate,FExpiredDate,''
      FROM TMGWCard 
        WHERE ISNULL(FTypeId,0) != 0
    AND FDeleted = 0 
    
    
    INSERT TMemberCardAcctHist(FId,FMemId,FCardId,FAcctId,FTime,FCardTypeId,FCompId,FBillType,FBizType,FTransId,FTransNo,FInAmount,FOutAmount,FBalance,FArrear,FRecordTime,FMemo)
    select dbo.FNNewId(),A.FMemId,A.FCardId,3,getdate(),A.FTypeId,A.FCompId,'DR','Import',0,'',A.FBalance,0,A.FBalance,0,getdate(),'美顾问导入'
      FROM TMGWCard  AS A
        WHERE ISNULL(FTypeId,0) != 0
    AND FDeleted = 0 
       
--疗程

CREATE TABLE TMGWCourse
(
    FCourseName VARCHAR(30),
    FCourseNo VARCHAR(30),
    FCourseId BIGINT
)

INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('发型师剪发无忧','2012L6')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('bjbs营养无忧','6011L4')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('bjbs洗无忧','1011L1')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('染发无忧','5011L2')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('烫染无忧','7011L2')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('bjbs打蜡无忧','5012L1')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('bjbs头皮无忧','6015L2')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('烫发无忧','3011L2')

UPDATE A
   SET A.FCourseId = B.FId
  FROM TMGWCourse AS A,TCOurse AS B
 WHERE A.FCourseNo = B.FNo 


--使用次数
UPDATE TMGWCard SET FUserPurTimes = 

--更新疗程
UPDATE A
   SET A.FCourseId = B.FCourseId
  FROM TMGWCard AS A,TMGWCourse AS B
 WHERE A.套餐名称 = B.FCourseName
 
UPDATE A
   SET A.FItemId = B.FItemId
  FROM TMGWCard AS A,TCourse AS B
 WHERE A.FCourseId = B.FId
 
--检查下
      SELECT * FROM TMGWCard WHERE  ISNULL(FTypeId,0) = 0 AND ISNULL(FCourseId,0) = 0 AND FDeleted = 0

UPDATE A 
   SET A.FPurchaseDate = B.FCreateDate,
    A.FExpiredDate = B.FExpiredDate
    FROM TMemberCourse AS A,TMGWCard AS B
   WHERE A.FId = B.FCardId
   
    INSERT TMemberCourse(FId,FMemId,FCourseId,FMemPackId,FItemId,FCompId,FPurTimes,FPurAmount,FFreeTimes,FFreeAmount,FPurchaseDate,FExpiredDate,FBizType,FTransId,FArrear,FDurable,FMemo,FCalcMode,FAdjustPrice)
    SELECT FCardId,FMemId,FCourseId,0,FItemId,FCompId,0,0,0,0,FCreateDate,FExpiredDate,'DR.Import',0,0,0,'',2,0
      FROM TMGWCard
      WHERE ISNULL(FTypeId,0) = 0
      AND FDeleted = 0 
      
      
    INSERT INTO TMemberCourseHist(FId,FMemId,FMemCourseId,FPackId,FCourseId,FTime,FPurTimes,FFreeTimes,FUsePurTimes,FUseFreeTimes,
                                  FPurAmount,FFreeAmount,FUsePurAmount,FUseFreeAmount,FCompId,
                                  FBillType,FBizType,FTransId,FTransNo,FTransDetailId)
    
    SELECT dbo.FNNewId(),FMemId,FCardId,0,FCourseId,FTime = getdate(),0,0,0,0, 
        0,0,0,0,1,
        'DR','Import',0,'',0

    FROM TMGWCard
      WHERE ISNULL(FTypeId,0) = 0
      AND FDeleted = 0 
  
  
SELECT FCompId,FMemId,FCardId,FCardNo,MIN(FCreateDate) AS FCreateDate,MAX(FExpiredDate) AS FExpiredDate
FROM TMGWCard
WHERE ISNULL(FTypeId ,0) != 0
AND FDeleted = 0
GROUP BY FCompId,FMemId,FCardId,FCardNo

SELECT * FROM TMGWCard WHERE FCardNo IN
(
SELECT FCardNo FROM TMGWCard  
WHERE FDeleted = 0
AND ISNULL(FTypeId ,0) != 0
GROUP BY FCardNo
HAVING(COUNT(1) > 1)
)
AND ISNULL(FTypeId ,0) != 0
ORDER BY FCardNo


SELECT 套餐名称,'-->' FROM TMGWCard 
GROUP BY 套餐名称

SELECT 'EXEC PDeleteMemberSaleBill ' + CAST(FBillId AS VARCHAR(22)) FROM TMemberSaleBill


 Truncate  table TMemberOldTransHist 
        Truncate table TMemberCardOldAcctHist 
        Truncate table TMemberArrear 
        Truncate table TMemberArrearEmployee
        Truncate table TMemberRepay
        Truncate table TMemberCardAccount
        Truncate table TMemberCardAcctHist 
         Truncate table  TMemberCourse
         Truncate table  TMemberTransHist
         Truncate table  TMemberCourseHist
         Truncate table  TMemberPointHist
         Truncate table  TMemberPhoto 
         
         
         ALTER TABLE TMember ADD FData VARCHAR(100)
         ALTER TABLE TMemberCard ADD FData VARCHAR(100)
         

UPDATE TMember SET FData = 'MGW' 
UPDATE TMemberCard SET FData = 'MGW' 

UPDATE TMember SET FData = 'M'

SELECT * FROM TMember WHERE FMemId NOT IN(SELECT FMemId FROM TMemberCard)
AND FMemId NOT IN(SELECT FMemId FROM TMemberCourse)

SELECT dbo.FNCompNo(FCompId) FROM TMemberCourse 
SELECT dbo.FNCompNo(FCompId) FROM TMemberCard
SELECT dbo.FNCompNo(FCompId) FROM TMember 


--欠款

        INSERT TMemberArrear(FId,FMemId,FTime,FCompId,FContentType,FCardId,FBillType,FBizType,FTransId,FTransNo,FTransDetailId,FContentId,FArrear,FEmpId,FRepaid,FAcctArrear)
        SELECT dbo.FNNewId(),A.FMemId,getdate(),A.FCompId,'DR_Course',0,'DR_KK','DR_CardSale',0,'',0,0,A.当前欠款,0,0,A.当前欠款
          FROM TMGWMember AS A
         WHERE A.当前欠款 > 0