Eulerian/EDW/UseCases/SanKeyMap/Sql.pm untested

filter: Statementstmt 0.0% 0 / 124 Branchbran 0.0% 0 / 32 Conditioncond 0.0% 0 / 12 MC/DCmcdc 0.0% 0 / 6 Subroutinesub 0.0% 0 / 14 Podpod 0.0% 0 / 10 totaltotal 0.0% 0 / 198 CC 17 SCAR 31.1
CC
17
Cov
0%
AddPageview
56.0
AddFilter
30.0
AddClickview
20.0
CRAP 22.5
1 package API::Eulerian::EDW::UseCases::SanKeyMap;
2  
3 use API::Eulerian::EDW::Sql::Builder;
4 use strict; use warnings;
5 use base 'Exporter';
6  
7 our @EXPORT = qw( Sql );
8  
9 sub AddTimerange
10 {
11 my ( $builder, $setup ) = @_;
12 $builder->timerange(
13 $setup->{ from }, $setup->{ to }
14 );
15 }
16  
17 sub ToArray
18 {
19 my ( $hash, $key ) = @_;
20 my $value = $hash->{ $key } || [];
21 $value = [ $value ] if ref( $value ) ne 'ARRAY';
22 return $value;
23 }
24  
25 sub AddPageview
26 {
27 my ( $builder, $setup ) = @_;
28 my $devices = ToArray( $setup, 'mdevicetypefull-name' );
29 my $subkeys = ToArray( $setup, 'subkey2-name' );
30 my $ndevices = scalar( @$devices );
31 my $nsubkeys = scalar( @$subkeys );
32 my $filter = '';
33  
34 # Handle pageview filtering on devices if any
35 if( $ndevices == 1 ) {
36 $filter .= 'pageview.device.devicetype.type == ';
37 $filter .= "'";
38 $filter .= $devices->[ 0 ];
39 $filter .= "'";
40 } elsif( $ndevices > 1 ) {
41 $filter .= 'IN( pageview.device.devicetype.type, ';
42 $filter .= join( ', ', map { '"' . $_ . '"' } @$devices );
43 $filter .= ' )';
44 }
45  
46 # Handle pageview filtering on subkey2 if any
47 if( $nsubkeys == 1 ) {
48 if( $ndevices ) { $filter .= " && "; }
49 $filter .= 'pageview.subkey2.name == ';
50 $filter .= "'";
51 $filter .= $subkeys->[ 0 ];
52 $filter .= "'";
53 } elsif( $nsubkeys > 1 ) {
54 if( $ndevices ) { $filter .= " && "; }
55 $filter .= 'IN( pageview.subkey2.name, ';
56 $filter .= join( ', ', map { '"' . $_ . '"' } @$subkeys );
57 $filter .= ' )';
58 }
59  
60 # Add pageview reader
61 $builder->readers(
62 'pageview', 'ea:pageview', $setup->{ site }, $filter
63 );
64  
65 }
66  
67 sub AddClickview
68 {
69 my ( $builder, $setup ) = @_;
70 my $medias = ToArray( $setup, 'media-shortname' );
71 my $nmedias = scalar( @$medias );
72 my $filter = '';
73  
74 # Handle clickview if need it */
75 if( $nmedias == 1 ) {
76 # Setup clickview filter
77 $filter .= "clickview.channel.odmedia == '";
78 $filter .= $medias->[ 0 ];
79 $filter .= "'";
80 } elsif( $nmedias > 1 ) {
81 $filter .= 'IN( clickview.channel.odmedia, ';
82 $filter .= join( ', ', map { '"' . $_ . '"' } @$medias );
83 $filter .= ' )';
84 }
85 # Add clickview reader
86 if( $filter ne '' ) {
87 $builder->readers(
88 'clickview', 'ea:clickview', $setup->{ site }, $filter
89 );
90 }
91  
92 }
93  
94 sub AddVisit
95 {
96 my ( $builder, $setup ) = @_;
97 my $filter;
98  
99 # Setup visit filter
100 $filter = 'visit.last.pageview.timestamp + MINS( ';
101 $filter .= int( $setup->{ session } || 30 );
102 $filter .= ' ) < pageview.timestamp';
103  
104 # Create visit
105 $builder->groups( 'visit', 'pageview', $filter );
106  
107 }
108  
109 sub AddJoin
110 {
111 my ( $builder, $setup ) = @_;
112 my $media = $setup->{ 'media-shortname' } || undef;
113 my $imedia = int( $setup->{ 'media-id' } || 0 );
114  
115 if( $imedia ) {
116 # Setup join filter
117 my $filter = '';
118 $filter .= "clickview.timestamp > visit.first.pageview.timestamp && ";
119 $filter .= "clickview.timestamp <= visit.last.pageview.timestamp && ";
120 $filter .= "join.count() < 1 ";
121 # Add join on clickview
122 $builder->joins( 'join', 'visit', 'clickview', $filter );
123 }
124  
125 return $imedia;
126 }
127  
128 sub AddOutputs
129 {
130 my ( $builder, $prefix, $setup ) = @_;
131 my $npages = int( $setup->{ npages } || 5 );
132  
133 # Add outputs
134 for my $ioutput ( 0 ... $npages - 1 ) {
135 my $output = $prefix;
136 $output .= "visit.items( $ioutput ).pageview.page.name";
137 $builder->outputs( $output );
138 }
139  
140 }
141  
142 sub AddFilter
143 {
144 my ( $builder, $prefix, $setup ) = @_;
145 my $roots = ToArray( $setup, 'roots' );
146  
147 # Add outputs filter
148 if( scalar( @$roots ) > 0 ) {
149 my $filter = '';
150  
151 if( $prefix ne '' ) {
152 $filter .= $prefix;
153 $filter .= "visit.first.pageview.timestamp != NULL && ";
154 }
155 if( scalar( @$roots ) == 1 ) {
156 $filter .= $prefix;
157 $filter .= "visit.items( 0 ).pageview.page.name == '";
158 $filter .= $roots->[ 0 ];
159 $filter .= "'";
160 } elsif( scalar( @$roots ) > 1 ) {
161 $filter .= "IN( ";
162 $filter .= $prefix;
163 $filter .= "visit.items( 0 ).pageview.page.name, ";
164 $filter .= join( ', ', map { '"' . $_ . '"' } @$roots );
165 $filter .= " )";
166 }
167 $builder->filter( $filter );
168  
169 }
170  
171 }
172  
173 sub AddThen
174 {
175 my ( $builder, $setup ) = @_;
176 my $palmares = int( $setup->{ npalmares } || 5 );
177 $builder->thens( "SanKeyMap( $palmares )" );
178 }
179  
180 sub Sql
181 {
182 my $builder = new API::Eulerian::EDW::Sql::Builder();
183 my $master = 'visit';
184 my ( $setup ) = @_;
185 my $prefix = '';
186  
187 $builder->mode( 'ROW' );
188 $builder->master( 'visit' );
189 AddTimerange( $builder, $setup );
190 AddPageview( $builder, $setup );
191 AddClickview( $builder, $setup );
192 AddVisit( $builder, $setup );
193 if( AddJoin( $builder, $setup ) ) {
194 $builder->master( 'join' );
195 $prefix = 'join.';
196 }
197 AddOutputs( $builder, $prefix, $setup );
198 AddFilter( $builder, $prefix, $setup );
199 AddThen( $builder, $setup );
200  
201 return $builder->str();
202 }