sql-tools/Import/六分.sql

use QingDao_XianMeiGuoDu


SELECT * FROM TConsumeBill

SELECT * FROM TMemberSaleBill


 

    if exists(select 1 from master..sysservers where srvname='rs') 
    begin 
        exec sp_dropserver 'rs','droplogins' 
    end
    exec sp_addlinkedserver 'rs','','SQLOLEDB','203.86.26.211,1343'
    exec sp_addlinkedsrvlogin  'rs','false',null,'SDjnxmgd','SDjnxmgd950902'

    
    SELECT * INTO Guest FROM rs.SDjnxmgd.dbo.Guest
    SELECT * INTO vipCard FROM rs.SDjnxmgd.dbo.vipCard
    SELECT * INTO gPrjCard FROM rs.SDjnxmgd.dbo.gPrjCard
    SELECT * INTO gPrjCardList FROM rs.SDjnxmgd.dbo.gPrjCardList

SELECT * FROM     
TMemberCardType

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 = 10000 + ID,VipNo,REPLACE(dbo.FNToDBC(VipName),' ',''),1,FMaterial = 2,ParValue,FCardFeeAmt = 0,FAcctId = 3,FDeposit = 0,FAcctId2 = 0,FDeposit2 = 0 ,FAcctId3 = 0,FDeposit3 = 0,
FTimes = 0,FAlertBalance = 0,FMinRchgAmt = 0,FSalePointMode = 0,FSalePointValue = 0 ,FRchgPointMode = 0,FRchgPointValue = 0,FPaySmsFee = 0,FTimeCard = 0,FValidityPeriod = 100,FValidityPeriodUnit = 1,
FExpiredDate = NULL,FDefaultMemo = '',FCreateTime = GETDATE(),1,1,FDeptId = 0,FEnabled =1,FOrder = 1
FROM vipCard


     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 > 1
     AND NOT EXISTS(SELECT TOP 1 1 FROM TMemberCardTypeSub AS B WHERE B.FCompId = C.FId AND B.FCardTypeId = I.FId)
    
    



ALTER TABLE Guest ADD FMemId BIGINT
ALTER TABLE Guest ADD FMemNo VARCHAR(50) 
ALTER TABLE Guest ADD FName VARCHAR(50)
ALTER TABLE Guest ADD FGender INT
ALTER TABLE Guest ADD FMobile VARCHAR(50)
ALTER TABLE Guest ADD FBirthdayType VARCHAR(50)
ALTER TABLE Guest ADD FBirthday DATETIME
ALTER TABLE Guest ADD FCompId BIGINT 
ALTER TABLE Guest ADD FCreateDate DATETIME 
ALTER TABLE Guest ADD FMemo VARCHAR(MAX) 
ALTER TABLE Guest ADD FDeleted BIT

  

UPDATE Guest SET FDeleted = 0
UPDATE Guest SET FDeleted = 1 WHERE Deleted != 0 OR CompanyId != 3
UPDATE Guest SET FMemNO = REPLACE(gNo ,' ','')
UPDATE Guest SET FMemId = 100000 + ID
UPDATE Guest SET FName = REPLACE(gName,' ','')
UPDATE Guest SET FBirthdayType = 'G'
UPDATE Guest SET FMobile = REPLACE(gPhone,' ','')
UPDATE Guest SET FMemo =  REPLACE(cast(gExplain  as varchar),' ','')
UPDATE Guest SET FCreateDate = DATEADD(DAY,CreateDate,'1900-01-01')
UPDATE Guest SET FCompId =  dbo.FNCompId('001') 
UPDATE Guest SET FGender = 0 WHERE FairSex = 1
UPDATE Guest SET FGender = 1 WHERE FairSex = 0
UPDATE Guest SET FDeleted = 1 WHERE gNo = 'ɢ<EFBFBD><EFBFBD>'



 
 SELECT FMemNO,FName,FCreateDate FROM Guest 
WHERE FDeleted = 0







 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 ,GETDATE()) 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,
       0 AS FPoint,
       1 AS FRecvConsSMS,
       1 AS FRecvBulkSMS,
       FMemo AS FMemo,
       '' AS FCustom1,'' AS FCustom2,'' AS FCustom3,'' AS FCustom4,'' AS FCustom5
       from Guest
       WHERE FDeleted = 0
       
 

 SELECT * FROM GuestVIPCard
  
  
