/*三明電力臨時卡處理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 @[email protected] 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 [email protected] 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 [email protected] where [email protected] 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 ([email protected] and [month] = @month))) begin delete from t_subsidynotputout where ([email protected] 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 ([email protected] and [month] = @month))) begin delete from t_subsidynotputout where ([email protected] 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 ([email protected] and [month] = @month))) begin delete from t_subsidymonthplan where ([email protected] 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 [email protected] and [email protected] /*統計當月補助計劃人數及金額，以t_subsidypre帳本為準*/if exists(select * from t_subsidymonth where [email protected] and [email protected] and [email protected] and [email protected]) /*判斷當月補助計劃數及金額是否正確*/ begin select @k1=count(*),@subsidyfare1=sum(subsidy) from t_subsidymonthplan where [email protected] and [email protected] /*統計當月補助未發人數及金額*/ if @subsidyfare1 is null begin set @subsidyfare1=0 end set @[email protected]@k1 set @[email protected]@subsidyfare1 update t_subsidymonth set [email protected],[email protected] where [email protected] and [email protected] /*當月已發人數及金額=當月補助計劃人數及金額-當月補助未發人數及金額*/ print '更改當月的補助信息完成!' endelse begin print '計劃總數不一致!' end
select @k=count(*),@subsidyfare=sum(subsidy) from t_subsidynotputout where [email protected] and month<@month /*統計前期補助未發人數及金額*/select @k1=planprecount,@subsidyfare1=planpre from t_subsidymonth where [email protected] and [email protected] /*統計當月前期補助計劃人數及金額*/set @[email protected]@kset @[email protected]@subsidyfareif @subsidyfare2 is null begin set @subsidyfare2=0 endupdate t_subsidymonth set [email protected],[email protected] where [email protected] and [email protected] /*當月前期已發人數及金額=當月前期補助計劃人數及金額-前期補助未發人數及金額*/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 [email protected] and [email protected] and [email protected] and [email protected] and [email protected] set @[email protected]/@incomecount if @avginc>5/*平均消費值，根據實際定*/ begin update t_incomerec set incomefare=4.5*incomecount where [email protected] and [email protected] and [email protected] and [email protected] and [email protected] 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
