zhensai
/
sql-tools
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
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.
5 Commits
1 Branch
0 Tags
233 MiB
Tag: Branch: Tree: main
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
sql-tools/Import/不见不散/美顾问.sql

526 lines
14 KiB
Raw Permalink Normal View History Unescape

'init'
1 year ago
SELECT * FROM TMember
SELECT * FROM TMGWMember
WHERE <EFBFBD><EFBFBD>ǰ״̬
SELECT * FROM TMGWMember
WHERE 1=1
--AND <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> = ''
--AND FDeleted = 0
AND <EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD>!= '1<EFBFBD><EFBFBD>-1<EFBFBD><EFBFBD>[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>]'
WHERE <EFBFBD><EFBFBD>ǰ״̬ = 'ע<EFBFBD><EFBFBD>'
SELECT <EFBFBD><EFBFBD>ǰ״̬ FROM TMGWMember
GROUP BY <EFBFBD><EFBFBD>ǰ״̬
--
IF OBJECT_ID('TMGWCompany') IS NULL
BEGIN
CREATE TABLE TMGWCompany
(
FCompName VARCHAR(50),
FCompNo VARCHAR(50),
FCompId BIGINT
)
END
SELECT FName FROM TCompany
SELECT <EFBFBD><EFBFBD><EFBFBD><EFBFBD> FROM TMGWMember
group by <EFBFBD><EFBFBD><EFBFBD><EFBFBD>
DELETE TMGWCompany
INSERT TMGWCompany(FCompName,FCompNo)
SELECT '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ɢ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>԰<EFBFBD>꣩','001'
INSERT TMGWCompany(FCompName,FCompNo)
SELECT '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ɢ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>·<EFBFBD>꣩','002'
INSERT TMGWCompany(FCompName,FCompNo)
SELECT '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ɢ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>·<EFBFBD>꣩','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 <EFBFBD><EFBFBD>ǰ״̬ = 'ע<EFBFBD><EFBFBD>'
UPDATE TMGWMember SET FDeleted = 1 WHERE <EFBFBD><EFBFBD>ǰ״̬ = '<EFBFBD>˿<EFBFBD>'
UPDATE TMGWMember SET FDeleted = 0 WHERE <EFBFBD><EFBFBD>ǰ״̬ = '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
UPDATE TMGWMember SET FDeleted = 0 WHERE FDeleted IS NULL
SELECT * FROM TMGWMember WHERE <EFBFBD><EFBFBD>ǰ״̬ = '<EFBFBD><EFBFBD>ʧ'
SELECT <EFBFBD><EFBFBD>ǰ״̬ FROM TMGWMember
GROUP BY <EFBFBD><EFBFBD>ǰ״̬
--<EFBFBD><EFBFBD>ԱID
UPDATE TMGWMember SET FMemId = dbo.FNNewId() WHERE FMemId IS NULL
--<EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE TMGWMember SET FMemNo = <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE TMGWMember SET FName = <EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--<EFBFBD>ֻ<EFBFBD>
UPDATE TMGWMember SET FMobilePhone = <EFBFBD><EFBFBD>ϵ<EFBFBD>绰
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>
UPDATE TMGWMember SET FCreateDate = <EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>
--״̬
UPDATE TMGWMember SET FState = 1 WHERE <EFBFBD><EFBFBD>ǰ״̬='<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
UPDATE TMGWMember SET FState = 5 WHERE <EFBFBD><EFBFBD>ǰ״̬='<EFBFBD><EFBFBD>ʧ'
UPDATE TMGWMember SET FState = 1 WHERE <EFBFBD><EFBFBD>ǰ״̬='<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
SELECT * FROM TMGWMember WHERE <EFBFBD><EFBFBD>ǰ״̬ = '<EFBFBD><EFBFBD>ʧ'
--<EFBFBD><EFBFBD>ע
UPDATE TMGWMember SET FMemo = <EFBFBD><EFBFBD>ע<EFBFBD><EFBFBD>Ϣ
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE TMGWMember SET FPoint = <EFBFBD><EFBFBD>ǰ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--<EFBFBD><EFBFBD>˾
UPDATE A
SET A.FCompId = B.FCompId
FROM TMGWMember AS A,TMGWCompany AS B
WHERE A.<EFBFBD><EFBFBD><EFBFBD><EFBFBD> = B.FCompName
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE TMGWMember SET FBirthdayType = 'G' WHERE <EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD> LIKE '%<EFBFBD><EFBFBD><EFBFBD><EFBFBD>%'
UPDATE TMGWMember SET FBirthdayType = 'L' WHERE <EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD> LIKE '%ũ<EFBFBD><EFBFBD>%'
UPDATE A SET FBirthday =
-- SELECT
CAST( '9999-' +
REPLACE(
REPLACE(
REPLACE(
REPLACE(<EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,'<EFBFBD><EFBFBD>',''),'<EFBFBD><EFBFBD>',''),'[ũ<EFBFBD><EFBFBD>]',''),'[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>]','') AS DATETIME)
FROM TMGWMember AS A
WHERE <EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD> LIKE '%<EFBFBD><EFBFBD><EFBFBD><EFBFBD>%'
AND <EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD> != '1<EFBFBD><EFBFBD>-1<EFBFBD><EFBFBD>[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>]'
--ɾ<EFBFBD><EFBFBD><EFBFBD>ظ<EFBFBD>
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
-----<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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 <EFBFBD>ײ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> FROM TMGWCard
GROUP BY <EFBFBD>ײ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--<EFBFBD><EFBFBD>ʼ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD><EFBFBD>ϵ
CREATE TABLE TMGWCardType
(
FCardTypeName VARCHAR(50),
FCardTypeId BIGINT,
FCardTypeNo VARCHAR(30)
)
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('400<EFBFBD><EFBFBD>400','04')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('498Ӫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>500','06')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('<EFBFBD><EFBFBD>1000<EFBFBD><EFBFBD>400','09')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('<EFBFBD><EFBFBD>500<EFBFBD><EFBFBD>150','07')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('498Ӫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','11')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('300<EFBFBD><EFBFBD>300','03')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('<EFBFBD><EFBFBD>1000<EFBFBD><EFBFBD>499<EFBFBD>̷<EFBFBD><EFBFBD><EFBFBD>Ⱦ<EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD>','10')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('<EFBFBD><EFBFBD>800<EFBFBD><EFBFBD>300','08')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('500<EFBFBD><EFBFBD>500','05')
INSERT TMGWCardType(FCardTypeName,FCardTypeNo)
VALUES('498Ӫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>500','06')
UPDATE A
SET A.FCardTypeId = B.FId
FROM TMGWCardType AS A,TMemberCardType AS B
WHERE A.FCardTypeNo = B.FNo
<EFBFBD>Ƴ̺ʹ<EFBFBD>ֵ<EFBFBD><EFBFBD>
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 ״̬='<EFBFBD><EFBFBD>ɾ<EFBFBD><EFBFBD>'
UPDATE TMGWCard SET FDeleted = 0 WHERE ״̬!='<EFBFBD><EFBFBD>ɾ<EFBFBD><EFBFBD>'
UPDATE TMGWCard SET FDeleted = 1 WHERE <EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>= ''
UPDATE TMGWCard SET FDeleted = 1 WHERE <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>=0
UPDATE A SET A.FDeleted = B.FDeleted
FROM TMGWCard AS A,TMGWMember AS B
WHERE A.<EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> = B.<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
AND B.FDeleted = 1
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE A
SET A.FTypeId = B.FCardTypeId
FROM TMGWCard AS A,TMGWCardType AS B
WHERE <EFBFBD>ײ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> = B.FCardTypeName
UPDATE A
SET A.FTypeId = 0
FROM TMGWCard AS A
WHERE A.FTypeId IS NULL
--<EFBFBD><EFBFBD>˾
UPDATE A
SET A.FCompId = B.FCompId
FROM TMGWCard AS A,TMGWCompany AS B
WHERE A.<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> = B.FCompName
--<EFBFBD><EFBFBD>ID
UPDATE TMGWCard SET FCardId = dbo.FNNewId() WHERE FCardId IS NULL
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE TMGWCard SET FCardNo = <EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--<EFBFBD><EFBFBD>ԱID
UPDATE A
SET A.FMemId = B.FMemId
FROM TMGWCard AS A,TMGWMember AS B
WHERE A.FCardNo = B.FMemNo
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE A
SET A.FCreateDate = <EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>,
A.FExpiredDate = <EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>
FROM TMGWCard AS A
--״̬
UPDATE TMGWCard SET FState = 1 WHERE ״̬!= '<EFBFBD><EFBFBD>ɾ<EFBFBD><EFBFBD>'
UPDATE A
SET A.FState = B.FState
FROM TMGWCard AS A,TMGWMember AS B
WHERE A.FMemId = B.FMemId
AND B.FState = 5 --<EFBFBD><EFBFBD>ʧ<EFBFBD><EFBFBD>
--<EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE TMGWCard SET FBalance = <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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.<EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> = A.<EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> AND A.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD> > B.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD> 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 = <EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> + '.' + CAST(FIndex AS VARCHAR(10))
WHERE ISNULL(FTypeId,0) != 0
AND FIndex != 1
AND FDeleted = 0
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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(),'<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʵ<EFBFBD><EFBFBD><EFBFBD>'
FROM TMGWCard AS A
WHERE ISNULL(FTypeId,0) != 0
AND FDeleted = 0
--<EFBFBD>Ƴ<EFBFBD>
CREATE TABLE TMGWCourse
(
FCourseName VARCHAR(30),
FCourseNo VARCHAR(30),
FCourseId BIGINT
)
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʦ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','2012L6')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('bjbsӪ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','6011L4')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('bjbsϴ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>','1011L1')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('Ⱦ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','5011L2')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('<EFBFBD><EFBFBD>Ⱦ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>','7011L2')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('bjbs<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','5012L1')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('bjbsͷƤ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>','6015L2')
INSERT TMGWCourse(FCourseName,FCourseNo)
VALUES('<EFBFBD>̷<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','3011L2')
UPDATE A
SET A.FCourseId = B.FId
FROM TMGWCourse AS A,TCOurse AS B
WHERE A.FCourseNo = B.FNo
--ʹ<EFBFBD>ô<EFBFBD><EFBFBD><EFBFBD>
UPDATE TMGWCard SET FUserPurTimes =
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ƴ<EFBFBD>
UPDATE A
SET A.FCourseId = B.FCourseId
FROM TMGWCard AS A,TMGWCourse AS B
WHERE A.<EFBFBD>ײ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> = B.FCourseName
UPDATE A
SET A.FItemId = B.FItemId
FROM TMGWCard AS A,TCourse AS B
WHERE A.FCourseId = B.FId
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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 <EFBFBD>ײ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,'-->' FROM TMGWCard
GROUP BY <EFBFBD>ײ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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
--Ƿ<EFBFBD><EFBFBD>
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.<EFBFBD><EFBFBD>ǰǷ<EFBFBD><EFBFBD>,0,0,A.<EFBFBD><EFBFBD>ǰǷ<EFBFBD><EFBFBD>
FROM TMGWMember AS A
WHERE A.<EFBFBD><EFBFBD>ǰǷ<EFBFBD><EFBFBD> > 0