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

filter: Statementstmt 0.0% 0 / 113 Branchbran 0.0% 0 / 26 Conditioncond 0.0% 0 / 12 MC/DCmcdc 0.0% 0 / 6 Subroutinesub 0.0% 0 / 15 Podpod 0.0% 0 / 11 totaltotal 0.0% 0 / 183 CC 14 SCAR 29.6
CC
14
Cov
0%
AddPageview
56.0
AddClickview
20.0
ToArray
6.0
CRAP 19.2
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 }