SELECT COUNT(1) FROM TImpMemberCard
SELECT COUNT(1) FROM TImpMember
SELECT COUNT(1) FROM TImpMemberCourse
SELECT COUNT(1) FROM TImpMemberFree

SELECT * FROM TImpMember  
SELECT * FROM TImpMemberCard  

 

DROP TABLE TImpCardType
CREATE TABLE TImpCardType
(
    FId BIGINT,
    FNo VARCHAR(50),
    FName VARCHAR(50),
    FType VARCHAR(50),
    FValueType INT,

)

ALTER TABLE TImpMemberCard ADD FCardTypeName VARCHAR(100)

ALTER TABLE TImpMember ADD FMemId BIGINT
ALTER TABLE TImpMember ADD FMemNo VARCHAR(50)
ALTER TABLE TImpMember ADD FName VARCHAR(50)
ALTER TABLE TImpMember ADD FGender INT
ALTER TABLE TImpMember ADD FMobile VARCHAR(50)
ALTER TABLE TImpMember ADD FCreateDate DATETIME
ALTER TABLE TImpMember ADD FCompId BIGINT
ALTER TABLE TImpMember ADD FMemo VARCHAR(1000)
ALTER TABLE TImpMember ADD FArrear DECIMAL(18,6)
ALTER TABLE TImpMember ADD FPoint DECIMAL(18,6)
ALTER TABLE TImpMember ADD FBirthday DATETIME
ALTER TABLE TImpMember ADD FBirthdayType CHAR
ALTER TABLE TImpMember ADD FCustom1 VARCHAR(100)
ALTER TABLE TImpMember ADD FPointText VARCHAR(100)

ALTER TABLE TImpMemberCard ADD FMemId BIGINT
ALTER TABLE TImpMemberCard ADD FCardNo VARCHAR(50)
ALTER TABLE TImpMemberCard ADD FCardTypeId BIGINT
ALTER TABLE TImpMemberCard ADD FCourseId BIGINT
ALTER TABLE TImpMemberCard ADD FBalance DECIMAL(18,6)
ALTER TABLE TImpMemberCard ADD FBalance2 DECIMAL(18,6)
ALTER TABLE TImpMemberCard ADD FRemainTimes INT
ALTER TABLE TImpMemberCard ADD FPurTimes INT
ALTER TABLE TImpMemberCard ADD FPurAmount DECIMAL(18,6)
ALTER TABLE TImpMemberCard ADD FItemId BIGINT
ALTER TABLE TImpMemberCard ADD FCreateDate DATETIME
ALTER TABLE TImpMemberCard ADD FExpiredDate DATETIME
ALTER TABLE TImpMemberCard ADD FCompId BIGINT
ALTER TABLE TImpMemberCard ADD FCardId BIGINT
ALTER TABLE TImpMemberCard ADD FMemo VARCHAR(1000)



ALTER TABLE TImpMemberCourse ADD FCardTypeId BIGINT
ALTER TABLE TImpMemberCourse ADD FItemId BIGINT

 
ALTER TABLE TImpMemberCourse ADD FRemainTimes INT
ALTER TABLE TImpMemberCourse ADD FPurTimes INT
ALTER TABLE TImpMemberCourse ADD FUsedTimes INT
ALTER TABLE TImpMemberCourse ADD FPurAmount DECIMAL(18,6)
ALTER TABLE TImpMemberCourse ADD FAdjustPrice DECIMAL(18,6)
 
ALTER TABLE TImpMemberCourse ADD FCreateDate DATETIME
ALTER TABLE TImpMemberCourse ADD FExpiredDate DATETIME
 
ALTER TABLE TImpMemberCourse ADD FCompId BIGINT
ALTER TABLE TImpMemberCourse ADD FCardId BIGINT
ALTER TABLE TImpMemberCourse ADD FMemId BIGINT
ALTER TABLE TImpMemberCourse ADD FMemo VARCHAR(1000) 
ALTER TABLE TImpMemberCourse ADD FCalcMode INT


SELECT * FROM TImpMemberCourse
--初始化卡类别
UPDATE TImpMemberCard SET FCardTYpeName = 卡名称 + '[' + 折扣 + '折]'


SELECT * FROM TImpMemberCard

DELETE TImpCardType
INSERT TImpCardType(FId,FNo,FName,FValueType)
SELECT dbo.FNNewId(),itemName,itemName  ,1
FROM TImpMemberCard
 
GROUP BY itemName 

 
 SELECT * FROM TImpMemberCourse
 
 DROP TABLE TImpItem
 
