ErpReport 自动发送邮件所使用SQL

作者:zilong 日期:2008-12-02

字体大小: 小中大

--****************************
--***开发人：杨闽　2008.11.30
--***功能：ErpReport自动发送信件给各部门经理
--***************************

--使用数据库
use aspnetdb
go
--1:建立日志表
IF EXISTS(Select * from SYSOBJECTS Where NAME='zlog')
    drop table zlog  --若存在REPORTCONFIG表，则先删除
GO
create table zlog
(
id int identity(1,1) primary key,
type int,--１失败，０成功
eventName varchar(100),--事件名
msg varchar(200),--具体信息
dt datetime,--日志时间
)

insert into zlog(type,eventName,msg,dt) values(1,'事件名称','事件信息',getdate())
go
select * from zlog
go

--2：创建配置表
IF EXISTS(Select * from SYSOBJECTS Where NAME='REPORTCONFIG')
    drop table REPORTCONFIG  --若存在REPORTCONFIG表，则先删除
GO
create table REPORTCONFIG
(
       Autocode int identity(1,1) primary key,          --自动增长ID
       REPORTNAME varchar(20),             --报表名
       PARAMATERS varchar(20),--参数中文
       PARAM_VALUE varchar(20),--参数
       ztype int default 0,--是否汇总
       DISPLAY_ORDER int,--显示顺序
)
--测试ZL0119报表
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0119','报表名称','ZL0119',1,0)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0119','日期','INQUIRY_DATE',2,3)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0119','汇总字段','STOCK_Q ',3,1)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0119','分类字段','MSEHL',4,2)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0119','汇总字段','stock_m',5,1)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0119','分类字段','WAERS',6,2)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0119','部门','ekorg',7,4)
--insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
--  values('ZL0119','排序字段','matnr',8,5)--其实都应该是分类字段
--insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
--  values('ZL0119','分类字段','matnr',9,2)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0119','分类字段','eknam',10,2)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0119','分类字段','ekotx',11,2)

-- Update ZL0119 SET MSEHL =  ZL0119.MEINS

--测试ZL0088报表
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0088','报表名称','ZL0088',1,0)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0088','日期','SQL_DATE',2,3)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0088','汇总字段','ZMENG ',3,1)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0088','分类字段','MEINS',4,2)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0088','汇总字段','KZWI5',5,1)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0088','分类字段','WAERC',6,2)
insert into REPORTCONFIG(REPORTNAME,PARAMATERS,PARAM_VALUE,DISPLAY_ORDER,ztype)
  values('ZL0088','部门','VKBUR',7,4)

go

select autocode,reportname,paramaters,param_value,display_order
from REPORTCONFIG where reportname = 'zl0119'
order by ztype
go

select convert(varchar(10),getdate()+1,120)

select id,report,email,username,department,organization from subscribe
GO

select * from subscribe   where id = 1
go
select splitcolumn  from [dbo].[z_split](
'ZL0079,ZL0119' ,','
)

--select splitcolumn  from [z_split](@str,',')

drop procedure ZREAD_MAILliST_one
go

--调用一个经理所看内容的存储过程
create procedure ZREAD_MAILliST_one
@userName varchar(20)
as

--1：取得报表名称和部门名称
declare @str varchar(2000)
set @str = ''
declare @ekorgs varchar(200)
set @ekorgs = ''

select @str = report,@ekorgs = department from subscribe    where username = @userName
print @str
print @ekorgs
--2：取得

----------
declare   @report_name varchar(20)
set @report_name = ''
declare zread_report cursor for
select splitcolumn  from [z_split](@str,',')

open zread_report

fetch next from zread_report
into @report_name
WHILE @@FETCH_STATUS = 0
BEGIN

--调用子存储过程
exec zpro_sql_sum  @report_name, @ekorgs

fetch next from zread_report
into @report_name
end
--print @str_all

CLOSE zread_report
DEALLOCATE zread_report

