You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
863 lines
28 KiB
863 lines
28 KiB
|
|
SELECT * FROM TImpMemberInfo_1
|
|
|
|
SELECT * FROM gdm01
|
|
|
|
SELECT count(1) FROM TImpMember WHERE FCPW != ''
|
|
SELECT count(1) FROM TImpMember WHERE CardId IS NOT NULL
|
|
|
|
SELECT count(1) FROM TImpMemberInfo WHERE MemId IS NOT NULL
|
|
SELECT count(1) FROM TImpMember WHERE 备注 IS NULL
|
|
|
|
SELECT count(1) FROM TImpMemberCourseEx
|
|
|
|
|
|
sp_rename 'TImpMemberCourse','TImpMemberCourse_1'
|
|
sp_rename 'TImpMember','TImpMember_1'
|
|
sp_rename 'TImpMemberInfo','TImpMemberInfo_1'
|
|
|
|
SELECT * FROM TImpMemberTransHist
|
|
SELECT * FROM TImpMemberCourse
|
|
SELECT * FROM TImpMemberCardAccthist
|
|
|
|
DELETE TImpMemberTransHist
|
|
DELETE FROM TImpMemberCourse
|
|
DELETE FROM TImpMemberCardAccthist
|
|
|
|
SELECT * FROM TImpMember where 卡号 < '532101429'
|
|
|
|
select
|
|
|
|
DELETE TImpMember
|
|
|
|
SELECT * FROM TImpMemberCourse
|
|
|
|
SELECT * FROM TImpMember
|
|
WHERE 卡类型 != '会员卡'
|
|
|
|
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE 套餐名称= '称克营养'
|
|
|
|
|
|
CREATE TABLE TImpCardType
|
|
(
|
|
FCardTypeName VARCHAR(500),
|
|
FCardTypeNo VARCHAR(50),
|
|
FCardTypeId BIGINT
|
|
)
|
|
|
|
INSERT TImpCardType(FCardTypeName,FCardTypeNo,FCardTypeId)
|
|
SELECT 套餐名称,套餐名称,DBO.FNNewId()
|
|
from TImpMemberCourse
|
|
WHERE 套餐类型= '金额消费'
|
|
group by 套餐名称
|
|
|
|
|
|
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 =FCardTypeId, FCardTypeNo,FCardTypeName,1,FMaterial = 2,0,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 TImpCardType
|
|
|
|
|
|
|
|
--会员资料
|
|
INSERT TImpMemberInfo(姓名,拼音简码,性别,手机号码,车牌号,生日,持卡数)
|
|
SELECT 姓名,拼音简码,'女',手机号码,'','',1
|
|
FROM TImpMember
|
|
WHERE 手机号码 NOT IN(SELECT 手机号码 FROM TImpMemberInfo)
|
|
|
|
ALTER TABLE TImpMemberInfo ADD FMemId BIGINT
|
|
ALTER TABLE TImpMemberInfo ADD FMemNo VARCHAR(50)
|
|
ALTER TABLE TImpMemberInfo ADD FName VARCHAR(50)
|
|
ALTER TABLE TImpMemberInfo ADD FGender INT
|
|
ALTER TABLE TImpMemberInfo ADD FMobile VARCHAR(50)
|
|
ALTER TABLE TImpMemberInfo ADD FBirthdayType VARCHAR(50)
|
|
ALTER TABLE TImpMemberInfo ADD FBirthday DATETIME
|
|
ALTER TABLE TImpMemberInfo ADD FCompId BIGINT
|
|
ALTER TABLE TImpMemberInfo ADD FCreateDate DATETIME
|
|
ALTER TABLE TImpMemberInfo ADD FMemo VARCHAR(MAX)
|
|
|
|
SELECT * FROM TImpMemberInfo
|
|
|
|
UPDATE TImpMemberInfo
|
|
SET FMemId = abs( CAST( CAST(cast(memid as uniqueidentifier) AS BINARY(8)) AS BIGINT))
|
|
|
|
UPDATE TImpMemberInfo
|
|
SET FMemId = dbo.FNNewId()
|
|
WHERE FMemId IS NULL
|
|
|
|
UPDATE A
|
|
SET A.FMemNo = B.卡号
|
|
FROM TImpMemberInfo AS A,TImpMemberCard AS B
|
|
WHERE A.手机号码 = B.手机号码
|
|
|
|
UPDATE TImpMemberInfo SET FMemNo = 手机号码 WHERE FMemNo IS NULL
|
|
|
|
UPDATE A
|
|
SET A.FCreateDate = (SELECT MIN(开卡时间) FROM TImpMemberCard AS B WHERE A.手机号码 = B.手机号码)
|
|
FROM TImpMemberInfo AS A
|
|
|
|
UPDATE TImpMemberInfo SET FCreateDate ='1900-01-01'
|
|
WHERE FCreateDate IS NULL
|
|
|
|
UPDATE TImpMemberInfo SET FName = 姓名
|
|
UPDATE TImpMemberInfo SET FMobile = 手机号码
|
|
UPDATE TImpMemberInfo SET FGender = CASE WHEN 性别 = '男' THEN '1' ELSE 0 END
|
|
UPDATE TImpMemberInfo SET FGender = CASE WHEN 性别 = '男' THEN '1' ELSE 0 END
|
|
UPDATE TImpMemberInfo SET FBirthdayType = 'G'
|
|
UPDATE TImpMemberInfo SET FBirthdayType = 'L' WHERE 生日 LIKE '%农历%'
|
|
UPDATE TImpMemberInfo SET FBirthday = REPLACE(生日,'公历','')
|
|
UPDATE TImpMemberInfo SET FCompId = 1
|
|
|
|
SELECT * FROM TMember
|
|
|
|
SELECT * FROM TImpMemberInfo
|
|
where 持卡数 > 1
|
|
|
|
SELECT * FROM TImpMemberInfo
|
|
where 照片 is null
|
|
|
|
UPDATE A SET FMemo = B.备注
|
|
FROM TImpMemberCard AS B,TImpMemberInfo AS A
|
|
WHERE 备注!= ''
|
|
AND A.手机号码 = B.手机号码
|
|
|
|
|
|
|
|
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,
|
|
FCreateDate 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,
|
|
ISNULL(FMemo,'') AS FMemo,
|
|
'太西美发' AS FCustom1,'' AS FCustom2,'' AS FCustom3,'' AS FCustom4,'' AS FCustom5
|
|
from TImpMemberInfo
|
|
|
|
|
|
|
|
SELECT * FROM TImpMember_1 WHERE 手机号码= '15123244225'
|
|
|
|
SELECT * FROM TImpMemberCard WHERE FCardNO = '288100002'
|
|
--卡
|
|
|
|
|
|
ALTER TABLE TImpMemberCard ADD FBalance DECIMAL(18,6)
|
|
ALTER TABLE TImpMemberCard ADD FBalance2 DECIMAL(18,6)
|
|
ALTER TABLE TImpMemberCard ADD FMemId BIGINT
|
|
ALTER TABLE TImpMemberCard ADD FCardTypeName VARCHAR(100)
|
|
ALTER TABLE TImpMemberCard ADD FCardNo VARCHAR(50)
|
|
ALTER TABLE TImpMemberCard ADD FCardTypeId BIGINT
|
|
ALTER TABLE TImpMemberCard ADD FExpiredDate DATETIME
|
|
ALTER TABLE TImpMemberCard ADD FCreateDate DATETIME
|
|
ALTER TABLE TImpMemberCard ADD FCardId BIGINT
|
|
ALTER TABLE TImpMemberCard ADD FCompId BIGINT
|
|
|
|
|
|
|
|
UPDATE TImpMemberCard SET FExpiredDate = 到期时间
|
|
UPDATE TImpMemberCard SET FCreateDate = 开卡时间
|
|
UPDATE TImpMemberCard SET FCardNo = 卡号
|
|
UPDATE TImpMemberCard SET FCompId = 1
|
|
|
|
|
|
UPDATE TImpMemberCard
|
|
SET FCardID = dbo.FNNewId() WHERE FCardid IS NULL
|
|
|
|
|
|
UPDATE A
|
|
SET A.FMemId = B.FMemId
|
|
FROM TImpMemberCard AS A,TImpMemberInfo AS B
|
|
WHERE A.手机号码 = B.手机号码
|
|
|
|
SELECT * FROM TImpMemberCard WHERE FMemId IS NULL
|
|
|
|
|
|
UPDATE A
|
|
SET A.FCardTypeId = B.FId
|
|
FROM TImpMemberCard AS A,TMemberCardType AS B
|
|
WHERE A.卡类型 = B.FName
|
|
|
|
|
|
|
|
UPDATE TImpMemberCard SET FBalance = CAST(实收余额 AS FLOAT)
|
|
UPDATE TImpMemberCard SET FBalance2 = CAST(赠送余额 AS FLOAT)
|
|
|
|
|
|
|
|
|
|
|
|
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,
|
|
1 AS FState,
|
|
FCreateDate AS FCreateDate,
|
|
FExpiredDate AS FExpiredDate,
|
|
0 AS FTotalTimes,
|
|
0 AS FUsedTimes,
|
|
FCompId AS FSaleCompId,
|
|
0 AS FSaleBillId,
|
|
'' AS FMemo
|
|
FROM TImpMemberCard
|
|
|
|
|
|
|
|
|
|
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
|
|
|
|
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
|
|
|
|
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
|
|
WHERE FBalance2 != 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,10,getdate(),A.FCardTypeId,A.FCompId,'DR','Import',0,'',A.FBalance2,0,A.FBalance2,0,getdate(),'从老系统中导入'
|
|
FROM TImpMemberCard AS A
|
|
WHERE FBalance2 != 0
|
|
|
|
UPDATE TMemberCard SET FExpiredDate = DATEADD(YEAR,100,FCreateDate )
|
|
WHERE FExpiredDate = '1900-01-01'
|
|
|
|
--疗程
|
|
|
|
|
|
SELECT 卡号,CardId FROM TImpMember
|
|
WHERE CardId NOT IN(SELECT CardId FROM TImpMemberCourseEx)
|
|
|
|
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)
|
|
|
|
|
|
--导疗程
|
|
UPDATE TImpMemberCourse
|
|
SET FCardId = dbo.FNNewId()
|
|
WHERE FCardId IS NULl
|
|
|
|
|
|
UPDATE TImpMemberCourse
|
|
SET FCardId = NULL
|
|
|
|
UPDATE TImpMemberCourse
|
|
SET FCardId = dbo.FNNewId() WHERE FCardId IS NULL
|
|
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE CardId = '8e07e178-11b0-41eb-84a5-a51600d0c6b6'
|
|
SELECT CardId,SUM(1)
|
|
FROM TImpMemberCourse
|
|
GROUP BY CardId
|
|
ORDER BY SUM(1) DESC
|
|
|
|
UPDATE TImpMemberCourse SET FCardId = dbo.FNNewId() WHERE FCardId IS NULL
|
|
UPDATE TImpMemberCourse SET FCompId = 1
|
|
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE 套餐类型 != '金额消费'
|
|
|
|
UPDATE TImpMemberCourse SET FIsCourse = 0
|
|
UPDATE TImpMemberCourse SET FIsCourse = 1 WHERE 套餐类型 != '金额消费'
|
|
UPDATE TImpMemberCourse SET FCalcMode = 1 WHERE 套餐类型 = '单项计次'
|
|
UPDATE TImpMemberCourse SET FCalcMode = 2 WHERE 套餐类型 = '不限次数'
|
|
UPDATE TImpMemberCourse SET FCalcMode = 1 WHERE 套餐类型 = '整体计次'
|
|
|
|
|
|
|
|
|
|
UPDATE A SET A.FCreateDate = B.开卡时间,
|
|
A.FExpiredDate = A.到期时间
|
|
FROM TImpMemberCourse AS A,TImpMEMBERCard AS B
|
|
WHERE A.CardId = B.CardId
|
|
|
|
|
|
SELECT 状态 FROM TImpMemberCourse
|
|
GROUP BY 状态
|
|
|
|
UPDATE TImpMemberCourse SET FState = 1 WHERE 状态 != '已退卡'
|
|
UPDATE TImpMemberCourse SET FState = 2 WHERE 状态 = '已退卡'
|
|
|
|
UPDATE A
|
|
SET A.FName = B.FName
|
|
FROM TCourse AS A,TItem AS B
|
|
WHERE A.FItemId = B.FId
|
|
AND B.FNo IN(SELECT 套餐名称 FROM TImpMemberCourse)
|
|
|
|
UPDATE A
|
|
SET A.FCalcMode = B.FCalcMode
|
|
FROM TCourse AS A,TImpMemberCourse AS B,TItem AS I
|
|
WHERE I.FNo = B.套餐名称
|
|
AND I.FId = A.FItemId
|
|
AND B.FIsCourse = 1
|
|
--AND 套餐名称 LIKE '%克%'
|
|
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FIsCourse = 1
|
|
AND FCalcMode = 0
|
|
|
|
UPDATE A
|
|
SET A.FMemId = M.FMemId
|
|
FROM TImpMemberCourse AS A,TImpMemberCard AS B,TImpMemberInfo AS M
|
|
WHERE M.手机号码 = B.手机号码
|
|
AND A.CardId = B.CardId
|
|
|
|
|
|
|
|
UPDATE A
|
|
SET A.FCardTypeId = B.FId,
|
|
A.FItemId = B.FItemId
|
|
FROM TImpMemberCourse AS A,TCourse AS B,TItem AS I
|
|
WHERE A.套餐名称= I.FNo
|
|
-- AND A.FCalcMode = B.FCalcMode
|
|
AND I.FId = B.FItemId
|
|
-- AND 套餐名称 LIKE '%克%'
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FIsCourse = 1 AND FItemId = 0
|
|
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FCardTypeId IS NULL AND FIsCourse = 1
|
|
|
|
|
|
UPDATE TImpMemberCourse SET FRemainTimes = CAST(剩余次数 AS FLOAT)
|
|
UPDATE TImpMemberCourse SET FBalance = CAST(剩余金额 AS FLOAT)
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FIsCourse = 1
|
|
|
|
SELECT * FROM TCourse WHERE FCalcMode = 2
|
|
|
|
SELECT * FROM TMemberCardType
|
|
|
|
SELECT * FROM TImpMember
|
|
WHERE CAST(赠送余额 AS FLOAT) != 0
|
|
|
|
SELECT A.卡号,A.卡类型,A.套餐余额,A.到期时间, B.套餐名称,B.套餐类型,B.剩余金额,B.到期时间, CAST(A.赠送余额 AS FLOAT)
|
|
FROM TImpMember AS A,TImpMemberCourse AS B
|
|
WHERE a.卡号 = B.FCardNo
|
|
AND CAST(赠送余额 AS FLOAT) != 0
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FCardNo = '532108609'
|
|
SELECT * FROM TImpMember WHERE 卡号 = '532108609'
|
|
|
|
SELECT FCardID,sum(1)
|
|
FROM TImpMember
|
|
GROUP BY FCardID
|
|
ORDER BY sum(1) DESC
|
|
|
|
SELECT * FROM TImpMember WHERE CardId IS NULL
|
|
|
|
SELECT * FROM TConsumeBill
|
|
|
|
SELECT * FROM TImpMemberCourseEx
|
|
|
|
SELECT count(1) FROM TImpMemberCourseEx
|
|
SELECT * FROM TImpMemberCourse
|
|
|
|
SELECT * FROM TImpMemberCourse
|
|
|
|
SELECT * FROM TMemberCourseLog
|
|
WHERE FBillType != 'DR'
|
|
|
|
sp_rename 'TImpMemberCourse','TImpMemberCourseEx'
|
|
|
|
SELECT * FROM TConsumeBill
|
|
|
|
DELETE TMemberCardAcctHist
|
|
DELETE TMemberCard WHERE FCardId> 0
|
|
|
|
|
|
|
|
DELETE TMemberCourseLog
|
|
|
|
SELECT * FROM TMemberSaleBill
|
|
|
|
|
|
|
|
ALTER TABLE TImpMemberCourseEx ALTER COLUMN 操作 VARCHAR(2000)
|
|
|
|
SELECT * FROM TMemberPhoto
|
|
INSERT TMemberPhoto(FMemId,FType,FImage,FImagePath)
|
|
SELECT FMemId,1,NULL,照片
|
|
FROM TImpMemberInfo
|
|
WHERE 照片 != ''
|
|
|
|
SELECT * FROM TMemberPhoto
|
|
|
|
SELECT * FROM TImpMember WHERE CardId IS NULL
|
|
|
|
SELECT * FROM TImpMemberInfo WHERE MemId IS NULL
|
|
|
|
SELECT * FROM TImpMemberCourse
|
|
|
|
SELECT * FROM TImpMemberInfo WHERE FName like '%王翠%'
|
|
|
|
SELECT * FROM TImpMember WHERE 手机号码 = '13608978208'
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FCardNo = '532102891'
|
|
|
|
SELECT * FROM TImpMember WHERE CardId = '43fdd01c-8919-451d-8087-a5e400b339a6'
|
|
|
|
UPDATE A
|
|
SET A.FMobilePhone = B.手机号码
|
|
FROM TMember AS A,TImpMemberInfo AS B
|
|
WHERE A.FMemId = B.FMemId
|
|
|
|
|
|
UPDATE A
|
|
SET A.FMemId = B.FMemId
|
|
FROM TImpMemberCourse AS A,TImpMember AS C,TImpMemberInfo AS B
|
|
WHERE A.FCardNO = C.卡号
|
|
AND C.手机号码= B.手机号码
|
|
|
|
|
|
--处理ID
|
|
UPDATE TImpMemberInfo SET MemId = REPLACE(LEFT(操作,CHARINDEX(',',操作)-1),'/MemberInfo/Delete/','')
|
|
FROM TImpMemberInfo
|
|
|
|
SELECT * FROM TMember WHERE FMemNo = '532101341'
|
|
|
|
UPDATE TMemberCourse SET FExpiredDate = nULL WHERE FExpiredDate = '1900-01-01 00:00:00.000' AND FCalcMode = 1
|
|
|
|
--删除克卡类
|
|
DELETE TMemberCardAccount WHERE FCardId IN(SELECT FCardId FROM TImpMemberCourse WHERE 套餐名称 LIKE '%克%')
|
|
DELETE TMemberCardAcctHist WHERE FCardId IN(SELECT FCardId FROM TImpMemberCourse WHERE 套餐名称 LIKE '%克%')
|
|
|
|
DELETE TMemberCard WHERE FCardId IN(SELECT FCardId FROM TImpMemberCourse WHERE 套餐名称 LIKE '%克%')
|
|
|
|
UPDATE TConsumeBill SET FCardId = 0 ,FCardNO = ''
|
|
WHERE FCardId IN(SELECT FCardId FROM TImpMemberCourse WHERE 套餐名称 LIKE '%克%')
|
|
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FCardTypeId IS NULL
|
|
|
|
|
|
UPDATE TImpMemberCourse SET FExpiredDate = nULL WHERE FExpiredDate = '1900-01-01 00:00:00.000' AND FIsCourse = 1
|
|
|
|
INSERT TMemberCourse(FId,FMemId,FCourseId,FMemPackId,FItemId,FCompId,FPurTimes,FPurAmount,FFreeTimes,FFreeAmount,FPurchaseDate,FExpiredDate,FBizType,FTransId,FArrear,FDurable,FMemo,FCalcMode,FAdjustPrice,FState)
|
|
SELECT FCardId,FMemId,FCardTypeId,0,FItemId,FCompId,FRemainTimes,FBalance,0,0,FCreateDate,FExpiredDate,'DR.Import',0,0,0,'老系统导入',FCalcMode,0,FState
|
|
FROM TImpMemberCourse AS A
|
|
WHERE A.FIsCourse = 1
|
|
|
|
|
|
-- AND 套餐名称 LIKE '%克%'
|
|
|
|
|
|
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 = FRemainTimes,InFreeTimes =0,
|
|
OutPurTimes = 0,OutFreeTimes = 0,RemainPurTimes = FRemainTimes,RemainFreeTimes = 0,CompId = FCompId ,BillType = 'DR',BizType = 'Import',BillId = 0,BillNo = '',BillDetailId = 0,RecordTime = getdate(),Memo = '老系统导入'
|
|
FROM TImpMemberCourse AS A
|
|
WHERE A.FIsCourse = 1
|
|
|
|
|
|
-- AND 套餐名称 LIKE '%克%'
|
|
|
|
SELECT * FROM TMemberCourse WHERE FCalcMode = 1
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FMemId = 2636725330156656311
|
|
|
|
SELECT * FROM TMember WHERE FMemId = 2636725330156656311
|
|
|
|
SELECT * FROM TMember WHERE FMemId = 1139835837647134132
|
|
SELECT * FROM TImpMemberCourse WHERE FMemId = 1139835837647134132
|
|
|
|
SELECT * FROM TImpMember WHERE 卡号 = 'L0532100302'
|
|
|
|
UPDATE A
|
|
SET A.FExpiredDate = B.到期时间
|
|
FROM TMemberCard AS A,TImpMemberCourse AS B
|
|
WHERE A.FCardId = B.FCardId
|
|
AND A.FExpiredDate != B.到期时间
|
|
|
|
--
|
|
|
|
储值卡
|
|
UPDATE TImpMemberCourse SET FCardState = 1
|
|
|
|
|
|
UPDATE TImpMemberCourse SET FIsCourse = 0 WHERE FIsCourse IS NULL
|
|
SELECT * FROM TImpMemberCourse WHERE ISNULL(FIsCourse,0) = 0
|
|
|
|
UPDATE A
|
|
SET A.FCardTypeId = B.FId
|
|
FROM TImpMemberCourse AS A,TMemberCardType AS B
|
|
WHERE A.套餐名称= B.FName
|
|
AND FIsCourse = 0
|
|
|
|
|
|
|
|
SELECT 套餐名称 FROM TImpMemberCourse
|
|
WHERE FIsCourse = 0
|
|
GROUP BY 套餐名称
|
|
|
|
SELECT * FROM TImpMember WHERE FCardNo = '288103120'
|
|
|
|
|
|
INSERT INTO TMemberCardType(FId,FNo,FName,FValueType,FMaterial,FPrice,FCardFeeAmt,FAcctId,FDeposit,FAcctId2,FDeposit2,
|
|
FAcctId3,FDeposit3,FTimes,FAlertBalance,FPaySmsFee,FTimeCard,FValidityPeriod,FValidityPeriodUnit,FExpiredDate,
|
|
FDefaultMemo,FCreateTime,FAllowSale,FAllowRchg,FEnabled,FOrder,FDeptId,FSalePointMode,FSalePointValue,FRchgPointMode,FRchgPointValue)
|
|
SELECT dbo.FNNewId() AS FId,套餐名称 AS FNo,套餐名称 AS FName,1 AS ValueType,1 AS FMaterial,0 AS FPrice,0 AS FCardFeeAmt,0 AS FAcctId,0 AS FDeposit,0 AS FAcctId2,0 AS FDeposit2,0 AS FAcctId3,0 AS FDeposit3,0 AS FTimes,0 AS FAlertBalance,0 AS FPaySmsFee,0 AS FTimeCard,0 AS FValidityPeriod,0 AS FValidityPeriodUnit,'' AS FExpiredDate,'' AS FDefaultMemo,'' AS FCreateTime,0 AS FAllowSale,0 AS FAllowRchg,1 AS FEnabled,0 AS FOrder,0 AS FDeptId,1,0,1,0
|
|
FROM TImpMemberCourse
|
|
WHERE FIsCourse = 0
|
|
GROUP BY 套餐名称
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FIsCourse = 0 AND FCardTypeId IS NULL
|
|
|
|
SELECT CardId,sum(1) FROM TImpMemberCourse WHERE FIsCourse = 0
|
|
AND FBalance != 0
|
|
GROUP BY CardId
|
|
ORDER BY SUM(1) DESC
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE CardId = '1e9da80a-1c14-49d5-865a-a54900c74a7f'
|
|
AND FIsCourse = 0
|
|
|
|
|
|
DELETE TMemberCard WHERE FCardId NOT IN(SELECT FCardId FROM TMemberCardAccount )
|
|
AND FCardNo LIKE '%.4'
|
|
|
|
DELETE TMemberCardAccount WHERE FBalance = 0
|
|
AND FCardId IN(SELECT FCardId FROM TMemberCard WHERE FCardNo LIKE '%.3')
|
|
|
|
DELETE TMemberCardAcctHist WHERE FCardId NOT IN(SELECT FCardId FROM TMemberCard)
|
|
|
|
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 AS FExpiredDate,
|
|
0 AS FTotalTimes,
|
|
0 AS FUsedTimes,
|
|
FCompId AS FSaleCompId,
|
|
0 AS FSaleBillId,
|
|
'' AS FMemo
|
|
FROM TImpMemberCourse
|
|
-- WHERE FCardTypeId IS NOT NULL
|
|
where FIsCourse = 0
|
|
|
|
UPDATE TMemberCardType SET FValidityPeriodUnit = 1,FValidityPeriod = 100
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE FIsCourse = 1
|
|
|
|
SELECT * FROM TImpM
|
|
|
|
UPDATE TMemberCardType SET FAllowRchg = 1
|
|
|
|
|
|
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 TImpMemberCourse
|
|
WHERE 1=1
|
|
AND FIsCourse = 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(),'从老系统中导入'
|
|
FROM TImpMemberCourse AS A
|
|
WHERE 1=1
|
|
AND FIsCourse = 0
|
|
|
|
SELECT * FROM TImpMemberCourse WHERE 状态 = '已退卡' AND FExpir
|
|
|
|
SELECT * FROM TMemberCard
|
|
|
|
SELECT FCardNO,sum(1)
|
|
FROM TImpMemberCourse
|
|
where FIsCourse = 0
|
|
GROUP BY FCardNO ORDER BY sum(1) DESC
|
|
|
|
|
|
SELECT * from TImpMemberCourse where FCardNO IN (SELECT FCardNO FROM TMemberCard)
|
|
|
|
|
|
--卡号
|
|
UPDATE A
|
|
SET A.FCardNo = B.卡号
|
|
FROM TImpMemberCourse AS A,TImpMemberCard AS B
|
|
WHERE A.CardId = B.CardId
|
|
|
|
UPDATE TImpMemberCourse
|
|
SET FCardNo = FCardNo + '.1'
|
|
WHERE FCardNo IN(SELECT FCardNo FROM TImpMemberCard)
|
|
|
|
|
|
ALTER TABLE TImpMemberCourse ADD FIndex INT
|
|
ALTER TABLE TImpMemberCourse ADD FSeq INT IDENTITY(1,1) NOT NULL
|
|
|
|
|
|
UPDATE A
|
|
SET A.FIndex = (SELECT COUNT(1) FROM TImpMemberCourse AS B WHERE B.FCardNo = A.FCardNo AND A.FIsCourse = B.FIsCourse AND B.FSeq > A.FSeq ) + 1
|
|
FROM TImpMemberCourse AS A
|
|
WHERE A.FIsCourse = 0
|
|
|
|
|
|
SELECT * FROM TImpMemberCourse
|
|
|
|
UPDATE TMemberCard SET FExpiredDate = '9000-01-01' WHERE FExpiredDate = '1900-01-01'
|
|
|
|
|
|
UPDATE TImpMemberCourse SET FCardno = REPLACE(FCardno,'.1','')
|
|
UPDATE TImpMemberCourse SET FCardno = REPLACE(FCardno,'.2','')
|
|
UPDATE TImpMemberCourse SET FCardno = REPLACE(FCardno,'.3','')
|
|
UPDATE TImpMemberCourse SET FCardno = REPLACE(FCardno,'.4','')
|
|
UPDATE TImpMemberCourse SET FCardno = REPLACE(FCardno,'.5','')
|
|
UPDATE TImpMemberCourse SET FCardno = REPLACE(FCardno,'.9','')
|
|
UPDATE TImpMemberCourse SET FCardno = REPLACE(FCardno,'.8','')
|
|
|
|
UPDATE TImpMemberCourse SET FCardno = FCardno + '.' + CAST(Findex AS VARCHAR)
|
|
WHERE FIndex >1
|
|
|
|
|
|
|
|
--处理疗程储值卡
|
|
|
|
|
|
SELECT * FROM TImpMember
|
|
|
|
SELECT 实收余额 + 赠送余额 FROM TImpMember
|
|
WHERE CAST(实收余额 AS FLOAT) + CAST(赠送余额 AS FLOAT) > 0
|
|
|
|
SELECT * FROM TImpMember
|
|
|
|
UPDATE TMemberCardType SET FName ='年卡储值' WHERE FNo = 'Course'
|
|
|
|
--
|
|
|
|
|
|
|
|
UPDATE TMemberCard SET FCardNO = FCardNo + '.8' WHERE FCardNo NOT LIKE '%.%'
|
|
AND FCardNo + '.8' NOT IN(SELECT FCardNO FROM TMemberCard)
|
|
AND FCardId != 0
|
|
|
|
|
|
SELECT * FROM TMemberCard WHERE FCardNo NOT LIKE '%.%'
|
|
|
|
|
|
use qingdao_vstar
|
|
|
|
SELECT * FROM TMember WHERE FMobilePhone = '15269218016'
|
|
|
|
UPDATE TMemberCardType SET FMATERIAL = 2
|
|
|
|
SELECT * FROM TMemberCardType
|
|
|
|
SELECT * FROM TImpMemberCourseHist
|
|
|
|
SELECT * FROM TImpMemberCourse
|
|
|
|
UPDATE A
|
|
SET A.FCardNO = B.FCardnO
|
|
from TCOnsumeBill AS A,TMemberCard AS B
|
|
WHERE A.FCardId = B.FCardId
|
|
|
|
SELECT * FROM TImpMemberCourse
|
|
|
|
|
|
SELECT * FROM TImpMember WHERE FCardNO = ''
|
|
|
|
SELECT * FROM TImpMember WHERE 姓名 LIKE '%牛西%'
|
|
|
|
|
|
UPDATE TMemberCard SET FExpiredDate = '9000-01-01' WHERE
|
|
|
|
|
|
UPDATE A
|
|
SET A.FExpiredDate = B.FExpiredDate
|
|
FROM TImpMemberCourse AS B,TMemberCard AS A
|
|
WHERE A.FCardID = b.FCardId
|
|
|
|
SELECT * FROM TMemberCard
|
|
|
|
|
|
SELECT * FROM TImpMemberCourseHist
|
|
|
|
|
|
select left('m7PhMEYqsWVxMkFlidZ1XKus8ngdp85S3KfaJqU9HGT',32)
|
|
|
|
|
|
ALTER TABLE TImpMemberCourseHist ADD FEmployees VARCHAR(1000)
|
|
ALTER TABLE TImpMemberCourseHist ADD FRemainTimes DECIMAL(18,6)
|
|
ALTER TABLE TImpMemberCourseHist ADD FBillType VARCHAR(1000)
|
|
ALTER TABLE TImpMemberCourseHist ADD FBizType VARCHAR(1000)
|
|
ALTER TABLE TImpMemberCourseHist ADD FId BIGINT
|
|
ALTER TABLE TImpMemberCourseHist ADD FInPurAmount DECIMAL(18,6)
|
|
ALTER TABLE TImpMemberCourseHist ADD FOutPurAmount DECIMAL(18,6)
|
|
ALTER TABLE TImpMemberCourseHist ADD FInPurTimes DECIMAL(18,6)
|
|
ALTER TABLE TImpMemberCourseHist ADD FOutPurTimes DECIMAL(18,6)
|
|
|
|
|
|
UPDATE TImpMemberCourseHist SET FEmployees = 服务员工
|
|
UPDATE TImpMemberCourseHist SET FId = dbo.FNNewId()
|
|
|
|
UPDATE TImpMemberCourseHist SET FBillType = 'DR'
|
|
UPDATE TImpMemberCourseHist SET FBizType =变更类型
|
|
|
|
UPDATE TImpMemberCourseHist SET FRemainTimes = CAST(当前余次 AS FLOAT)
|
|
UPDATE TImpMemberCourseHist SET FBillType ='DR_XF' WHERE 变更类型 = '消费'
|
|
UPDATE TImpMemberCourseHist SET FBizType ='DR_Item' WHERE 变更类型 = '消费'
|
|
UPDATE TImpMemberCourseHist SET FBillType ='DR_KK' WHERE 变更类型 = '购买'
|
|
UPDATE TImpMemberCourseHist SET FBizType ='DR_CourseSale' WHERE 变更类型 = '购买'
|
|
|
|
DELETE TMemberCourseLog WHERE FBilltYPE LIKE 'DR%'
|
|
|
|
|
|
SELECT * FROM TImpMemberCourseHist WHERE 操作时间 = '2016-10-08 10:14:28'
|
|
|
|
SELECT * FROM TMember WHERE FMemNO = '532106105'
|
|
|
|
SELECT * FROM TImpMemberCourseHist WHERE FMemId = 1630559165388138822
|
|
|
|
UPDATE TImpMemberCourseHist SET FInPurTimes = cast(变更次数 AS FLOAT) WHERE cast(变更次数 AS FLOAT) >= 0
|
|
UPDATE TImpMemberCourseHist SET FOutPurTimes = cast(变更次数 AS FLOAT)WHERE cast(变更次数 AS FLOAT) < 0
|
|
|
|
UPDATE TImpMemberCourseHist SET FInPurAmount = cast(变更金额 AS FLOAT) WHERE cast(变更金额 AS FLOAT) >= 0
|
|
UPDATE TImpMemberCourseHist SET FOutPurAmount = cast(变更金额 AS FLOAT)WHERE cast(变更金额 AS FLOAT) < 0
|
|
|
|
|
|
INSERT INTO TMemberCourseLog(FId,FMemId,FCardId,FCourseId,FItemId,FPackId,FTime,FInPurTimes,FOutPurTimes,FInPurAmount,FOutPurAmount,FInFreeTimes,FOutPurTimes,FOutFreeTimes,FRemainPurTimes,FRemainFreeTimes,FCompId,FBillType,FBizType,FBillId,FBillNo,FBillDetailId,FRecordTime,FMemo,FEmployees)
|
|
SELECT Id = dbo.FNNewId(),MemId = FMemId,CardId = FCardId,CourseId = FCardTypeId,ItemId = FItemId,PackId = 0,Time = 操作时间,FInPurTimes,FOutPurTimes,FInPurAmount,FOutPurAmount,InFreeTimes =0,
|
|
OutPurTimes = 0,OutFreeTimes = 0,RemainPurTimes = FRemainTimes,RemainFreeTimes = 0,CompId = FCompId ,BillType = FBillType,BizType = FBizType,BillId = 0,BillNo = '',BillDetailId = 0,RecordTime = getdate(),Memo = '老系统导入',FEmployees
|
|
FROM TImpMemberCourseHist AS A
|
|
WHERE FCardId IN(SELECT FId FROM TMemberCourse)
|
|
|
|
UPDATE A
|
|
SET A.FItemId = B.FId
|
|
FROM TImpMemberCourseHist AS A,TItem AS B
|
|
WHERE A.项目名称= b.fname
|
|
|
|
UPDATE A
|
|
SET A.FItemId = B.FItemid
|
|
FROM TImpMemberCourseHist AS A,TCourse AS B
|
|
WHERE A.FCardTypeId= b.FId
|
|
|
|
|
|
-- AND 套餐名称 LIKE '%克%'
|
|
|
|
SELECT MemId,FMemId FROM TImpMemberInfo_1 WHERE FMemId NOT IN(SELECT FMemId FROM TImpMemberTransHist)
|
|
AND MemId IS NULL
|
|
|
|
http://www.bsgrj.com/MemberCard/ConsumeLog?memberId=d249be91-2136-45a9-9a08-a781015453ef
|
|
|
|
SELECT * FROM TImpMemberInfo_1 WHERE MemId = 'd249be91-2136-45a9-9a08-a781015453ef'
|
|
|
|
SELECT top 11 * FROM TImpMemberTransHist WITH(NOLOCK)
|
|
|
|
USE ChangZhi_XiaoJiu
|
|
|
|
|
|
SELECT * FROM
|
|
|
|
ALTER TABLE TImpMemberTransHist ADD FCardId BIGINT
|
|
UPDATE A
|
|
SET A.FCardId = B.FCardId
|
|
FROM TMemberCard AS B,TImpMemberTransHist as a
|
|
WHERE A.卡号=B.FCardNo
|
|
|
|
UPDATE TImpMemberTransHist SET FCardId = 0 WHERE FCardId IS NULL
|
|
|
|
ALTER TABLE TMemberOldTransHist ALTER COLUMN FEmployee VARCHAR(2000)
|
|
|
|
SELECT MAX(LEN(服务员工)) FROM TImpMemberTransHist
|
|
|
|
|
|
INSERT TMemberOldTransHist(FId,FCompId,FMemId,FTime,FCardId,FBIllType,FBizType,FTransNo,FCOntent,FPrice,FQuantity,FAmount,FPayment,Femployee,FMemo)
|
|
SELECT dbo.FNNewId(),1,FMemId,ISNULL(单据日期,'1753-01-01'),FCardId, '' 结算类型, '' 服务员工,ISNULL( 单据号,'') AS FTransNo,
|
|
ISNULL(消费项目,''),金额,1,金额,ISNULL('',''),'', '' AS FMemo
|
|
FROM TImpMemberTransHist WITH(NOLOCK)
|
|
WHERE FMemId IS NOT NULL
|
|
AND FMemId IN(SELECT FMemId FROM TMember)
|
|
|
|
UPDATE A
|
|
SET A.F |