1
CREATE PROCEDURE HVC_Statistics_Sta
2
(
3
@week int
4
)
5![](/Images/OutliningIndicators/None.gif)
6
AS
7
set nocount on
8![](/Images/OutliningIndicators/None.gif)
9
if not exists(select * from HVC_Statistics_PortalRank where week= @week)
10
begin
11
insert into HVC_Statistics_PortalRank(PortalID,TotalAccessed,CreatedDate,Week)
12
select PortalID,HitCount,getdate(),@week
13
from HVC_Counter_Counter
14
end
15
if exists(select * from HVC_Statistics_PortalRank where week= @week)
16
begin
17
update HVC_Statistics_PortalRank set
18
AllArticlesNum= (SELECT count(*) FROM HVC_News_Article B
19
WHERE HVC_Statistics_PortalRank.PortalID =B.PortalID AND B.Passed = 1),
20
AllArticleHits= (SELECT isnull(sum(C.Hits),0) FROM HVC_News_Article C
21
WHERE HVC_Statistics_PortalRank.PortalID =C.PortalID AND C.Passed = 1),
22
WeekArticleUpdated= (SELECT count(*) FROM HVC_News_Article F
23
WHERE HVC_Statistics_PortalRank.PortalID =F.PortalID AND F.Passed = 1 AND DateDiff(w, F.CreatedDate,GetDate())=0),
24
WeekArticleHits=(SELECT isnull(sum(G.Hits),0) FROM HVC_News_Article G
25
WHERE HVC_Statistics_PortalRank.PortalID =G.PortalID AND G.Passed = 1 AND DateDiff(w, G.CreatedDate,GetDate())=0),
26![](/Images/OutliningIndicators/None.gif)
27
MonthTotalUpdated= (SELECT count(*) FROM HVC_News_Article D
28
WHERE HVC_Statistics_PortalRank.PortalID =D.PortalID AND D.Passed = 1 AND DateDiff(m, D.CreatedDate,GetDate())=0),
29
MonthArticleHits=(SELECT isnull(sum(E.Hits),0) FROM HVC_News_Article E
30
WHERE HVC_Statistics_PortalRank.PortalID =E.PortalID AND E.Passed = 1 AND DateDiff(m, E.CreatedDate,GetDate())=0)
31
32
end
33
RETURN
34![](/Images/OutliningIndicators/None.gif)
35![](/Images/OutliningIndicators/None.gif)
36
GO
37![](/Images/OutliningIndicators/None.gif)
38
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}HVC_Statistics_Cal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
39
drop procedure {databaseOwner}{objectQualifier}HVC_Statistics_Cal
40
GO
41![](/Images/OutliningIndicators/None.gif)
42
CREATE PROCEDURE HVC_Statistics_Cal
43
(
44
@week int
45
)
46![](/Images/OutliningIndicators/None.gif)
47
AS
48![](/Images/OutliningIndicators/None.gif)
49
if exists(select * from HVC_Statistics_PortalRank where week= @week)
50
begin
51
update HVC_Statistics_PortalRank set
52
ResultValue=
53
((HVC_Statistics_PortalRank.TotalAccessed*(SELECT FloatValue FROM HVC_Statistics_Setting WHERE AttrID =1))
54
+( AllArticlesNum*(SELECT FloatValue FROM HVC_Statistics_Setting WHERE AttrID =2))
55
+( AllArticleHits*(SELECT FloatValue FROM HVC_Statistics_Setting WHERE AttrID =3))
56
+( MonthTotalUpdated*(SELECT FloatValue FROM HVC_Statistics_Setting WHERE AttrID =4))
57
+( MonthArticleHits*(SELECT FloatValue FROM HVC_Statistics_Setting WHERE AttrID =5))
58
+( WeekArticleUpdated*(SELECT FloatValue FROM HVC_Statistics_Setting WHERE AttrID =6))
59
+( WeekArticleHits*(SELECT FloatValue FROM HVC_Statistics_Setting WHERE AttrID =7))
60
+( Score*(SELECT FloatValue FROM HVC_Statistics_Setting WHERE AttrID =8)))
61![](/Images/OutliningIndicators/None.gif)
62
end
63![](/Images/OutliningIndicators/None.gif)
64
RETURN
65![](/Images/OutliningIndicators/None.gif)
66![](/Images/OutliningIndicators/None.gif)
67
GO
68![](/Images/OutliningIndicators/None.gif)
69![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/None.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/None.gif)
64
![](/Images/OutliningIndicators/None.gif)
65
![](/Images/OutliningIndicators/None.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/None.gif)
68
![](/Images/OutliningIndicators/None.gif)
69
![](/Images/OutliningIndicators/None.gif)