原创
/*三明电力临时卡处理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 intselect @k1=count(*) from t_customers where Insertedselect @k2=count(*) from t_customers where Deletedif @k1=@k2 and @k1>0begin 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, TermFROM T_IncomeRecWHERE (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) endelse 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=@month and 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 '更改当月的补助信息完成!' endelse 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-@kset @subsidyFare2=@subsidyFare1-@subsidyFareif @subsidyFare2 is null begin set @subsidyFare2=0 endupdate 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]') andOBJECTPROPERTY(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