ALTER TABLE GuestVIPCard ADD FMemId BIGINT
ALTER TABLE GuestVIPCard ADD FCardId BIGINT
ALTER TABLE GuestVIPCard ADD FCardTypeId BIGINT 
ALTER TABLE GuestVIPCard ADD FCreateDate DATETIME
ALTER TABLE GuestVIPCard ADD FExpiredDate DATETIME
ALTER TABLE GuestVIPCard ADD FCardNo VARCHAR(100)
ALTER TABLE GuestVIPCard ADD FCompId BIGINT
ALTER TABLE GuestVIPCard ADD FDeleted BIT
ALTER TABLE GuestVIPCard ADD FState INT
ALTER TABLE GuestVIPCard ADD FBalance DECIMAL(18,6)
ALTER TABLE GuestVIPCard ADD FBalance2 DECIMAL(18,6)
ALTER TABLE GuestVIPCard ADD FArrear DECIMAL(18,6)

ALTER TABLE GuestVIPCard ADD FMemo VARCHAR(100)
  
 
select * from GuestVIPCard


UPDATE GuestVIPCard SET FMemId = 100000 + GuestId
UPDATE GuestVIPCard SET FCardId = 1000000 + ID
UPDATE GuestVIPCard SET FCardTypeId = 10000 + vipID
UPDATE GuestVIPCard SET FCreateDate = SellDate
UPDATE GuestVIPCard SET FBalance = Balance
UPDATE GuestVIPCard SET FArrear = Refund
UPDATE GuestVIPCard SET FMemo = remark

UPDATE GuestVIPCard SET FDeleted = 0
UPDATE GuestVIPCard SET FDeleted = 1 WHERE Deleted != 0 OR CompanyId != 3 OR GuestId NOT IN(SELECT ID FROM Guest WHERE FDeleted= 0)

UPDATE GuestVIPCard SET FExpiredDate = DATEADD(DAY,yxrq,'1900-01-01') 
UPDATE GuestVIPCard SET FExpiredDate = DATEADD(YEAR,100,FCreateDate) WHERE yxrq = 0
UPDATE GuestVIPCard SET FCompId = dbo.FNCompId('001')
UPDATE GuestVIPCard SET FState = 1


UPDATE A
   SET A.FCardNo = REPLACE(B.gNo,' ','')
 FROM Guest AS B,GuestVIPCard AS A
 WHERE A.GuestId = B.ID
 


  --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ظ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
 
 DROP TABLE #Card
 
  SELECT FCardNo INTO #Card
  FROM GuestVIPCard
    WHERE FDeleted = 0 
     
  GROUP BY FCardNo
  HAVING SUM(1) > 1
   
   
  UPDATE GuestVIPCard SET FCardNo = FCardNo + '.' + CAST(ID AS VARCHAR)
  WHERE FCardNo IN
  (
	  select FCardNo FROM #Card 
  ) 
  AND FBalance = 0
  
 
ALTER TABLE GuestVIPCard ADD FSeq INT IDENTITY 
ALTER TABLE GuestVIPCard ADD FIndex INT

UPDATE A
SET A.FIndex = 1+(SELECT COUNT(1) FROM GuestVIPCard AS B WHERE A.FCardNo = B.FCardNo AND (A.FCreateDate > B.FCreateDate OR (A.FCreateDate = B.FCreateDate AND (A.FSeq > B.FSeq) )))
FROM GuestVIPCard AS A
WHERE A.FDeleted = 0


