NET统计分析报表解决方案.docx

1、NET统计分析报表解决方案.NET统计分析报表解决方案1-用户订购统计分析案例 前段时间接到很多报表的工作，现拿出一些小例子与大家分享。用户需求：运营商管理员：1. 以合作伙伴为维度对用户订购进行统计分析： a. 可对某一个合作伙伴进行分析，查看该合作伙伴下产品的用户订购在某个时间段内的发展趋势，需实现折线图、柱状图。 b. 可对所有合作伙伴进行分析，对比在某段时间内所有合作伙伴下产品的用户订购发展量，查看在某段时间内所有合作伙伴下产品的用户订购发展量在订购总量中所占比例，需实现柱状图、饼状图。2. 以产品为维度对用户订购进行统计分析： a. 可对某一个产品进行分析，查看该产品的用户订购在某个

2、时间段内的发展趋势，需实现折线图、柱状图。 b. 可对所有产品进行分析，对比在某段时间内所有产品的用户订购发展量，查看在某段时间内所有产品的用户订购发展量在订购总量中所占比例，需实现柱状图、饼状图。合作伙伴管理员：1. 以产品为维度对用户订购进行统计分析： a. 可对本公司下某一个产品进行分析，查看该产品的用户订购在某个时间段内的发展趋势，需实现折线图、柱状图。 b. 可对本公司下所有产品进行分析，对比在某段时间内所有产品的用户订购发展量，查看在某段时间内所有产品的用户订购发展量在订购总量中所占比例，需实现柱状图、饼状图。时间需支持按年、按月统计。根据用户需求，我们可以开始进行实现。实现步骤：

3、这里是用一个例子实现用户需求，没有用到系统内的表，系统内的表有很多例子中不需要的字段，所以根据系统内表结构新建。1. 数据库数据表设计：合作伙伴表（CPInfo）产品信息表（ProductInfo）：用户订购表（UserOrderInfo）：因为是移动的系统，所以用户订购是通过手机。2. 统计分析表建立：由于现实系统数据量比较大，统计的数据也不是实时的，所以统计分析不直接在原始数据表上进行，这样我们就需要建立一个专门用来存放统计分析数据的表。用户订购统计分析表（UserOrderStat）：a. StatType字段为报表类型，这里定义为：CP-按CP公司统计数据，Product-按产品统计数

4、据b. TimeType字段为时间类型，这里定义为：Year-按年统计数据，Month-按月统计数据3. 统计分析存储过程建立：按年统计存储过程：IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Ndbo.SP_UserOrderStatByYear) AND type in (NP, NPC)DROP PROCEDURE dbo.SP_UserOrderStatByYearGO-按年统计用户订购CREATE PROCEDURE SP_UserOrderStatByYearBeginYear INT, -开始年份E