--执行发某一个用户EMAIL的存储过程
exec ZREAD_MAILliST_one '张三'

--exec (@str_all)

--调用子存储过程
--exec zpro_sql_sum  'ZL0119', '1010,1000'

--字段名，表名，数据库名之类作为变量时，必须用动态SQL
--eg:
declare @fname varchar(20)
set @fname = '[name]'
--Select @fname from sysobjects -- 错误
Exec('select ' + @fname + ' from sysobjects') -- 请注意加号前后的单引号的边上要加空格
--exec sp_executesql N' select ' + @fname + ' from sysobjects'
--当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = '[name]'
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from sysobjects'
Exec(@s) -- 成功
--exec sp_executesql @s -- 此句会报错

declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from sysobjects'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确，

----------------------------------------------------------------------------
declare @s varchar(2000)
set @s = 'ok ha ha!'
print @s

Select * FROM reportconfig where reportname = 'ZL0119'

------------------
drop function z_cou1
------------------------------------------------------------
create function z_cou1(@str_rptName varchar(20))
RETURNS  nvarchar(4000)
as
begin

--------------
declare @ps varchar(20),@pv varchar(20),@d int,@str_all varchar(2000),@str_group_lin varchar(100),@str_date varchar(40)
set @str_all = ''
set @str_group_lin = ''
--set @str_date = getdate() -1
declare zconfig_cur cursor for
select PARAMATERS,PARAM_VALUE,ztype from
reportconfig where reportname = 'ZL0119' AND PARAMATERS <> '报表名称'
open zconfig_cur

fetch next from zconfig_cur
into @ps,@pv,@d
WHILE @@FETCH_STATUS = 0
BEGIN
--print @pv
--print 'ok-begin'
if @d = 3
set @str_date = 'convert(varchar(10),getdate()-1,120) '

if @d = 2
--set @str_all = @str_all + @pv + ','
set @str_group_lin  = @str_group_lin  + @pv + ','
--print @str_all

if @d = 1
set @str_all = @str_all + 'sum('+ @pv + '),'
--print @str_all

fetch next from zconfig_cur
into @ps,@pv,@d
end
--print @str_all

CLOSE zconfig_cur
DEALLOCATE zconfig_cur

set @str_all = left(@str_all,(len(@str_all)-1))
set @str_group_lin= left(@str_group_lin,(len(@str_group_lin)-1))
set @str_all = 'select ' + @str_all + ' from zl0119 where convert(varchar(10),sql_date,120) = '+ @str_date +'  group by '  + @str_group_lin
print @str_all

exec (@str_all)

--------
select sum(STOCK_Q ),sum(stock_m) from zl0119 where convert(varchar(10),sql_date,120) = convert(varchar(10),getdate()-2,120)   group by MEINS,WAERS

return  @str_all

end

select dbo.z_cou1('zl0119')

select * from reportconfig

select * from zl0119 where convert(varchar(10),sql_date,120)   = convert(varchar(10),getdate()-1,120)

----
drop procedure zpro_sql_sum
GO
--存储过程版本
create procedure zpro_sql_sum
@reportName varchar(20),
@ekorg varchar(300)
as

--------------
declare @ps varchar(20),@pv varchar(20),@d int,@str_all varchar(2000),@str_group_lin varchar(200),@str_date varchar(40)
declare @par varchar(20),@str_orders varchar(200),@str_ekorg varchar(10)
set @str_all = ''
set @str_group_lin = ''
set @str_orders = ''
set @str_ekorg = ''
--set @str_date = getdate() -1
declare zconfig_cur cursor for
select PARAMATERS,PARAM_VALUE,ztype,PARAMATERS from
reportconfig where reportname = @reportName AND PARAMATERS <> '报表名称' orDER BY DISPLAY_ORDER
--reportconfig where reportname = 'ZL0119' AND PARAMATERS <> '报表名称'

open zconfig_cur

