fixing widget where-clause generators
authormiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Wed, 4 Jan 2006 20:42:27 +0000 (20:42 +0000)
committermiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Wed, 4 Jan 2006 20:42:27 +0000 (20:42 +0000)
git-svn-id: svn://svn.open-ils.org/ILS/trunk@2608 dcc99617-32d9-48b4-a31d-7c20da2025e4

Open-ILS/src/reporter/widgets.example.xml

index c17df24..92f4ed0 100644 (file)
                                on which to report.
                        </description>
                        <filter-code type="perl">
-                               $full_col = "DATE_TRUNC('month', $full_col)";
+                               $full_col = "CAST(DATE_TRUNC('month', $full_col) AS DATE)";
                                $where_clause = 
-                                       "( $full_col = DATE_TRUNC('month', NOW() - CAST( ? || ' month' AS INTERVAL )) ".
-                                       "  OR $full_col = DATE_TRUNC('month', NOW() - CAST( ? || ' month' AS INTERVAL )) )";
+                                       "$full_col IN ( CAST(DATE_TRUNC('month', NOW() - CAST( ? || ' month' AS INTERVAL )) AS DATE), ".
+                                       "  CAST(DATE_TRUNC('month', NOW() - CAST( ? || ' month' AS INTERVAL )) AS DATE))";
                                push @$bind_list, $$val{target}, $$val{target} + $$val{compare};
                        </filter-code>
                </widget>
                        </description>
                        <filter-code type="perl">
                                $where_clause = 
-                                       "DATE_TRUNC('day', $full_col) = ".
-                                               "DATE_TRUNC('day', NOW()) - CAST(? || ' day' AS INTERVAL);
+                                       "CAST($full_col AS DATE) = ".
+                                               "CAST((NOW() - CAST(? || ' day' AS INTERVAL)) AS DATE)";
                                push @$bind_list, $val;
                        </filter-code>
                </widget>
                        </description>
                        <filter-code type="perl">
                                $where_clause =
-                                       "( DATE_TRUNC('week', $full_col) = ".
-                                       "  DATE_TRUNC('week', NOW() - CAST(? || ' weeks' AS INTERVAL) ) ";
+                                       "( CAST(DATE_TRUNC('week', $full_col) AS DATE) = ".
+                                       "  CAST(DATE_TRUNC('week', NOW() - CAST(? || ' weeks' AS INTERVAL) ) AS DATE) ";
                                push @$bind_list, $val;
                        </filter-code>
                </widget>
                        </description>
                        <filter-code type="perl">
                                $where_clause = 
-                                       "DATE_TRUNC('month', $full_col) = ".
-                                               "DATE_TRUNC('month', NOW()) - CAST(? || ' months' AS INTERVAL);
+                                       "CAST(DATE_TRUNC('month', $full_col) AS DATE) = ".
+                                               "CAST(DATE_TRUNC('month', NOW()) - CAST(? || ' months' AS INTERVAL) AS DATE)";
                                push @$bind_list, $val;
                        </filter-code>
                </widget>
                        </description>
                        <filter-code type="perl">
                                $where_clause = 
-                                       "DATE_TRUNC('year', $full_col) = ".
-                                               "DATE_TRUNC('year', NOW()) - CAST(? || ' years' AS INTERVAL);
+                                       "CAST(DATE_TRUNC('year', $full_col) AS DATE)= ".
+                                               "CAST(DATE_TRUNC('year', NOW()) - CAST(? || ' years' AS INTERVAL) AS DATE)";
                                push @$bind_list, $val;
                        </filter-code>
                </widget>