5、ndYear INT -结束年份ASBEGIN DECLARE Temp_BeginYear INT DECLARE Temp_EndYear INT DECLARE BeginTime NVARCHAR(16) DECLARE EndTime NVARCHAR(16) SET Temp_BeginYear = BeginYear SET Temp_EndYear = EndYear + 1 -判断结束年份是否为当前时间年份或大于当前时间年份 IF EndYear = YEAR(GETDATE() SET Temp_EndYear = YEAR(GETDATE() SET BeginTime

6、= CAST(Temp_BeginYear AS NVARCHAR(8) + -1-1 SET EndTime = CAST(Temp_EndYear AS NVARCHAR(8) + -1-1 -添加年统计数据 INSERT INTO UserOrderStat ( StatType, TimeType, Year, CPCode, CPChName, ProductCode, ProductName, OrderCount ) -按CP公司统计 SELECT CP, Year, YEAR(uoi.OrderTime), uoi.CPCode, MAX(c.CPChName), , , CO

7、UNT(0) FROM UserOrderInfo uoi JOIN CPInfo c ON uoi.CPCode = c.CPCode JOIN ProductInfo p ON uoi.ProductCode = p.ProductCode WHERE uoi.Status = 1 AND uoi.OrderTime BeginTime AND uoi.OrderTime BeginTime AND uoi.OrderTime YEAR(GETDATE() OR (EndYear = YEAR(GETDATE() AND EndMonth MONTH(GETDATE() BEGIN SET

8、 Temp_EndYear = YEAR(GETDATE() SET Temp_EndMonth = MONTH(GETDATE() END SET BeginTime = CAST(Temp_BeginYear AS NVARCHAR(8) + - + CAST(BeginMonth AS NVARCHAR(8) + -1 SET EndTime = CAST(Temp_EndYear AS NVARCHAR(8) + - + CAST(EndMonth AS NVARCHAR(8) + -1 -添加月统计数据 INSERT INTO UserOrderStat ( StatType, Ti

9、meType, Year, Month, CPCode, CPChName, ProductCode, ProductName, OrderCount ) -按CP公司统计 SELECT CP, Month, YEAR(uoi.OrderTime), MONTH(uoi.OrderTime), uoi.CPCode, MAX(c.CPChName), , , COUNT(0) FROM UserOrderInfo uoi JOIN CPInfo c ON uoi.CPCode = c.CPCode JOIN ProductInfo p ON uoi.ProductCode = p.Produc

10、tCode WHERE uoi.Status = 1 AND uoi.OrderTime BeginTime AND uoi.OrderTime BeginTime AND uoi.OrderTime EndTime AND NOT EXISTS (SELECT 0 FROM UserOrderStat uos WHERE uos.CPCode = uoi.CPCode AND uos.ProductCode = uoi.ProductCode AND uos.Year = YEAR(uoi.OrderTime) AND uos.Month = MONTH(uoi.OrderTime) GRO

11、UP BY uoi.ProductCode, YEAR(uoi.OrderTime), MONTH(uoi.OrderTime)ENDGO4. 新建数据库作业，用来定时执行年统计存储过程和月统计存储过程，一个用来执行月统计存储过程，计划为每月的1日零点执行。至此数据准备工作完成，现在开始着手设计报表。在例子中用三层架构的方式实现，分为UI层、业务逻辑层、数据访问层。5. 新建用户统计报表工程解决方案统计分析数据查询存储过程：-统计查询存储过程CREATE PROCEDURE dbo.SP_UserOrderStatQueryStatType NVARCHAR(32),TimeType NVAR

12、CHAR(32),BeginYear INT,EndYear INT,BeginMonth INT,EndMonth INT,CPCode NVARCHAR(32),CPName NVARCHAR(256),ProductCode NVARCHAR(32),ProductName NVARCHAR(256)ASBEGIN -查询字段 DECLARE Field NVARCHAR(512) -查询条件 DECLARE Where NVARCHAR(512) -按范围查询时间字段 DECLARE Time NVARCHAR(256) -分组 DECLARE Group NVARCHAR(256)

13、-排序 DECLARE Order NVARCHAR(256) -设置查询字段 SET Field = NMAX(SeqNo) AS SeqNo,MAX(StatType) AS StatType,MAX(TimeType) AS TimeType, MAX(Year) AS Year,MAX(Month) AS Month,MAX(CPCode) AS CPCode,MAX(CPChName) AS CPChName, MAX(ProductCode) AS ProductCode,MAX(ProductName) AS ProductName,MAX(OrderCount) AS Orde

14、rCount SET Time = -如果时间条件都未选择，则查询总时间段 IF BeginYear = -1 AND EndYear = -1 AND BeginMonth = -1 AND EndMonth = -1 SET Time = ,总时间段 AS Time -如果年为时间条件，并且开始年与结束年相等 IF BeginYear -1 AND EndYear -1 AND BeginYear = EndYear AND BeginMonth = -1 AND EndMonth = -1 SET Time = , + CAST(BeginYear AS NVARCHAR(8) + 年

15、AS Time -如果年为时间条件，并且开始年与结束年不相等 IF BeginYear -1 AND EndYear -1 AND BeginYear EndYear AND BeginMonth = -1 AND EndMonth = -1 SET Time = , + CAST(BeginYear AS NVARCHAR(8) + 年 - + CAST(EndYear AS NVARCHAR(8) + 年 AS Time -如果年和月为时间条件，并且开始年与结束年相等，开始月与结束月相等 IF BeginYear -1 AND EndYear -1 AND BeginYear = EndY

16、ear AND BeginMonth -1 AND EndMonth -1 AND BeginMonth = EndMonth SET Time = , + CAST(BeginYear AS NVARCHAR(8) + 年 + CAST(BeginMonth AS NVARCHAR(8) + 月 AS Time -如果年和月为时间条件，并且开始年与结束年相等或者开始年与结束年不相等，开始月与结束月不相等 IF BeginYear -1 AND EndYear -1 AND BeginMonth -1 AND EndMonth -1 AND (BeginYear = EndYear AND B

17、eginMonth EndMonth) OR (BeginYear EndYear AND BeginMonth EndMonth) SET Time = , + CAST(BeginYear AS NVARCHAR(8) + 年 + CAST(BeginMonth AS NVARCHAR(8) + 月 - + CAST(EndYear AS NVARCHAR(8) + 年 + CAST(EndMonth AS NVARCHAR(8) + 月 AS Time SET Field = Field + Time SET Where = WHERE 1=1 -如果时间类型不为按时间范围查询 IF T

18、imeType Area SET Where = Where + AND TimeType = + TimeType + -如果StatType不为空 IF StatType AND StatType IS NOT NULL SET Where = Where + AND StatType = + StatType + -如果CPCode不为空 IF CPCode AND CPCode IS NOT NULL SET Where = Where + AND CPCode = + CPCode + -如果CPName不为空 IF CPName AND CPName IS NOT NULL SET

19、 Where = Where + AND CPName LIKE % + CPName + % -如果ProductCode不为空 IF ProductCode AND ProductCode IS NOT NULL SET Where = Where + AND ProductCode = + ProductCode + -如果ProductName不为空 IF ProductName AND ProductName IS NOT NULL SET Where = Where + AND ProductName = % + ProductName + % IF BeginYear -1 AN

20、D EndYear -1 SET Where = Where + AND (Year = + CAST(BeginYear AS NVARCHAR(8) + AND YEAR = + CAST(EndYear AS NVARCHAR(8) + ) IF BeginMonth -1 AND EndMonth -1 SET Where = Where + OR (Month = + CAST(BeginMonth AS NVARCHAR(8) + AND Month = + CAST(EndMonth AS NVARCHAR(8) + ) IF BeginYear -1 AND EndYear -

21、1 SET Where = Where + ) SET Group = GROUP BY IF StatType = CP SET Group = Group + CPCode IF StatType = Product SET Group = Group + ProductCode IF TimeType = Year SET Group = Group + ,Year IF TimeType = Month SET Group = Group + ,Month PRINT SELECT + Field + FROM UserOrderStat + Where + Group EXECUTE

22、 (SELECT + Field + FROM UserOrderStat + Where + Group)ENDGO数据访问类：UserOrderStatDAL.cs在这里用到了企业库进行数据库操作using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.Common;using Microsoft.Practices.EnterpriseLibrary.Data;namespace DAL public partial class UserOrderStatDAL / / 获取用户订购统计分析数据 / / 统计类型：CP-按CP统计，Product-按产品统计 / 时间类型：Year-按年统计，Month-按月统计，Area-按时间范围统计 / param name=be