|
|
|
|
|
SELECT * FROM TMemberArrear WHERE FBillType = 'DR'
|
|
|
|
|
|
|
|
|
|
|
|
select gcl09c from gcm10 WITH (NOLOCK) where gcl00c='001' and gcl03c='002' and gcl06i=5 order by gcl02d desc
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SELECT * FROM fromServer.qd_YANSE_CY8fnFYhg86gn9.dbo.gcm10 where gcl06i=5 order by gcl02d desc
|
|
|
|
|
|
|
|
|
|
|
|
SELECT * FROM TEmployee WHERE FNo NOT IN(SELECT haa01c FROM fromServer.qd_YANSE_CY8fnFYhg86gn9.dbo.ham01)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
IF EXISTS (SELECT 1
|
|
|
|
|
|
FROM dbo.sysreferences r
|
|
|
|
|
|
JOIN dbo.sysobjects o
|
|
|
|
|
|
ON (o.id = r.constid AND o.type = 'F')
|
|
|
|
|
|
WHERE r.fkeyid = object_id('TMemberArrearEmployee')
|
|
|
|
|
|
AND o.name = 'FK_MEMBER_ARREAR_EMPLOYEE_MEM_ID')
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
ALTER TABLE TMemberArrearEmployee
|
|
|
|
|
|
DROP CONSTRAINT FK_MEMBER_ARREAR_EMPLOYEE_MEM_ID
|
|
|
|
|
|
END
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
IF EXISTS (SELECT 1
|
|
|
|
|
|
FROM sysindexes
|
|
|
|
|
|
WHERE id = object_id('TMemberArrearEmployee')
|
|
|
|
|
|
AND name = 'IX_MEMBER_ARREAR_EMPLOYEE_MEM_ID'
|
|
|
|
|
|
AND indid > 0
|
|
|
|
|
|
AND indid < 255)
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
DROP INDEX dbo.TMemberArrearEmployee.IX_MEMBER_ARREAR_EMPLOYEE_MEM_ID
|
|
|
|
|
|
END
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
IF EXISTS (SELECT 1
|
|
|
|
|
|
FROM sysindexes
|
|
|
|
|
|
WHERE id = object_id('TMemberArrearEmployee')
|
|
|
|
|
|
AND name = 'IX_MEMBER_ARREAR_EMPLOYEE_CLUSTERED'
|
|
|
|
|
|
AND indid > 0
|
|
|
|
|
|
AND indid < 255)
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
DROP INDEX dbo.TMemberArrearEmployee.IX_MEMBER_ARREAR_EMPLOYEE_CLUSTERED
|
|
|
|
|
|
END
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
/*==============================================================*/
|
|
|
|
|
|
/* Table: TMemberArrearEmployee */
|
|
|
|
|
|
/*==============================================================*/
|
|
|
|
|
|
drop table #MemberArrearEmployee
|
|
|
|
|
|
|
|
|
|
|
|
CREATE TABLE #MemberArrearEmployee
|
|
|
|
|
|
(
|
|
|
|
|
|
FId bigint not null,
|
|
|
|
|
|
FMemId bigint not null,
|
|
|
|
|
|
FArrearId bigint not null,
|
|
|
|
|
|
FWorkType bigint not null,
|
|
|
|
|
|
FEmpId bigint not null,
|
|
|
|
|
|
FShareRate Decimal(18,6) not null
|
|
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
declare @cardNo varchar(20)
|
|
|
|
|
|
declare @memId bigint
|
|
|
|
|
|
declare @arrearId bigint
|
|
|
|
|
|
|
|
|
|
|
|
declare arrear_cur cursor for
|
|
|
|
|
|
select distinct FMemId,FId,(select FNo FROM TMemberCard WHERE FId = A.FCardId) from TMemberArrear AS A WHERE FBillType = 'DR'
|
|
|
|
|
|
|
|
|
|
|
|
open arrear_cur
|
|
|
|
|
|
fetch arrear_cur into @memId,@arrearId, @cardNo
|
|
|
|
|
|
while @@fetch_status=0
|
|
|
|
|
|
begin
|
|
|
|
|
|
declare @BillNo VARCHAR(30)
|
|
|
|
|
|
declare @EmpNo1 VARCHAR(20)
|
|
|
|
|
|
declare @EmpNo2 VARCHAR(20)
|
|
|
|
|
|
declare @EmpNo3 VARCHAR(20)
|
|
|
|
|
|
declare @EmpNo4 VARCHAR(20)
|
|
|
|
|
|
declare @EmpNo5 VARCHAR(20)
|
|
|
|
|
|
declare @EmpNo6 VARCHAR(20)
|
|
|
|
|
|
declare @EmpNo7 VARCHAR(20)
|
|
|
|
|
|
declare @EmpNo8 VARCHAR(20)
|
|
|
|
|
|
declare @EmpNo9 VARCHAR(20)
|
|
|
|
|
|
declare @EmpNo10 VARCHAR(20)
|
|
|
|
|
|
|
|
|
|
|
|
declare @Rate1 float
|
|
|
|
|
|
declare @Rate2 float
|
|
|
|
|
|
declare @Rate3 float
|
|
|
|
|
|
declare @Rate4 float
|
|
|
|
|
|
declare @Rate5 float
|
|
|
|
|
|
declare @Rate6 float
|
|
|
|
|
|
declare @Rate7 float
|
|
|
|
|
|
declare @Rate8 float
|
|
|
|
|
|
declare @Rate9 float
|
|
|
|
|
|
declare @Rate10 float
|
|
|
|
|
|
|
|
|
|
|
|
SELECT top 1
|
|
|
|
|
|
@BillNo = gcl01c,
|
|
|
|
|
|
@EmpNo1 = gcl09c,
|
|
|
|
|
|
@EmpNo2 = gcl17c,
|
|
|
|
|
|
@EmpNo3 = gcl19c,
|
|
|
|
|
|
@EmpNo4 = gcl22c,
|
|
|
|
|
|
@EmpNo5 = gcl24c,
|
|
|
|
|
|
@EmpNo6 = gcl26c,
|
|
|
|
|
|
@EmpNo7 = gcl28c,
|
|
|
|
|
|
@EmpNo8 = gcl30c,
|
|
|
|
|
|
@EmpNo9 = gcl32c,
|
|
|
|
|
|
@EmpNo10 = gcl34c ,
|
|
|
|
|
|
@Rate1 = gcl16f,
|
|
|
|
|
|
@Rate2 = gcl18f,
|
|
|
|
|
|
@Rate3 = gcl20f,
|
|
|
|
|
|
@Rate4 = gcl23f,
|
|
|
|
|
|
@Rate5 = gcl25f,
|
|
|
|
|
|
@Rate6 = gcl27f,
|
|
|
|
|
|
@Rate7 = gcl29f,
|
|
|
|
|
|
@Rate8 = gcl31f,
|
|
|
|
|
|
@Rate9 = gcl33f,
|
|
|
|
|
|
@Rate10 = gcl35f
|
|
|
|
|
|
FROM fromServer.qd_YANSE_CY8fnFYhg86gn9.dbo.GCM10
|
|
|
|
|
|
where gcl03c = @cardNo and gcl06i=5 AND isnull(gcl94d,'') != '' order by gcl02d desc
|
|
|
|
|
|
|
|
|
|
|
|
IF ISNULL(@EmpNo1,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12001 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo1) AS FEmpId,@Rate1 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
IF ISNULL(@EmpNo2,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12002 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo2) AS FEmpId,@Rate2 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
IF ISNULL(@EmpNo3,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12003 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo3) AS FEmpId,@Rate3 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
IF ISNULL(@EmpNo4,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12004 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo4) AS FEmpId,@Rate4 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
IF ISNULL(@EmpNo5,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12005 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo5) AS FEmpId,@Rate5 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
IF ISNULL(@EmpNo6,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12006 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo6) AS FEmpId,@Rate6 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
IF ISNULL(@EmpNo7,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12007 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo7) AS FEmpId,@Rate7 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
IF ISNULL(@EmpNo8,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12008 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo8) AS FEmpId,@Rate8 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
IF ISNULL(@EmpNo9,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12009 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo9) AS FEmpId,@Rate9 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
IF ISNULL(@EmpNo10,'') != ''
|
|
|
|
|
|
BEGIN
|
|
|
|
|
|
INSERT #MemberArrearEmployee(FId,FMemId,FArrearId,FWorkType,FEmpId,FShareRate)
|
|
|
|
|
|
SELECT dbo.FNGetNewId() AS FId,@MemId AS FMemId,@ArrearId AS FArrearId,
|
|
|
|
|
|
12010 AS FWorkType, (SELECT FId FROM TEmployee WHERE FNo = @EmpNo10) AS FEmpId,@Rate10 AS FShareRate
|
|
|
|
|
|
END
|
|
|
|
|
|
print @BillNo
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
fetch arrear_cur into @memId,@arrearId, @cardNo
|
|
|
|
|
|
end
|
|
|
|
|
|
close arrear_cur
|
|
|
|
|
|
deallocate arrear_cur
|
|
|
|
|
|
|
|
|
|
|
|
delete A
|
|
|
|
|
|
from TMemberArrearEmployee AS A
|
|
|
|
|
|
WHERE A.FArrearId IN(SELECT B.FId FROM TMemberArrear AS B WHERe B.FBillType = 'DR')
|
|
|
|
|
|
|
|
|
|
|
|
INSERT TMemberArrearEmployee
|
|
|
|
|
|
select * from #MemberArrearEmployee
|
|
|
|
|
|
WHERE FMemId NOT IN(SELECT FId FROM TMember)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
select * from TMember WHERE FId IN(SELECT FMemId FROM TMemberArrear WHERE FBillType = 'DR_KK')
|
|
|
|
|
|
SELECT * FROM TMemberArrearEmployee WHERE FMemId = 5976012988745209504
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DECLARE @CardNo VARCHAR(20)
|
|
|
|
|
|
DECLARE @Date DATETIME
|
|
|
|
|
|
SELECT TOP 1 @CardNo = FNo,@Date = FBirthday FROM TMember ORDER BY FNo
|
|
|
|
|
|
print @CardNo
|
|
|
|
|
|
print @Date
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
UPDATE TMemberArrear SET FBillType = 'DR_KK',FBizType = 'DR_CardSale' WHERE FBillType = 'DR' AND FContentType = 'Account'
|
|
|
|
|
|
UPDATE TMemberSaleRepay SET FArrearBillType = 'DR_KK',FArrearBizType = 'DR_CardSale' WHERE FArrearBillType = 'DR' AND FArrearContentType = 'Account'
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SELECT * FROM TMemberSaleRepay
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|
|
|
|
|
INSERT TMemberSaleEmployee( FId , FBillId ,FDetailId, FWorkType , FDeptId,FEmpId ,FShareRate, FPerf ,FComm)
|
|
|
|
|
|
SELECT dbo.FNGetNewId(), A.FBillId,A.FId,B.FWorkType,0,B.FEmpId,B.FShareRate,0,0
|
|
|
|
|
|
FROM TMemberSaleRepay AS A,TMemberArrearEmployee AS B
|
|
|
|
|
|
WHERE A.FArrearId = B.FArrearId
|
|
|
|
|
|
AND B.FArrearId IN (SELECT FId FROM TMemberArrear WHERE FBillType = 'DR_KK')
|
|
|
|
|
|
|
|
|
|
|
|
SELECT * FROM TMemberArrearEmployee WHERE FMemId = (SELECT FId FROM TMember WHERE FNo = '0532000000118')
|
|
|
|
|
|
|
|
|
|
|
|
UPDATE TMemberArrearEmployee SET FShareRate = 1 WHERE FShareRate = 0
|