select cardType,dbo.FNNewId() AS FId,dbo.FNNewId() AS FCourseId, itemName + '.' + cardType   AS FNo , itemName AS FName 
 INTO TImpItem
   from TImpMemberCourse
GROUP BY itemName,cardType


SELECT * FROM TImpMember WHERE FMemId = 164982

SELECT * FROM TImpMemberCourse WHERE itemName = '5999少女之歌'

    INSERT  TItem(FId,FNo,FNoOfPad,FName,FAbbr,FTypeId,FBriefCode,FDescription,FPriceMode,FIsService,FStatTimes,FCustom1,FCustom2,FCustom3,FEnabled,FPrice,FVipPrice,FTime,FDctId,FPointMode,FPointValue,FDeptId,FAllowBooking)
    SELECT FId,FNo,FName,FName,FName,0,dbo.FNGetFirstPinYin(FName),'''',1,1,1,'''','''','''',1,0,0,0,0,0,0,0,1
      FROM TImpItem
 

        INSERT INTO  TCourse(FId,FNo,FName,FBriefCode,FItemId,FPackId,FSequence,FTypeId,FTimes,FFreeTimes,FDurable,FValidityPeriodUnit,FValidityPeriod,FCreateDate,FExpiredDate,FDescription,FEnabled,FPrice,FAmount,FCalcMode)  
        SELECT FCourseId AS FId,
               I.FNo + 'L1',
               I.FName + '-' + '疗程1' AS FName,
               '' AS FBriefCode,
               I.FId AS FItemId,
               0 AS FPackId,
               1 AS FSequence,
               (SELECT TOP 1 FId FROM TBasicType WHERE FKey = 'CourseType') AS FTypeId,
               1 AS FTimes,
               0 AS FFreeTimes,
               0 AS FDurable,
               0 AS FValidityPeriodUnit,
               0 AS FValidityPeriod, 
               getdate() AS FCreateDate,
               NULL AS FExpiredDate,
               '' AS FDescription,
               1 AS FEnabled,
               0 AS FPrice,
               0 AS FAmount,
               1 AS FCalcMode
          FROM TImpItem AS I
    
        UPDATE TCourse SET FCalcMode = 2 WHERE FNo LIKE '%时效卡%'
        SELECT * FROM TCourse
        
           
           
 --会员ID
 
 UPDATE TImpMemberCard SET FMemId = userInfoId


--开卡日期
UPDATE TImpMember SET FCreateDate = 注册日期

UPDATE A
   SET A.FCreateDate = B.FCreateDate
  FROM TImpMemberCard AS A,TImpMember AS B
 WHERE A.MemId = B.MemId
 
 
UPDATE TImpMemberCard SET FExpiredDate = 失效日期
UPDATE TImpMemberCard SET FExpiredDate = DATEADD(YEAR,100,FCreateDate) WHERE FExpiredDate = '1900-01-01'
 
   
 

--会员ID
 UPDATE TImpMember SET FMemId = shopUserInfoId
--编号
UPDATE TImpMember SET FMemNo = userTel
--姓名
UPDATE TImpMember SET FName = userName
--电话
UPDATE TImpMember SET FMobile = userTel
--性别
UPDATE TImpMember SET FGender = CASE WHEN sexName = '男' THEN 1 ELSE '0' END

--生日
UPDATE TImpMember SET FBirthdayType = 'G'
 UPDATE TImpMember SET FBirthday = userBirthday
 
--备注
UPDATE TImpMember SET FMemo = remark
 

--门店
UPDATE TImpMember SET FCompId = dbo.FNCompId('002')

--开卡日期
UPDATE TImpMember SET FCreateDate = CONVERT(VARCHAR,GETDATE(),23)


--卡

--卡ID
UPDATE TImpMemberCard SET FCardId = acId
--会员ID
UPDATE TImpMemberCard SET FMemId = userInfoId
--卡号
UPDATE A
   SET A.FCardNo = B.FMemNO
  FROM TIMpMemberCard AS A,TImpMember AS B
 WHERE A.FMemId = B.FMemId
 
--卡类别
UPDATE A
   SET A.FCardTypeId = B.FId
  FROM TIMpMemberCard AS A,TImpCardType AS B
 WHERE A.itemName = B.FName
 
--余额
UPDATE TImpMemberCard SET FBalance = 0
UPDATE TImpMemberCard SET FBalance = money

--赠送账户
UPDATE TImpMemberCard SET FBalance2 = 0

