用友UAP报表net控件开发总结

目录

1

写存储过程，并在

UFData

库执行

................................................................................................

2

2

编写订单查询报表控件代码

.........................................................................................................

4

3

编写订单明细查询报表控件代码

5

6

新建订单明细查询报表

7

订单查询报表联查订单明细查询报表

8

代码下载

........................................................................................................................................

DROP PROCEDURE [dbo].[LJYC_OrderReport_Pro]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create proc [dbo].[LJYC_OrderReport_Pro]

@cStartTime varchar(20) = '1900-01-01', --开始日期

@cStopTime varchar(20) = '1900-01-01',--结束日期

@cCusCode varchar(2000) = '-1',--客户

declare @dStopTime datetime

declare @cPStartTime varchar(255)

declare @cPStopTime varchar(255)

set @cCusCode = replace(@cCusCode,',',''',''')

set @cCusCode = replace(@cCusCode,char(9),''',''')

set @cCusCode = replace(@cCusCode,';',''',''')

set @iwhere = ' 1=1'

set @cPStartTime = @cStartTime

set @cPStopTime = @cStopTime

set @dStartTime = cast(@cStartTime as datetime)

if @cCusCode!='-1' and @cCusCode!=''

begin

set @iwhere = @iwhere + ' and isnull(A.cCusCode,'''') in ('''+@cCusCode+''')'

end

if @cPStartTime!='1900-01-01'

set @iwhere = @iwhere+' and dDate>='''+@cStartTime+''''

if @cPStopTime!='1900-01-01'

set @iwhere = @iwhere+' and dDate<='''+@cStopTime+''''

--业务员数据权限控制

if not exists(SELECT * FROM UFSystem..UA_User A INNER JOIN UFSystem..UA_Role B ON A.cUser_Id

and cBusObId=N''person'' And isUserGroup=0 ) or A.cPersonCode in (SELECT cUser_Id FROM

UserHrPersonContro WHERE cUser_Id = '''+@cUserId+'''))'

end

set @esql = ' SELECT A.cSOCode AS 订单号,CONVERT(VARCHAR(100),A.dDate,23) AS 订单日

期,A.cCusCode AS 客户编码,A.cCusName AS 客户名称,A.cPersonCode AS 业务员编码,P.cPsn_Name

AS 业务员 FROM dbo.SO_SOMain A INNER JOIN dbo.Customer CT ON A.cCusCode = CT.cCusCode LEFT

JOIN dbo.hr_hi_person P ON A.cPersonCode = P.cPsn_Num where ' + @iwhere

EXEC(@esql)

end

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create proc [dbo].[LJYC_OrderDetailReport_Pro]

@cOrderNo varchar(40) = '-1'--订单号

as

begin

declare @esql varchar(8000)

declare @iwhere varchar(2000) --总过滤条件

set @iwhere = ' 1=1'

begin

set @iwhere = @iwhere + ' and A.cSOCode = '''+@cOrderNo+''''

end

set @esql = 'SELECT A.cSOCode AS 单据号,CONVERT(VARCHAR(100),A.dDate,23) AS 订单日

存货名称,CONVERT(DECIMAL(18,2),B.iQuantity) AS 数量 FROM dbo.SO_SOMain A INNER JOIN

dbo.SO_SODetails B ON A.ID = B.ID LEFT JOIN dbo.Customer CT ON A.cCusCode = CT.cCusCode LEFT

#region sql

clsLogin login = (e.Login as clsLogin);

e.DataSource.Type = CustomDataSourceTypeEnum.SQL;//数据源类型就是 SQL 类型

SqlHelper.connU8 = login.UFDataConnstringForNet;

string cStartTime = "";

string cStopTime = "";

string cUserId = "";

string cCusCode = "";

#region 过滤条件

if (e.FltSrv.GetValue1("cCusCode") != null)

}

if (e.FltSrv.GetValue2("dDate") != null)

{

cStopTime = e.FltSrv.GetValue2("dDate");

}

cUserId = e.UserID;

#endregion