1 <?php
2
3 /**
4 * AfMssqlDbHelper class file.
5 *
6 * @author Keith Burton <kburton@kappasoft.net>
7 * @package advancedfilters.dbhelpers
8 */
9
10 /**
11 * A generic database helper for SQL Server. If you are using an SQL Server
12 * version of 2012 or later, you should instead use the more robust
13 * AfMssql2012DbHelper class, by overriding the $driverMap property in the
14 * configuration of the advanced filters extension.
15 */
16 class AfMssqlDbHelper extends AfBaseDbHelper
17 {
18 /**
19 * Provides conversion to decimal for SQL Server versions earlier than 2012.
20 *
21 * SQL Server stops processing records as soon as it hits an invalid cast.
22 * To support older versions of SQL Server, this implementation checks the
23 * value length and uses ISNUMERIC rather than the more reliable TRY_CAST
24 * function. If you're using SQL Server 2012 or later, you should use the
25 * AfMssql2012DbHelper class instead.
26 *
27 * @param string $dbExpression the expression to update.
28 * @param integer $numDigits the maximum number of digits that the decimal
29 * number should contain.
30 * @param integer $decimalPlaces the number of decimal places that the
31 * resulting decimal should have.
32 * @param integer $nonNumericResultValue the integer value to use if an
33 * expression isn't recognised as a number. This can also be null.
34 * @return string the updated expression.
35 */
36 public function convertExpressionToDecimal($dbExpression, $numDigits,
37 $decimalPlaces, $nonNumericResultValue)
38 {
39 $numDigits = (int)$numDigits;
40 $decimalPlaces = (int)$decimalPlaces;
41 $nonNumericResultValue = $nonNumericResultValue === null
42 ? 'NULL' : (int)$nonNumericResultValue;
43
44 return "CASE WHEN LEN($dbExpression) <= $numDigits "
45 . "AND ISNUMERIC(CONCAT(($dbExpression), 'e0')) = 1 "
46 . "THEN CAST(($dbExpression) "
47 . "AS DECIMAL($numDigits, $decimalPlaces)) "
48 . "ELSE $nonNumericResultValue END";
49 }
50
51 /**
52 * Checks regular expression syntax using the very limited PATINDEX
53 * function. Documentation for this function's syntax can be found on MSDN.
54 *
55 * It makes use of wildcards, so, assuming the extension is configured to
56 * use the default regex prefix and suffix, patterns might look like this:
57 *
58 * /%[0-9]%/ The value contains a number.
59 * /[0-9]%/ The value starts with a number.
60 * /%[0-9]/ The value ends with a number.
61 *
62 * @param CDbConnection $dbConnection the database connection object.
63 * @param string $regex the pattern to test.
64 * @return boolean true if the syntax is valid, false if not.
65 */
66 public function checkRegex($dbConnection, $regex)
67 {
68 $query = "SELECT PATINDEX(:regex, '1')";
69
70 try
71 {
72 $dbConnection->createCommand($query)
73 ->query(array(':regex'=>$regex));
74 }
75 catch (Exception $ex)
76 {
77 return false;
78 }
79
80 return true;
81 }
82
83 /**
84 * Adds a regex condition using the very limited PATINDEX function.
85 * Documentation for this function's syntax can be found on MSDN.
86 *
87 * It makes use of wildcards, so, assuming the extension is configured to
88 * use the default regex prefix and suffix, patterns might look like this:
89 *
90 * /%[0-9]%/ The value contains a number.
91 * /[0-9]%/ The value starts with a number.
92 * /%[0-9]/ The value ends with a number.
93 *
94 * @param CDbCriteria $criteria the criteria to update.
95 * @param string $columnExpression the column to search, or a valid
96 * expression.
97 * @param string $regex the pattern to match against.
98 * @param boolean $invertLogic whether the logic should be inverted to
99 * return the opposite query results.
100 */
101 public function addRegexCondition($criteria, $columnExpression,
102 $regex, $invertLogic)
103 {
104 $paramName = ':regex' . md5($regex . $columnExpression);
105 $operator = $invertLogic ? '=' : '>';
106 $criteria->addCondition(
107 "PATINDEX($paramName, ($columnExpression)) $operator 0");
108 $criteria->params[$paramName] = $regex;
109 }
110 }
111