UPDATE A
   SET A.FBalance2 = B.sendMoney
  FROM TImpMemberCard AS A,TImpMemberFree AS B
 WHERE A.itemName ='充值卡'
   AND A.userInfoId = B.userInfoId
   
--开卡日期
UPDATE TImpMemberCard SET FCreateDate = CONVERT(VARCHAR,GETDATE(),23)
UPDATE TImpMemberCard SET FExpiredDate = '9999-01-01'


--门店
UPDATE TImpMemberCard SET FCompId = dbo.FNCompId('002')

 
 ---备注
 UPDATE TImpMemberCard SET FMemo = ''

 
--其他卡号
DROP TABLE #Card
SELECT FCardNo
 INTO #Card
FROM TImpMemberCard
GROUP BY FCardNo
HAVING(SUM(1) > 1)

SELECT * FROM TImpMemberCard WHERE FCardNO = '13036386221'

ALTER TABLE TImpMemberCard ADD FSeq INT IDENTITY 
ALTER TABLE TImpMemberCard ADD FIndex INT
ALTER TABLE TImpMemberCard ADD FCardTypeIndex INT


SELECT * FROM TImpCardType

UPDATE TImpMemberCard SET FCardTypeIndex = 0 WHERE itemName ='充值卡'
UPDATE TImpMemberCard SET FCardTypeIndex = 1 WHERE itemName !='充值卡'

