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