/*三明电力临时卡处理SQL代码,临时卡为专门一类卡，消费时五折优惠，月消费有限额，采取策略是“钱多存，消费少报”*/
SELECT dbo.T_Station.StatName AS 工作站, dbo.T_Cashiers.Name AS 出纳员,
lll.OpDt AS 日期, lll.InFare AS 存款额, lll.InCount AS 存款次数, lll.OutFare AS 取款额,
lll.OutCount AS 取款次数, ISNULL(lll.suminfare, 0) / 2 AS 让利额, ISNULL(lll.countinfare,
0) AS 让利次数, (lll.InFare - lll.OutFare) - ISNULL(lll.suminfare, 0)
/ 2 AS 存取款应缴金额
FROM (SELECT dbo.T_CashDay.StatID, dbo.T_CashDay.OpDt, dbo.T_CashDay.CashID,
dbo.T_CashDay.InFare, dbo.T_CashDay.InCount, dbo.T_CashDay.OutFare,
dbo.T_CashDay.OutCount, ll.suminfare, ll.countinfare
FROM dbo.T_CashDay LEFT OUTER JOIN /*采用左外联接查询*/
(SELECT cash.StatID, cash.CashID, dbo.T_CashDay.OpDt,
dbo.T_CashDay.InFare, dbo.T_CashDay.InCount,
dbo.T_CashDay.OutFare, dbo.T_CashDay.OutCount, cash.suminfare,
countinfare
FROM (SELECT Cashrec.StatID, Cashrec.CashID, Cashrec.dt,
SUM(Cashrec.InFare) AS SumInfare, COUNT(*)
AS CountInfare
FROM (SELECT StatID, CashID, RTRIM(CAST(YEAR(CashDt)
AS char)) + '-' + RTRIM(CAST(MONTH(CashDt) AS char))
+ '-' + RTRIM(CAST(DAY(CashDt) AS char)) AS dt /*存款日期*/, InFare,
OutFare, CustomerID
FROM dbo.T_CashRec
WHERE outfare = 0) Cashrec /*存款具体时间转换为日期的视图*/ INNER JOIN
dbo.T_Customers ON
Cashrec.CustomerID = dbo.T_Customers.CustomerID
WHERE (dbo.T_Customers.CardType = 1 /*临时卡类型*/ )
GROUP BY Cashrec.StatID, Cashrec.CashID, Cashrec.dt)
cash /*以StatID,CashID,dt作为分组字段，统计出临时卡的存款总额及存款次数的视图*/ INNER JOIN
dbo.T_CashDay ON cash.StatID = dbo.T_CashDay.StatID AND
cash.CashID = dbo.T_CashDay.CashID AND
cash.dt = dbo.T_CashDay.OpDt) ll /*cash视图与T_CashDay表进行联接查询的视图*/ ON
dbo.T_CashDay.OpDt = ll.OpDt AND dbo.T_CashDay.CashID = ll.CashID AND
ll.StatID = dbo.T_CashDay.StatID
WHERE (dbo.T_CashDay.OpDt BETWEEN '2004-7-1' AND '2004-7-1'/*临时卡存款的时间范围*/)) lll INNER JOIN
dbo.T_Cashiers ON lll.CashID = dbo.T_Cashiers.CashID AND
lll.StatID = dbo.T_Cashiers.StatID INNER JOIN
dbo.T_Station ON lll.StatID = dbo.T_Station.StatID
SELECT dbo.T_Station.StatName AS 工作站, dbo.T_Eatery.STName AS 食堂,
dbo.T_Group.GrpName AS 食堂经营组, dbo.T_Terms.Port AS 端口,
dbo.T_Terms.TermName AS 窗机名称, yf.SumDt AS 日期,
dbo.T_Meal.MealName AS 餐别, yf.IncomeFare AS 营业额,
yf.IncomeCount AS 营业次数, ISNULL(yf.SumOpfare, 0) / 2 AS 优惠额,
ISNULL(yf.CountOpfare, 0) AS 优惠次数, yf.MngFare AS 管理费,
yf.CorrectFare AS 纠错额, yf.IncomeFare - ISNULL(yf.SumOpfare, 0)
/ 2 + yf.MngFare - yf.CorrectFare AS 实际收入
FROM (SELECT dbo.T_IncomeRec.StatID, dbo.T_IncomeRec.MealID,
dbo.T_IncomeRec.Port, dbo.T_IncomeRec.Term, dbo.T_IncomeRec.SumDt,
dbo.T_IncomeRec.IncomeFare, dbo.T_IncomeRec.IncomeCount,
s_c_opf.SumOpfare, s_c_opf.CountOpfare, dbo.T_IncomeRec.MngFare,
dbo.T_IncomeRec.CorrectFare
FROM dbo.T_IncomeRec LEFT OUTER JOIN
(SELECT ConsumeRec.StatID, ConsumeRec.Port, ConsumeRec.Term,
ConsumeRec.MealID, dt, SUM(ConsumeRec.OpFare) AS SumOpfare,
COUNT(*) AS CountOpfare
FROM (SELECT StatID, Port, Term, CustomerID,
RTRIM(CAST(YEAR(dbo.T_ConsumeRec.OpDt) AS char))
+ '-' + RTRIM(CAST(MONTH(dbo.T_ConsumeRec.OpDt)
AS char)) + '-' + RTRIM(CAST(DAY(dbo.T_ConsumeRec.OpDt)
AS char)) AS dt, CollectDt, MealID, OpFare, MngFare,
OddFare
FROM dbo.T_ConsumeRec) AS consumerec INNER JOIN
dbo.T_Customers ON
ConsumeRec.CustomerID = dbo.T_Customers.CustomerID
WHERE (dbo.T_Customers.CardType = 1)
GROUP BY ConsumeRec.StatID, ConsumeRec.Port, ConsumeRec.Term,
ConsumeRec.MealID, ConsumeRec.dt) s_c_opf ON
s_c_opf.StatID = dbo.T_IncomeRec.StatID AND
s_c_opf.Port = dbo.T_IncomeRec.Port AND
s_c_opf.Term = dbo.T_IncomeRec.Term AND
s_c_opf.MealID = dbo.T_IncomeRec.MealID AND
dbo.T_IncomeRec.SumDt = s_c_opf.dt
WHERE (dbo.T_IncomeRec.SumDt BETWEEN '2004-7-6' AND '2004-7-6'))
yf INNER JOIN
dbo.T_Eatery ON yf.StatID = dbo.T_Eatery.StatID INNER JOIN
dbo.T_Group ON yf.StatID = dbo.T_Group.StatID AND
dbo.T_Eatery.STID = dbo.T_Group.STID INNER JOIN
dbo.T_Station ON yf.StatID = dbo.T_Station.StatID INNER JOIN
dbo.T_Terms ON dbo.T_Eatery.StatID = dbo.T_Terms.StatID AND
dbo.T_Group.STID = dbo.T_Terms.STID AND
dbo.T_Group.GrpID = dbo.T_Terms.GrpID AND yf.Port = dbo.T_Terms.Port AND
yf.Term = dbo.T_Terms.Term INNER JOIN
dbo.T_Meal ON yf.MealID = dbo.T_Meal.MealID
-------------------------------------------------------------------------------------
/*列出未注册卡SQL代码*/
select t_consumerec.customerid as 未注册客户号,t_consumerec.oddfare as 未注册卡余额,t_consumerec.opdt as 未注册卡消费时间 from t_consumerec where t_consumerec.customerid<>all(select customerid from t_customers)
and t_consumerec.opdt between '2004-9-12 00:00:00' and '2004-9-15 23:59:59'
-----------------------------------------------------------------------
/*门禁系统后台数据库IDCARD中T_customers表的触发器*/
create trigger delterms on t_customers for update as
declare @id int,@k1 int,@k2 int
select @k1=count(*) from t_customers where Inserted
select @k2=count(*) from t_customers where Deleted
if @k1=@k2 and @k1>0
begin
select @id=t_customers.customerid from t_customers,t_terms where t_customers.customerid=t_terms.customerid and
t_customers.cardno<>t_terms.cardno
delete from t_terms where customerid=@id
end
-----------------------------------------------------------------------
/*门禁系统的卡号纠正T_SQL代码*/
declare @id int, @no int
while 1=1
begin
select @id=t_customers.customerid,@no=t_customers.cardno from t_customers,t_terms where
t_customers.customerid=t_terms.customerid and t_customers.cardno<>t_terms.cardno
order by t_customers.customerid asc
if exists(select t_customers.customerid from t_customers,t_terms where t_customers.customerid=t_terms.customerid and t_customers.cardno<>t_terms.cardno )
begin
update t_terms set cardno=@no where customerid=@id
end
else
begin
print '更新完毕！'
break
end
end
-----------------------------------------------------------------------
/*清除冲突补助月份的T_SQL代码*/
declare @id int
declare @month datetime
while 1=1
begin
SELECT @id=T_SubsidyNotPutOut.CustomerID ,@month=dbo.T_SubsidyNotPutOut.[Month]
FROM dbo.T_SubsidyNotPutOut INNER JOIN
dbo.T_SubsidyPutOut ON
dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND
dbo.T_SubsidyNotPutOut.[Month] = dbo.T_SubsidyPutOut.[Month]
if (exists(select * FROM T_SubsidyNotPutOut
WHERE (CustomerID=@id and [Month] = @month)))
begin
DELETE FROM T_SubsidyNotPutOut
WHERE (CustomerID=@id and [Month] = @month)
continue
end
else
begin
print '没有相关客户的相关补助发放冲突月份！或补助冲突月份已经清理完毕！'
break
end
end
-----------------------------------------------------------------------
/*前期补助无法发放名单SQL代码*/
SELECT dbo.T_Customers.Name AS 姓名, dbo.T_SubsidyNotPutOut.[Month] AS 月份
FROM dbo.T_SubsidyNotPutOut INNER JOIN
dbo.T_SubsidyPutOut ON
dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND
dbo.T_SubsidyNotPutOut.[Month] = dbo.T_SubsidyPutOut.[Month] INNER JOIN
dbo.T_Customers ON
dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_Customers.CustomerID
--------------------------------------------------------------------------------
/*显示人员消费情况及联系SQL代码*/
SELECT dbo.T_Customers.Name AS 姓名, dbo.T_ConsumeRec.OpDt AS 消费时间,
dbo.T_ConsumeRec.OpFare AS 消费额, dbo.T_ConsumeRec.OddFare AS 余额,
ISNULL(dbo.T_Department.DpName1, '') + ISNULL(dbo.T_Department.DpName2, '')
+ ISNULL(dbo.T_Department.DpName3, '') AS 部门,
dbo.T_Station.StatName AS 工作站, dbo.T_Eatery.STName AS 食堂,
dbo.T_Group.GrpName AS 食堂组, dbo.T_Terms.Port AS 端口号,
dbo.T_Terms.TermName AS 窗机名称
FROM dbo.T_ConsumeRec INNER JOIN
dbo.T_Customers INNER JOIN
dbo.T_Department ON SUBSTRING(dbo.T_Customers.Account, 1, 2)
= dbo.T_Department.DpCode1 AND SUBSTRING(dbo.T_Customers.Account, 3, 2)
= dbo.T_Department.DpCode2 AND SUBSTRING(dbo.T_Customers.Account, 5, 3)
= dbo.T_Department.DpCode3 ON
dbo.T_ConsumeRec.CustomerID = dbo.T_Customers.CustomerID INNER JOIN
dbo.T_Eatery ON dbo.T_ConsumeRec.StatID = dbo.T_Eatery.StatID INNER JOIN
dbo.T_Group ON dbo.T_ConsumeRec.StatID = dbo.T_Group.StatID AND
dbo.T_Eatery.STID = dbo.T_Group.STID INNER JOIN
dbo.T_Station ON dbo.T_ConsumeRec.StatID = dbo.T_Station.StatID INNER JOIN
dbo.T_Terms ON dbo.T_Eatery.StatID = dbo.T_Terms.StatID AND
dbo.T_Eatery.STID = dbo.T_Terms.STID AND
dbo.T_Group.GrpID = dbo.T_Terms.GrpID AND
dbo.T_ConsumeRec.Port = dbo.T_Terms.Port AND
dbo.T_ConsumeRec.Term = dbo.T_Terms.Term
----------------------------------------------------------------------------------------
/*列出存取款及联系SQL代码*/
SELECT dbo.T_Customers.Name AS 姓名, ISNULL(dbo.T_Department.DpName1, '') + ISNULL(dbo.T_Department.DpName2, '')
+ ISNULL(dbo.T_Department.DpName3, '') AS 部门,
dbo.T_CashRec.CashDt AS 存取款时间, dbo.T_CashRec.InFare AS 存款额,
dbo.T_CashRec.OutFare AS 取款额, dbo.T_CashRec.OddFare AS 余额,
dbo.T_Station.StatName AS 工作站, dbo.T_Cashiers.Name AS 出纳员,
dbo.T_CashRec.Port AS 出纳机端口, dbo.T_CashRec.Term AS 出纳机机器号
FROM dbo.T_Station INNER JOIN
dbo.T_Cashiers ON dbo.T_Station.StatID = dbo.T_Cashiers.StatID INNER JOIN
dbo.T_Customers INNER JOIN
dbo.T_Department ON SUBSTRING(dbo.T_Customers.Account, 1, 2)
= dbo.T_Department.DpCode1 AND SUBSTRING(dbo.T_Customers.Account, 3, 2)
= dbo.T_Department.DpCode2 AND SUBSTRING(dbo.T_Customers.Account, 5, 3)
= dbo.T_Department.DpCode3 INNER JOIN
dbo.T_CashRec ON dbo.T_Customers.CustomerID = dbo.T_CashRec.CustomerID ON
dbo.T_Cashiers.CashID = dbo.T_CashRec.CashID AND
dbo.T_Station.StatID = dbo.T_CashRec.StatID
-----------------------------------------------------------------------------------------
/*列出incomerec帐本中与现有窗机比较后不存在的窗机*/
SELECT Port, Term
FROM T_IncomeRec
WHERE (Term <> ALL
(SELECT term
FROM t_terms
WHERE statid = 2)) AND (StatID = 2)
----------------------------------------------------------------------------------------
/*公安专发放补助前处理的SQL代码(当月补助形成后)*/
if exists(select customerid from t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0 group by customerid having count(*)>1)
begin /*判断5、6类卡是否存在一人两条前期记录即存在去年没领补助的教师*/
select month ,customerid as 去年没领补助的教师的客户号,cardtype,subsidy from t_subsidynotputout where customerid
=any(select customerid from t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0 group by customerid having count(*)>1)
end
else
begin
--go
insert into t_subsidymonthplan(month,customerid,cardtype,subsidy)
select * from t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0
/*公安专在当月补助形成后（5、6类卡为教师卡，一年只领一次补助），发补助前：向表monthplan插入符合在表notputout中5、6类卡subsidy为0的记录，
注意是否存在一人两条前期记录(一般不会出现这种况，除非去年没领补助)，否则在monthplan表中后面的更新时间会出错！！*/
--go
delete t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0
/*删除notputout被复制的记录*/
--go
update t_subsidymonthplan set month='2004-12-1' where (cardtype=5 or cardtype=6) and subsidy=0
/*更改表monthplan中的month日期，月份根据实际定*/
--go
update t_customers set subsidydt='2004-12-1' where (cardtype=5 or cardtype=6) and cursubsidyfare>0
/*更改t_customers表中的补助时间，注意与t_subsidymonthplan表中的month日期值保持一致！！*/
end
go
select sum(cursubsidyfare) as 客户帐本的补助总额 from t_customers
go
select sum(subsidy) as 前期补助总额 from t_subsidynotputout
go
select sum(subsidy) as 当月补助总额 from t_subsidymonthplan
/*查询客户帐本的补助总额是否等于前期补助总额与当月补助总额之和！*/
--------------------------------------------------------------------------------------------------
/*师大补助月报表信息纠正T_SQL代码*/
declare @k int, @k1 int,@k2 int,@month datetime,@subsidyFare money,@cardtype tinyint,@subsidyFare1 money,@subsidyFare2 money
/*清除冲突补助月份的T_SQL代码开始*/
declare @id int
while 1=1
begin
SELECT @id=T_SubsidyNotPutOut.CustomerID ,@month=dbo.T_SubsidyNotPutOut.[Month]
FROM dbo.T_SubsidyNotPutOut INNER JOIN
dbo.T_SubsidyPutOut ON
dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND
dbo.T_SubsidyNotPutOut.[Month] = dbo.T_SubsidyPutOut.[Month]
if (exists(select * FROM T_SubsidyNotPutOut
WHERE (CustomerID=@id and [Month] = @month)))
begin
DELETE FROM T_SubsidyNotPutOut
WHERE (CustomerID=@id and [Month] = @month)
end
else
begin
print '没有相关客户的相关补助发放冲突月份！或补助冲突月份已经清理完毕！'
break
end
end /**/
while 1=1
begin
SELECT @id=T_Subsidymonthplan.CustomerID ,@month=dbo.T_Subsidymonthplan.[Month]
FROM dbo.T_Subsidymonthplan INNER JOIN
dbo.T_SubsidyPutOut ON
dbo.T_Subsidymonthplan.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND
dbo.T_Subsidymonthplan.[Month] = dbo.T_SubsidyPutOut.[Month]
if (exists(select * FROM T_Subsidymonthplan
WHERE (CustomerID=@id and [Month] = @month)))
begin
DELETE FROM T_Subsidymonthplan
WHERE (CustomerID=@id and [Month] = @month)
end
else
begin
print '没有相关客户的本月补助冲突！或本月补助冲突已经清理完毕！'
break
end
end
/*清除冲突补助月份的T_SQL代码结束*/
set @month='2004-9-1' /*补助的月份,根据实际定*/
set @cardtype=4 /*卡的类别，根据实际定*/
select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidypre where cardtype=@cardtype and month=@month /*统计当月补助计划人数及金额，以t_subsidypre帐本为准*/
if exists(select * from t_subsidymonth where plancount=@k and plansubsidy=@subsidyFare and month=@monthand cardtype=@cardtype) /*判断当月补助计划数及金额是否正确*/
begin
select @k1=count(*),@subsidyFare1=sum(subsidy) from t_subsidymonthplan where cardtype=@cardtype and month=@month /*统计当月补助未发人数及金额*/
if @subsidyFare1 is null
begin
set @subsidyFare1=0
end
set @k2=@k-@k1
set @subsidyFare2=@subsidyFare-@subsidyFare1
update t_subsidymonth set putoutcount=@k2,putoutsubsidy=@subsidyFare2 where cardtype=@cardtype and month=@month /*当月已发人数及金额=当月补助计划人数及金额-当月补助未发人数及金额*/
print '更改当月的补助信息完成!'
end
else
begin
print '计划总数不一致!'
end
select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidynotputout where cardtype=@cardtype and month<@month /*统计前期补助未发人数及金额*/
select @k1=planprecount,@subsidyFare1=planpre from t_subsidymonth where cardtype=@cardtype and month=@month /*统计当月前期补助计划人数及金额*/
set @k2=@k1-@k
set @subsidyFare2=@subsidyFare1-@subsidyFare
if @subsidyFare2 is null
begin
set @subsidyFare2=0
end
update t_subsidymonth set putoutprecount=@k2,putoutpre=@subsidyFare2 where cardtype=@cardtype and month=@month /*当月前期已发人数及金额=当月前期补助计划人数及金额-前期补助未发人数及金额*/
print '更改当月的前期补助信息完成!'
-------------------------------------------------------------------------------------------------
/*清除管理费的触发器及生成t_mngfarelog表的脚本*/
create trigger tr_mngfarelog on t_incomerec for insert,update
as
if exists(select * from t_incomerec where mngfare>0)
begin
declare @statid tinyint,@mealid tinyint,@port tinyint,@term tinyint,
@sumdt datetime,@incomefare money,@mngfare money,@avginc money,
@incomecount int
select @statid=statid,@mealid=mealid,@port=port,@term=term,
@sumdt=sumdt, @incomefare=incomefare,@incomecount=incomecount,
@mngfare=mngfare from t_incomerec where mngfare>0
update t_incomerec set mngfare=0 where statid=@statid and mealid=@mealid and
port=@port and term=@term and sumdt=@sumdt
set @avginc=@incomefare/@incomecount
if @avginc>5/*平均消费值，根据实际定*/
begin
update t_incomerec set incomefare=4.5*incomecount where
statid=@statid and mealid=@mealid and port=@port and term=@term and sumdt=@sumdt
end
insert into t_mngfarelog values(@statid,@mealid,@port,@term,
@sumdt,@incomefare,@incomecount,@mngfare)
end
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_mngfarelog]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1) /*生成t_mngfarelog表的脚本*/
drop table [dbo].[t_mngfarelog]
GO
CREATE TABLE [dbo].[t_mngfarelog] (
[statid] [tinyint] NOT NULL ,
[mealid] [tinyint] NOT NULL ,
[port] [tinyint] NOT NULL ,
[term] [tinyint] NOT NULL ,
[sumdt] [datetime] NOT NULL ,
[incomefare] [money] NOT NULL ,
[incomecount] [int] NOT NULL ,
[mngfare] [money] NULL
) ON [PRIMARY]
GO