UPDATE A
SET A.FIndex = 1+(SELECT COUNT(1) FROM TImpMemberCard AS B WHERE A.FCardNo = B.FCardNo AND (A.FCardTypeIndex > B.FCardTypeindex OR (A.FCardTypeIndex = B.FCardTypeindex AND (A.FCreateDate > B.FCreateDate OR (A.FCreateDate = B.FCreateDate AND (A.FSeq > B.FSeq))) )))
FROM TImpMemberCard AS A
WHERE A.FCardNo IN(SELECT FCardNo FROM #Card)
 

UPDATE TImpMemberCard SET FCardNo = FCardNo + '.' +  CAST(FIndex AS VARCHAR) 
WHERE FCardNo IN( SELECT FCardNo FROM #Card)  
AND FIndex > 1

SELECT * FROM TImpMemberCard WHERE FMemo IS NULL

SELECT * FROM TImpMemberCard WHERE CardId = 242037327
 
UPDATE TImpMember SET FMemno = 'JY' + FMemNO,FCardNo = 'JY' + FCardnO 

SELECT * FROM TImpMember WHERE FMemno = '10839'

delete TMemberCardType WHERE FId > 100
INSERT INTO TMemberCardType(FId,FNo,FName,FValueType,FMaterial,FPrice,FCardFeeAmt,FAcctId,FDeposit,FAcctId2,FDeposit2,FAcctId3,FDeposit3,FTimes,FAlertBalance,FMinRchgAmt,FSalePointMode,FSalePointValue,FRchgPointMode,FRchgPointValue,FPaySmsFee,FTimeCard,FValidityPeriod,FValidityPeriodUnit,FExpiredDate,FDefaultMemo,FCreateTime,FAllowSale,FAllowRchg,FDeptId,FEnabled,FOrder)
SELECT FId ,FNo,FName,FValueType,2,0,0,3,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
FROM TImpCardType
ORDER BY FNo 

UPDATE TMemberCardType SET FAllowRchg = 1 WHERE FId IN(SELECT FId FROM TImpCardType)
UPDATE TMemberCardType SET FAllowSale = 1 WHERE FId IN(SELECT FId FROM TImpCardType)
UPDATE TMemberCardType SET FEnabled = 1 WHERE FId IN(SELECT FId FROM TImpCardType)
UPDATE TMemberCardType SET FValidityPeriodUnit = 1,FValidityPeriod = 100


INSERT INTO TMemberCardTypeSub(FId,FCompId,FCardTypeId,FAllowSale,FAllowRchg,FEnabled)
SELECT dbo.FNNewId(),C.FId,I.FId,FAllowSale,FAllowRchg,FEnabled
  FROM TMemberCardType AS I,TCompany AS C
 WHERE I. FId IN(SELECT FId FROM TImpCardType)
 AND C.FNo IN('001','002')
 AND NOT EXISTS(SELECT TOP 1 1 FROM TMemberCardTypeSub AS B WHERE B.FCompId = C.FId AND B.FCardTypeId = I.FId)
 
 
  
   
 
 --套餐

SELECT * FROM TImpMemberCourse WHERE cellPhone = '18680802350'

UPDATE TImpMemberCourse SET FMemId = userInfoId
UPDATE TImpMemberCourse SET FCardId = acId
UPDATE TImpMemberCourse SET FExpiredDate = NULL
UPDATE TImpMemberCourse SET FExpiredDate = endTime
UPDATE TImpMemberCourse SET FMemo = ''
UPDATE TImpMemberCourse SET FCompId = dbo.FNCompId('002')

UPDATE TImpMemberCourse SET FPurAmount = money
UPDATE TImpMemberCourse SET FAdjustPrice= unitPrice
UPDATE TImpMemberCourse SET FPurTimes= total
UPDATE TImpMemberCourse SET FPurTimes= 0 WHERE FPurTimes IS NULL
UPDATE TImpMemberCourse SET FRemainTimes= surPlusTimes
UPDATE TImpMemberCourse SET FRemainTimes= 0 WHERE FRemainTimes IS NULL
UPDATE TImpMemberCourse SET FCalcMode = 1
UPDATE TImpMemberCourse SET FCalcMode = 2 WHERE cardType = '时效卡'
 UPDATE TImpMemberCourse SET FCreateDate = CONVERT(VARCHAR,GETDATE(),23)


 SELECT * FROM TImpMember
 WHERE FMemId IN(SELECT userInfoId FROM TImpMemberCourse WHERE itemName = '充值卡')
 
UPDATE A
   SET A.FItemId = B.FId,
       A.FCardTypeId = B.FCourseId
  FROM TImpMemberCourse AS A,TImpItem AS B
 WHERE A.itemName + '.' + A.cardType = B.FNo
 
 
 
 
 
 SELECT * FROM TImpMemberCourse WHERE FCardTYPEID IS NULL
 
 
 
UPDATE TImpMemberCourse SET FCompId = dbo.FNCompId('001')

  
  DROP TABLE TImpMemberCourse
  
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)
SELECT FMemId AS FMemId,
      FMemNo AS FMemNo,
      FName AS FName,
      dbo.FNGetFirstPinYin(FName),
      0 AS FTypeId,
      FCompId AS FCompId,
      ISNULL(FCreateDate ,'1900-01-01') AS FCreateDate,
      FGender AS FGender,
      FBirthdayType AS FBirthdayType,
      FBirthday AS FBirthday,
      '' AS FTelephone,
      ISNULL(FMobile,'') AS FMobilePhone,
       '' AS FQQ,
       '' AS FWeChat,
       '' AS FIdCard,
       '' AS FEmail,
       '' AS FAddress,
       0 AS FProId,
       '' AS FCPW,
       '' AS FQPW,
       0 AS FChargeEmpId,
      ISNULL( FPoint,0) AS FPoint,
       1 AS FRecvConsSMS,
       1 AS FRecvBulkSMS,
       ISNULL(FMemo,'') AS FMemo,
      '' AS FCustom1,'' AS FCustom2,'' AS FCustom3,'' AS FCustom4,'' AS FCustom5
       from TImpMember
 
 
 INSERT INTO TMemberCard(
        FCardId,
        FCardNo,
        FCardFaceNo,
        FTypeId,
        FValueType,
        FCompId,
        FMemId,
        FState,
        FCreateDate,
        FExpiredDate,
        FTotalTimes,
        FUsedTimes,
        FSaleCompId,
        FSaleBillId,
        FMemo)
    SELECT 
        FCardId AS FId,
        FCardNo AS FNo,
        FCardNo AS FFaceNo,
        FCardTypeId AS FTypeId,    
        (SELECT FValueType FROM TMemberCardType WHERE FId = FCardTypeId) AS FValueType,    
        FCompId AS FCompId,
        FMemId AS FMemId,
        1 AS FState,
        FCreateDate AS FCreateDate,
        FExpiredDate AS FExpiredDate,
        ISNULL(FRemainTimes,0) AS FTotalTimes,
        0 AS FUsedTimes,
        FCompId AS FSaleCompId,
        0 AS FSaleBillId,
        ISNULL('','') AS FMemo
   FROM TImpMemberCard
 
   

   
   SELECT * FROM TImpMember WHERE FCardId IS NULL
   
   
 WHERE CardNo IS NOT NULL 

    INSERT TMemberCardAccount(FMemId,FId,FCardId,FAcctId,FDeposit,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo)
    SELECT FMemId,dbo.FNNewId(),FCardId AS FCardId,3,FBalance,FBalance,0,FCreateDate,NULL AS FExpiredDate,''
   FROM TImpMemberCard AS A
   
        
 
    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.FCardTypeId,A.FCompId,'DR','Import',0,'',A.FBalance,0,A.FBalance,0,getdate(),'从盛传中导入'
   FROM TImpMemberCard AS A 
  
   
   DELETE TMemberCardAccount WHERE FAcctId = 10
   DELETE TMemberCardAcctHist WHERE FAcctId = 10
   
       INSERT TMemberCardAccount(FMemId,FId,FCardId,FAcctId,FDeposit,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo)
    SELECT FMemId,dbo.FNNewId(),FCardId AS FCardId,10,FBalance2,FBalance2,0,FCreateDate,NULL AS FExpiredDate,''
   FROM TImpMemberCard AS A
   WHERE FBalance2 != 0 
   AND itemName = '充值卡'
   and FCardNO NOT LIKE '%.%'
 
       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,10,getdate(),A.FCardTypeId,A.FCompId,'DR','Import',0,'',A.FBalance2,0,A.FBalance2,0,getdate(),'从盛传中导入'
   FROM TImpMemberCard AS A
   WHERE FBalance2 != 0 
   AND itemName = '充值卡'
     and FCardNO NOT LIKE '%.%'
     
 
 
 --疗程
     INSERT TMemberCourse(FId,FMemId,FCourseId,FMemPackId,FItemId,FCompId,FPurTimes,FUsedPurTimes,FPurAmount,FFreeTimes,FFreeAmount,FPurchaseDate,FExpiredDate,FBizType,FTransId,FArrear,FDurable,FMemo,FCalcMode,FAdjustPrice)
    SELECT FCardId,FMemId,FCardTypeId,0,FItemId,FCompId,FPurTimes,FPurTimes-FRemainTimes,FPurAmount,0,0,FCreateDate,FExpiredDate,'DR.Import',0,0,0,'从老系统导入',FCalcMode,0
    FROM TImpMemberCourse AS A
    WHERE FRemainTimes > 0 OR FCalcMode = 2
      
 
    INSERT INTO TMemberCourseLog(FId,FMemId,FCardId,FPackId,FCourseId,FTime,FInPurTimes,FInFreeTimes,FOutPurTimes,FOutFreeTimes,FCompId,FBillType,FBizType,FBillId,FBillNo,FBillDetailId,FRecordTime)
    SELECT dbo.FNNewId(),FMemId,A.FCardId AS FCardId,0, ISNULL(FCardTypeId,0),getdate(),FPurTimes,0,FPurTimes-FRemainTimes,0,FCompId,'DR','Import',0,'',0,getdate()
    FROM  TImpMemberCourse AS A  
    
    
    DELETE TImpMemberCourse
 
    
    DELETE TImpMemberCourseLog 

   
   SELECT * FROM TImpMember WHERE FBalance2!= 0
   
   SELECT * FROM TImpMember AS A
   WHERE FBalance2 != 0 
   
   SELECT * FROM TImpTransHist
   
   SELECT * FROM TImpMemberTransHist

 
ALTER TABLE TImpMemberTransHist ADD FCompId BIGINT
ALTER TABLE TImpMemberTransHist ADD FTime DATETIME

UPDATE TImpMemberTransHist SET FtIME = REPLACE(REPLACE(REPLACE(consumeDate,'年','-'),'月','-'),'日','')

 UPDATE TImpMemberTransHist SET FCompID = DBO.FNCompId('002')

UPDATE 
SELECT * from TIMpMEMBER WHERE shopuserInfoId not in(
         SELECT userInfoID FROM TImpMemberTransHist)
         
             
             
             
INSERT TMemberOldTransHist(FId,FCompId,FMemId,FTime,FCardId,FBIllType,FBizType,FTransNo,FCOntent,FPrice,FQuantity,FAmount,FPayment,Femployee,FMemo)
SELECT dbo.FNNewId(),FCompId,userInfoId,FTime,0,'',BizType,'',
msg,0,1,0,0,'', '' AS FMemo
  FROM TImpMemberTransHist
  
  UPDATE TMemberOldTransHist SET FTransNo = FBizType
  
  WHERE FMemId IS NOT NULL
  AND FMemId IN(SELECT FMemId FROM TImpMember3)
  
      
      SELECT * FROM TCompany 
 
   
   SELECT * FROM TMemberCard WHERE FMemo != ''
   
   SELECT * FROM TImpMember where isnull(FMemo,'') != ''
   
   ALTER TABLE TImpMember ADD ShopId 
   
   SELECT * FROM TIMpMemberCourse WHERE itemName = '充值卡'
   AND