fetch next from zconfig_cur
into @ps,@pv,@d,@par
WHILE @@FETCH_STATUS = 0
BEGIN

--if @d = 3
if @par = '日期'
set @str_date = 'convert(varchar(10),getdate()-1,120) '   --需要按实际情况进行修改　为前一天
--if @d = 2

if @par = '分类字段' or     @par ='部门' or @par ='部组'
begin
set @str_all = @str_all + @pv + ','
set @str_group_lin  = @str_group_lin  + @pv + ','
end
--print @str_all

--if @d = 1
if @par = '汇总字段'
set @str_all = @str_all + 'sum('+ @pv + ') as '+ @pv +','
--print @str_all

if @par = '排序字段'
begin
set @str_orders = @str_orders + @pv + ','
--print @str_orders
end

if @par = '部门'
begin
  set @str_ekorg = @pv
end

fetch next from zconfig_cur
into @ps,@pv,@d,@par
end
--print @str_all

CLOSE zconfig_cur
DEALLOCATE zconfig_cur

if len(@str_all) > 0
begin

set @str_all = left(@str_all,(len(@str_all)-1))
set @str_group_lin= left(@str_group_lin,(len(@str_group_lin)-1))
--set @str_orders= left(@str_orders,(len(@str_orders)-1))
--set @str_all = 'select ' + @str_all + ' from zl0119 where convert(varchar(10),sql_date,120) = '+ @str_date +'  group by '  + @str_group_lin  + ' order by ' + @str_group_lin  --order by 字段需要出现在GROUP BY 字段中

set @str_all = 'select ' + @str_all + ' from  ' + @reportName + ' where '+ @str_ekorg +' IN ( ' + @ekorg + ') and  convert(varchar(10),sql_date,120) = '+ @str_date +'  group by '  + @str_group_lin + ' order by ' + @str_group_lin
print @str_all

exec (@str_all)
end
go

---执行存储过程输入报表名，部门名称
exec zpro_sql_sum  'ZL0119', '1010'
exec zpro_sql_sum  'ZL0088', '1000,1010'

exec zpro_sql_sum  'ZL0080', '1010'
exec zpro_sql_sum  'ZL0102', '1000,1010'

--select sum(STOCK_Q ) as STOCK_Q ,MEINS,sum(stock_m) as stock_m,WAERS from zl0119 where convert(varchar(10),sql_date,120) = convert(varchar(10),getdate()-2,120)   group by MEINS,WAERS

--select sum(STOCK_Q ) as STOCK_Q ,MEINS,sum(stock_m) as stock_m,WAERS,ekotx,eknam from  ZL0119 where ekorg = 1010and  convert(varchar(10),sql_date,120) = convert(varchar(10),getdate()-2,120)   group by MEINS,WAERS,ekotx,eknam order by MEINS,WAERS,ekotx,eknam
select *
from zl0088

update zl0088 set waerc = 'USD' where vgbel >=100000661
update zl0088 set sql_date = '2008-11-25',vkbur = '1000' where vgbel  <100000661

select * from zl0119 where sql_date =
--更新测试数据
update zl0119 set sql_date = getdate() -1
go
update zl0088 set  sql_date = getdate() -1
go
-----------------------

--在MSSQL中未发现split拆分函数
--MSSQL数据库 split函数
--@str 字符串 @splitstr 分隔符号如","
Create function [dbo].[z_split](@str varchar(8000),@splitstr varchar(10))
    returns @temp table([splitcolumn] varchar(20))
as
begin
    declare @i int
    set @str=rtrim(ltrim(@str))
    set @i=charindex(@splitstr,@str)
    while @i>=1
    begin
        insert @temp values(left(@str,@i-1))
        set @str=substring(@str,@i+1,len(@str)-@i)
        set @i=charindex(@splitstr,@str)
    end
    if @str<>''
    insert @temp values(@str)
    return
end

--调用：
select splitcolumn  from [dbo].[z_split]('11,33,455',',')