| 1 | ############################################################################### | ||
| 2 | # | ||
| 3 | # @brief RollMap EDW sql builder implementation. | ||
| 4 | # | ||
| 5 | # This module is used to produce a D3 SanKey Map datas. Those datas allow to | ||
| 6 | # draw a palmares of pages read before a given page. | ||
| 7 | # | ||
| 8 | # @file API/Eulerian/EDW/UseCases/UnRollMap.pm | ||
| 9 | # | ||
| 10 | # @date 2026/02/03 | ||
| 11 | # | ||
| 12 | ############################################################################### | ||
| 13 | # | ||
| 14 | # Enforce compilor rules. | ||
| 15 | # | ||
| 16 | use strict; use warnings; | ||
| 17 | # | ||
| 18 | # Define package name. | ||
| 19 | # | ||
| 20 | package API::Eulerian::EDW::UseCases::UnRollMap; | ||
| 21 | # | ||
| 22 | # Use Sql::Builder. | ||
| 23 | # | ||
| 24 | use API::Eulerian::EDW::Sql::Builder; | ||
| 25 | # | ||
| 26 | # Import Exporter. | ||
| 27 | # | ||
| 28 | use base 'Exporter'; | ||
| 29 | # | ||
| 30 | # Define exported interface | ||
| 31 | # | ||
| 32 | our @EXPORT = qw( Sql ); | ||
| 33 | # | ||
| 34 | # @brief Add Timerange. | ||
| 35 | # | ||
| 36 | # @param $builder - Sql builder. | ||
| 37 | # @param $setup - Request setup. | ||
| 38 | # | ||
| 39 | sub AddTimerange | ||
| 40 | { | ||
| 41 | my ( $builder, $setup ) = @_; | ||
| 42 | $builder->timerange( $setup->{ from }, $setup->{ to } ); | ||
| 43 | } | ||
| 44 | # | ||
| 45 | # @brief Get an array from a hash map entry. | ||
| 46 | # | ||
| 47 | # @param $hash - Hash map. | ||
| 48 | # @param $key - Key. | ||
| 49 | # | ||
| 50 | # @return Array. | ||
| 51 | # | ||
| 52 | sub ToArray | ||
| 53 | { | ||
| 54 | my ( $hash, $key ) = @_; | ||
| 55 | my $value = $hash->{ $key } || []; | ||
| 56 | $value = [ $value ] if ref( $value ) ne 'ARRAY'; | ||
| 57 | return $value; | ||
| 58 | } | ||
| 59 | # | ||
| 60 | # @brief Add Pageview reader. | ||
| 61 | # | ||
| 62 | # @param $builder - Sql builder. | ||
| 63 | # @param $setup - Request setup. | ||
| 64 | # | ||
| 65 | sub AddPageview | ||
| 66 | { | ||
| 67 | my ( $builder, $setup ) = @_; | ||
| 68 | my $devices = ToArray( $setup, 'mdevicetypefull-name' ); | ||
| 69 | my $subkeys = ToArray( $setup, 'subkey2-name' ); | ||
| 70 | my $ndevices = scalar( @$devices ); | ||
| 71 | my $nsubkeys = scalar( @$subkeys ); | ||
| 72 | my $filter = ''; | ||
| 73 | |||
| 74 | # Handle pageview filtering on devices if any | ||
| 75 | if( $ndevices == 1 ) { | ||
| 76 | $filter .= 'pageview.device.devicetype.type == '; | ||
| 77 | $filter .= "'"; | ||
| 78 | $filter .= $devices->[ 0 ]; | ||
| 79 | $filter .= "'"; | ||
| 80 | } elsif( $ndevices > 1 ) { | ||
| 81 | $filter .= 'IN( pageview.device.devicetype.type, '; | ||
| 82 | $filter .= join( ', ', map { '"' . $_ . '"' } @$devices ); | ||
| 83 | $filter .= ' )'; | ||
| 84 | } | ||
| 85 | |||
| 86 | # Handle pageview filtering on subkey2 if any | ||
| 87 | if( $nsubkeys == 1 ) { | ||
| 88 | if( $ndevices ) { $filter .= " && "; } | ||
| 89 | $filter .= 'pageview.subkey2.name == '; | ||
| 90 | $filter .= "'"; | ||
| 91 | $filter .= $subkeys->[ 0 ]; | ||
| 92 | $filter .= "'"; | ||
| 93 | } elsif( $nsubkeys > 1 ) { | ||
| 94 | if( $ndevices ) { $filter .= " && "; } | ||
| 95 | $filter .= 'IN( pageview.subkey2.name, '; | ||
| 96 | $filter .= join( ', ', map { '"' . $_ . '"' } @$subkeys ); | ||
| 97 | $filter .= ' )'; | ||
| 98 | } | ||
| 99 | |||
| 100 | # Add pageview reader | ||
| 101 | $builder->readers( | ||
| 102 | 'pageview', 'ea:pageview', $setup->{ site }, $filter | ||
| 103 | ); | ||
| 104 | |||
| 105 | } | ||
| 106 | # | ||
| 107 | # @brief Add Clickview reader. | ||
| 108 | # | ||
| 109 | # @param $builder - Sql builder. | ||
| 110 | # @param $setup - Request setup. | ||
| 111 | # | ||
| 112 | sub AddClickview | ||
| 113 | { | ||
| 114 | my ( $builder, $setup ) = @_; | ||
| 115 | my $medias = ToArray( $setup, 'media-shortname' ); | ||
| 116 | my $imedia = int( $setup->{ 'media-id' } || 0 ); | ||
| 117 | my $nmedias = scalar( @$medias ); | ||
| 118 | my $filter = ''; | ||
| 119 | |||
| 120 | # Handle clickview if need it */ | ||
| 121 | if( $nmedias == 1 ) { | ||
| 122 | # Setup clickview filter | ||
| 123 | $filter .= "clickview.channel.odmedia == '"; | ||
| 124 | $filter .= $medias->[ 0 ]; | ||
| 125 | $filter .= "'"; | ||
| 126 | } elsif( $nmedias > 1 ) { | ||
| 127 | $filter .= 'IN( clickview.channel.odmedia, '; | ||
| 128 | $filter .= join( ', ', map { '"' . $_ . '"' } @$medias ); | ||
| 129 | $filter .= ' )'; | ||
| 130 | } | ||
| 131 | if( $filter ne '' ) { | ||
| 132 | # Add clickview reader | ||
| 133 | $builder->readers( | ||
| 134 | 'clickview', 'ea:clickview', $setup->{ site }, $filter | ||
| 135 | ); | ||
| 136 | } | ||
| 137 | |||
| 138 | } | ||
| 139 | # | ||
| 140 | # @brief Add group visit. | ||
| 141 | # | ||
| 142 | # @param $builder - Sql builder. | ||
| 143 | # @param $setup - Request setup. | ||
| 144 | # | ||
| 145 | sub AddVisit | ||
| 146 | { | ||
| 147 | my ( $builder, $setup ) = @_; | ||
| 148 | my $filter; | ||
| 149 | |||
| 150 | # Setup visit filter | ||
| 151 | $filter = 'pageview.rtvisit == 1'; | ||
| 152 | |||
| 153 | # Create visit | ||
| 154 | $builder->groups( 'visit', 'pageview', $filter ); | ||
| 155 | |||
| 156 | } | ||
| 157 | # | ||
| 158 | # @brief Join visit and clickview. | ||
| 159 | # | ||
| 160 | # @param $builder - Sql builder. | ||
| 161 | # @param $setup - Request setup. | ||
| 162 | # | ||
| 163 | sub AddJoin | ||
| 164 | { | ||
| 165 | my ( $builder, $setup ) = @_; | ||
| 166 | my $imedia = int( $setup->{ 'media-id' } || 0 ); | ||
| 167 | |||
| 168 | if( $imedia ) { | ||
| 169 | # Setup join filter | ||
| 170 | my $filter = ''; | ||
| 171 | $filter .= 'clickview.timestamp >= visit.first.pageview.timestamp && '; | ||
| 172 | $filter .= 'clickview.timestamp <= visit.last.pageview.timestamp && '; | ||
| 173 | $filter .= 'join.count() < 1 '; | ||
| 174 | # Add join on clickview | ||
| 175 | $builder->joins( 'join', 'visit', 'clickview', $filter ); | ||
| 176 | } | ||
| 177 | |||
| 178 | return $imedia; | ||
| 179 | } | ||
| 180 | # | ||
| 181 | # @brief Add aliases. | ||
| 182 | # | ||
| 183 | # @param $builder - Sql builder. | ||
| 184 | # @param $setup - Request setup. | ||
| 185 | # | ||
| 186 | sub AddAliases | ||
| 187 | { | ||
| 188 | my ( $builder, $prefix, $setup ) = @_; | ||
| 189 | my $npages = int( $setup->{ npages } || 5 ); | ||
| 190 | my $target = "'" . $setup->{ target } . "'"; | ||
| 191 | # Target Alias | ||
| 192 | $builder->aliases( "Target", $target ); | ||
| 193 | # PageId Alias | ||
| 194 | $builder->aliases( | ||
| 195 | "PageId", | ||
| 196 | "${prefix}visit.find_last( ${prefix}visit.current.pageview.page.name == Target )" | ||
| 197 | ); | ||
| 198 | } | ||
| 199 | # | ||
| 200 | # @brief Add outputs. | ||
| 201 | # | ||
| 202 | # @param $builder - Sql builder. | ||
| 203 | # @param $setup - Request setup. | ||
| 204 | # | ||
| 205 | sub AddOutputs | ||
| 206 | { | ||
| 207 | my ( $builder, $prefix, $setup ) = @_; | ||
| 208 | my $npages = int( $setup->{ npages } || 5 ); | ||
| 209 | |||
| 210 | # Add outputs | ||
| 211 | for my $ioutput ( 0 ... $npages - 1 ) { | ||
| 212 | $builder->outputs( | ||
| 213 | "${prefix}visit.items( PageId - $ioutput ).pageview.page.name" | ||
| 214 | ); | ||
| 215 | } | ||
| 216 | |||
| 217 | } | ||
| 218 | # | ||
| 219 | # @brief Add outputs filter. | ||
| 220 | # | ||
| 221 | # @param $builder - Sql builder. | ||
| 222 | # @param $setup - Request setup. | ||
| 223 | # | ||
| 224 | sub AddFilter | ||
| 225 | { | ||
| 226 | my ( $builder, $prefix, $setup ) = @_; | ||
| 227 | my $filter = 'PageId != NULL'; | ||
| 228 | |||
| 229 | if( $prefix ne '' ) { | ||
| 230 | $filter .= ' && '; | ||
| 231 | $filter .= "${prefix}clickview.timestamp != NULL"; | ||
| 232 | } | ||
| 233 | $builder->filter( $filter ); | ||
| 234 | |||
| 235 | } | ||
| 236 | # | ||
| 237 | # @brief Add Post processing command. | ||
| 238 | # | ||
| 239 | # @param $builder - Sql builder. | ||
| 240 | # @param $setup - Request setup. | ||
| 241 | # | ||
| 242 | sub AddThen | ||
| 243 | { | ||
| 244 | my ( $builder, $setup ) = @_; | ||
| 245 | my $palmares = int( $setup->{ npalmares } || 5 ); | ||
| 246 | $builder->thens( "UnRollMap( $palmares )" ); | ||
| 247 | } | ||
| 248 | # | ||
| 249 | # @brief Forge SQL request used to produce DS3 Sankey Map | ||
| 250 | # Graph. | ||
| 251 | # | ||
| 252 | # @param $setup - Request setup. | ||
| 253 | # | ||
| 254 | # @return Sql request. | ||
| 255 | # | ||
| 256 | sub Sql | ||
| 257 | { | ||
| 258 | my $builder = new API::Eulerian::EDW::Sql::Builder(); | ||
| 259 | my $master = 'visit'; | ||
| 260 | my ( $setup ) = @_; | ||
| 261 | my $prefix = ''; | ||
| 262 | |||
| 263 | $builder->mode( 'ROW' ); | ||
| 264 | $builder->master( 'visit' ); | ||
| 265 | AddTimerange( $builder, $setup ); | ||
| 266 | AddPageview( $builder, $setup ); | ||
| 267 | AddVisit( $builder, $setup ); | ||
| 268 | AddClickview( $builder, $setup ); | ||
| 269 | if( AddJoin( $builder, $setup ) ) { | ||
| 270 | $builder->master( 'join' ); | ||
| 271 | $prefix = 'join.'; | ||
| 272 | } | ||
| 273 | AddAliases( $builder, $prefix, $setup ); | ||
| 274 | AddOutputs( $builder, $prefix, $setup ); | ||
| 275 | AddFilter( $builder, $prefix, $setup ); | ||
| 276 | AddThen( $builder, $setup ); | ||
| 277 | |||
| 278 | return $builder->str(); | ||
| 279 | } |