UPDATE GuestVIPCard SET FCardNo = FCardNo + '.' +  CAST(FIndex AS VARCHAR) 
WHERE FCardNo IN( SELECT FCardNo FROM #Card)  
AND FDeleted = 0
  

 INSERT INTO TMemberCard(
        FCardId,
        FCardNo,
 
        FTypeId,
        FValueType,
        FCompId,
        FMemId,
        FState,
        FCreateDate,
        FExpiredDate,
        FTotalTimes,
        FUsedTimes,
        FSaleCompId,
        FSaleBillId,
        FMemo)
    SELECT 
        FCardId AS FId,
        FCardNo AS FNo,
 
        FCardTypeId AS FTypeId,    
        (SELECT FValueType FROM TMemberCardType WHERE FId = FCardTypeId) AS FValueType,    
        FCompId AS FCompId,
        FMemId AS FMemId,
        FState AS FState,
        FCreateDate AS FCreateDate,
        FExpiredDate = ISNULL(FExpiredDate,DATEADD(YEAR,100,FCreateDate)),
        0 AS FTotalTimes,
        0 AS FUsedTimes,
        FCompId AS FSaleCompId,
        0 AS FSaleBillId,
        ISNULL(FMemo,'') AS FMemo
   FROM GuestVIPCard
  WHERE FDeleted = 0 
 
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 GuestVIPCard
WHERE 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.FCardTypeId,A.FCompId,'DR','Import',0,'',A.FBalance,0,A.FBalance,0,getdate(),'<EFBFBD><EFBFBD>ɳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
FROM GuestVIPCard AS A
 WHERE FDeleted = 0
 



SELECT * FROM gPrjCardList
WHERE gPcId IN(SELECT ID FROM Guest WHERE FDeleted = 0)


SELECT * FROM Guest WHERE gNo = 'QDJMHS00011'      

 
 
SELECT * FROM gPrjCardList WHERE gPcId = 2798
SELECT * FROM gPrjCard WHERE gId = 1866
SELECT * FROM Guest WHERE Id = 2425

SELECT C.FName,C.FMemNo,A.*,B.* FROM gPrjCard AS A,gPrjCardList AS B,Guest AS C
WHERE C.Id = A.gId
AND A.ID = B.gPcId
AND C.FDeleted = 0
AND C.CompanyId = 3
AND A.Deleted = 0
AND A.CompanyId = 3

SELECT SUM(FBalance) FROM TMemberCardAccount

SELECT SUM(1) FROM TMemberCard 

SELECT * FROM Guest WHERE FMemNO = '0004728848'



SELECT * FROM TImpMemberCourse

drop table TImpMemberCourse


DELETE TItem WHERE FId IN(SELECT FItemId FROM TImpItem)

ALTER TABLE TImpMemberCourse ADD FCardState INT
  
 ALTER TABLE TImpMemberCourse ADD FMemId BIGINT
ALTER TABLE TImpMemberCourse ADD FCardTypeId BIGINT
ALTER TABLE TImpMemberCourse ADD FIsCourse BIT
ALTER TABLE TImpMemberCourse ADD FCalcMode INT
ALTER TABLE TImpMemberCourse ADD FBalance DECIMAL(18,6)
ALTER TABLE TImpMemberCourse ADD FRemainTimes INT
ALTER TABLE TImpMemberCourse ADD FPurTimes INT
ALTER TABLE TImpMemberCourse ADD FPurAmount DECIMAL(18,6)
ALTER TABLE TImpMemberCourse ADD FItemId BIGINT
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 FMemo VARCHAR(1000)
ALTER TABLE TImpMemberCourse ADD FArrear DECIMAL(18,6)
ALTER TABLE TImpMemberCourse ADD FPoint DECIMAL(18,6)
ALTER TABLE TImpMemberCourse ADD FState INT
ALTER TABLE TImpMemberCourse ADD FCardNo VARCHAR(1000)
ALTER TABLE TImpMemberCourse ADD FAdjustPrice DECIMAL(18,6)

DROP TABLE TImpiTEM
 
select <EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> AS FName,dbo.FNNewId() AS FItemId,dbo.FNNewId() AS FCourseId 
INTO TImpItem
FROM 
TImpMemberCourse  
GROUP BY  <EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>


drop table 

TImpMemberCourse


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 FItemId,FName,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,
       A.FName + 'L1',
       A.FName   AS FName,
       '' AS FBriefCode,
       A.FItemId 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 A 
WHERE FCourseId NOT IN(SELECT FId FROM TCourse)

UPDATE TImpMemberCourse SET FCardId = dbo.FNNewId()

UPDATE  A 
   SET A.FCardTypeId = B.FCourseId,
       A.FItemId = B.FItemId
   FROM TImpMemberCourse AS A,TImpItem AS B
  WHERE A.<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>= B.FName
  
 
SELECT * FROM Guest WHERE gNo LIKE '000%'

UPDATE A
   SET A.FMemId = B.FMemId
 FROM TImpMemberCourse AS A,Guest AS B
WHERE A.<EFBFBD>˿Ϳ<EFBFBD><EFBFBD><EFBFBD> = B.gNo

SELECT * FROM TIMpMemberCourse

UPDATE A
   SET A.FMemId = B.FMemId
 FROM TImpMemberCourse AS A,Guest AS B
WHERE '000' + A.<EFBFBD>˿Ϳ<EFBFBD><EFBFBD><EFBFBD> = B.gNo
and a.<EFBFBD>˿<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> = B.gName
 
 
 UPDATE A
   SET A.FMemId = B.FMemId
 FROM TImpMemberCourse AS A,Guest AS B
WHERE '00' + A.<EFBFBD>˿Ϳ<EFBFBD><EFBFBD><EFBFBD> = B.gNo
and a.<EFBFBD>˿<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> = B.gName

UPDATE TImpMemberCourse SET FCompId = dbo.FNCompId('001')
UPDATE TImpMemberCourse SET FCardId = dbo.FNNewId()

UPDATE TImpMemberCourse SET FCalcMode =1
UPDATE TImpMemberCourse SET FCalcMode = 2 WHERE <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> = '<EFBFBD><EFBFBD>'
UPDATE TImpMemberCourse SET FPurTimes = <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> WHERE <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> != '<EFBFBD><EFBFBD>'
UPDATE TImpMemberCourse SET FAdjustPrice = <EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE TImpMemberCourse SET FPurAmount = CAST(<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> AS FLOAT) * FPurTimes
UPDATE TImpMemberCourse SET FRemainTimes = ʣ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE TImpMemberCourse SET FCreateDate = dateadd(day,cast(null as int),'1900-01-01')
UPDATE TImpMemberCourse SET FExpiredDate = NULL

UPDATE TImpMemberCourse SET FExpiredDate = <EFBFBD><EFBFBD>Ч<EFBFBD><EFBFBD><EFBFBD><EFBFBD> WHERE <EFBFBD><EFBFBD>Ч<EFBFBD><EFBFBD><EFBFBD><EFBFBD> IS NOT NULL


UPDATE TImpMemberCourse SET FState = 1
UPDATE TImpMemberCourse SET FPurAmount = 0 WHERE FPurAmount IS NULL
UPDATE TImpMemberCourse SET FAdjustPrice = 0 WHERE FAdjustPrice IS NULL
UPDATE TImpMemberCourse SET FPurTimes = 0 WHERE FPurTimes IS NULL
UPDATE TImpMemberCourse SET FRemainTimes = 0 WHERE FRemainTimes IS NULL
UPDATE TImpMemberCourse SET FMemo = ISNULL(<EFBFBD><EFBFBD>ע,'')


 
 UPDATE A
    SET A.FCreateDate = B.FCreateDate 
  FROM TIMpMemberCourse AS A,TMember AS B
 WHERE A.FMemId = B.FMemId
 AND A.FCreateDate IS NULL
 
 

INSERT TMemberCourse(FId,FMemId,FCourseId,FMemPackId,FItemId,FCompId,FPurTimes,FUsedPurTimes,FPurAmount,FFreeTimes,FFreeAmount,FPurchaseDate,FExpiredDate,FBizType,FTransId,FArrear,FDurable,FMemo,FCalcMode,FAdjustPrice,FState)
SELECT FCardId,FMemId,FCardTypeId,0,FItemId,FCompId,FPurTimes,FPurTimes - FRemainTimes,FPurAmount,0,0,FCreateDate,FExpiredDate,'DR.Import',0,0,0,'',FCalcMode,FAdjustPrice,FState
FROM TImpMemberCourse AS A


 
-- AND <EFBFBD>ײ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> LIKE '%<EFBFBD><EFBFBD>%'
 
INSERT INTO TMemberCourseLog(FId,FMemId,FCardId,FCourseId,FItemId,FPackId,FTime,FInPurTimes,FInFreeTimes,FOutPurTimes,FOutFreeTimes,FRemainPurTimes,FRemainFreeTimes,FCompId,FBillType,FBizType,FBillId,FBillNo,FBillDetailId,FRecordTime,FMemo)
SELECT Id = FCardId,MemId = FMemId,CardId = 0,CourseId = FCardTypeId,ItemId = FItemId,PackId = 0,Time = getdate(),InPurTimes = FPurTimes,InFreeTimes =0,
OutPurTimes = FPurTimes - FRemainTimes,OutFreeTimes = 0,RemainPurTimes = FRemainTimes,RemainFreeTimes = 0,CompId = FCompId ,BillType = 'DR',BizType = 'Import',BillId = 0,BillNo = '',BillDetailId = 0,RecordTime = getdate(),Memo = '<EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
FROM  TImpMemberCourse AS A