2 /*********************************************************************************
3 * SugarCRM Community Edition is a customer relationship management program developed by
4 * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
6 * This program is free software; you can redistribute it and/or modify it under
7 * the terms of the GNU Affero General Public License version 3 as published by the
8 * Free Software Foundation with the addition of the following permission added
9 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
10 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
11 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
13 * This program is distributed in the hope that it will be useful, but WITHOUT
14 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
18 * You should have received a copy of the GNU Affero General Public License along with
19 * this program; if not, see http://www.gnu.org/licenses or write to the Free
20 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
24 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
26 * The interactive user interfaces in modified source and object code versions
27 * of this program must display Appropriate Legal Notices, as required under
28 * Section 5 of the GNU Affero General Public License version 3.
30 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
31 * these Appropriate Legal Notices must retain the display of the "Powered by
32 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
33 * technical reasons, the Appropriate Legal Notices must display the words
34 * "Powered by SugarCRM".
35 ********************************************************************************/
38 require_once 'modules/DynamicFields/templates/Fields/TemplateInt.php';
39 require_once 'modules/DynamicFields/templates/Fields/TemplateDate.php';
40 require_once 'include/SearchForm/SearchForm2.php';
41 require_once 'modules/Opportunities/Opportunity.php';
43 class RangeSearchTest extends Sugar_PHPUnit_Framework_TestCase
45 private $hasExistingCustomSearchFields = false;
50 public function setUp()
52 if(file_exists('custom/modules/Opportunities/metadata/SearchFields.php'))
54 $this->hasExistingCustomSearchFields = true;
55 copy('custom/modules/Opportunities/metadata/SearchFields.php', 'custom/modules/Opportunities/metadata/SearchFields.php.bak');
56 unlink('custom/modules/Opportunities/metadata/SearchFields.php');
57 } else if(!file_exists('custom/modules/Opportunities/metadata')) {
58 mkdir_recursive('custom/modules/Opportunities/metadata');
61 //Setup Opportunities module and date_closed field
62 $_REQUEST['view_module'] = 'Opportunities';
63 $_REQUEST['name'] = 'date_closed';
64 $templateDate = new TemplateDate();
65 $templateDate->enable_range_search = true;
66 $templateDate->populateFromPost();
67 include('custom/modules/Opportunities/metadata/SearchFields.php');
70 $seed = new Opportunity();
71 $module = 'Opportunities';
72 $this->searchForm = new SearchForm($seed, $module);
73 $this->searchForm->searchFields = array(
74 'range_date_closed' => array
76 'query_type' => 'default',
77 'enable_range_search' => 1,
79 'value' => '[this_year]',
80 'operator' => 'this_year',
82 'start_range_date_closed' => array
84 'query_type' => 'default',
85 'enable_range_search' => 1,
88 'end_range_date_closed' => array
90 'query_type' => 'default',
91 'enable_range_search' => 1,
94 'range_amount' => array
96 'query_type' => 'default',
97 'enable_range_search' => true
99 'start_range_amount' => array
101 'query_type' => 'default',
102 'enable_range_search' => true
104 'end_range_amount' => array (
105 'query_type' => 'default',
106 'enable_range_search' => true
110 $this->originalDbType = $GLOBALS['db']->dbType;
113 public function tearDown()
115 $GLOBALS['db']->dbType = $this->originalDbType;
117 if(!$this->hasExistingCustomSearchFields)
119 unlink('custom/modules/Opportunities/metadata/SearchFields.php');
122 if(file_exists('custom/modules/Opportunities/metadata/SearchFields.php.bak')) {
123 copy('custom/modules/Opportunities/metadata/SearchFields.php.bak', 'custom/modules/Opportunities/metadata/SearchFields.php');
124 unlink('custom/modules/Opportunities/metadata/SearchFields.php.bak');
127 if(file_exists($this->smartyTestFile))
129 unlink($this->smartyTestFile);
134 public function testRangeSearchMysql()
136 $GLOBALS['db']->dbType = 'mysql';
137 $where_clauses = $this->searchForm->generateSearchWhere();
138 $this->assertEquals($where_clauses[0], 'LEFT(opportunities.date_closed,4) = EXTRACT(YEAR FROM ( current_date ))');
140 $this->searchForm->searchFields['range_date_closed'] = array (
141 'query_type' => 'default',
142 'enable_range_search' => 1,
143 'is_date_field' => 1,
144 'value' => '[next_year]',
145 'operator' => 'next_year',
148 $where_clauses = $this->searchForm->generateSearchWhere();
149 $this->assertEquals($where_clauses[0], 'LEFT(opportunities.date_closed,4) = EXTRACT(YEAR FROM ( current_date + interval \'1\' year))');
152 public function testRangeNumberSearches()
154 $GLOBALS['db']->dbType = 'mysql';
155 unset($this->searchForm->searchFields['range_date_closed']);
156 $this->searchForm->searchFields['range_amount'] = array (
157 'query_type' => 'default',
158 'enable_range_search' => 1,
160 'operator' => 'greater_than',
163 $where_clauses = $this->searchForm->generateSearchWhere();
164 $this->assertEquals($where_clauses[0], "opportunities.amount > '10000'");
166 $this->searchForm->searchFields['range_amount'] = array (
167 'query_type' => 'default',
168 'enable_range_search' => 1,
170 'operator' => 'less_than',
173 $where_clauses = $this->searchForm->generateSearchWhere();
174 $this->assertEquals($where_clauses[0], "opportunities.amount < '10000'");
176 $this->searchForm->searchFields['range_amount'] = array (
177 'query_type' => 'default',
178 'enable_range_search' => 1,
180 'operator' => 'greater_than_equals',
183 $where_clauses = $this->searchForm->generateSearchWhere();
184 $this->assertEquals($where_clauses[0], "opportunities.amount >= '10000'");
186 $this->searchForm->searchFields['range_amount'] = array (
187 'query_type' => 'default',
188 'enable_range_search' => 1,
190 'operator' => 'less_than_equals',
193 $where_clauses = $this->searchForm->generateSearchWhere();
194 $this->assertEquals($where_clauses[0], "opportunities.amount <= '10000'");
196 $this->searchForm->searchFields['range_amount'] = array (
197 'query_type' => 'default',
198 'enable_range_search' => 1,
200 'operator' => 'not_equal',
203 $where_clauses = $this->searchForm->generateSearchWhere();
204 $this->assertEquals($where_clauses[0], "opportunities.amount != '10000'");
206 $this->searchForm->searchFields['range_amount'] = array (
207 'query_type' => 'default',
208 'enable_range_search' => 1,
213 $where_clauses = $this->searchForm->generateSearchWhere();
214 $this->assertEquals($where_clauses[0], "opportunities.amount >= '9999.99' AND opportunities.amount <= '10000.01'");
218 public function testRangeSearchMssql()
220 $GLOBALS['db']->dbType = 'mssql';
221 $where_clauses = $this->searchForm->generateSearchWhere();
222 $this->assertEquals($where_clauses[0], 'DATEPART(yy,opportunities.date_closed) = DATEPART(yy, GETDATE())');
224 $this->searchForm->searchFields['range_date_closed'] = array (
225 'query_type' => 'default',
226 'enable_range_search' => 1,
227 'is_date_field' => 1,
228 'value' => '[next_year]',
229 'operator' => 'next_year',
232 $where_clauses = $this->searchForm->generateSearchWhere();
233 $this->assertEquals($where_clauses[0], 'DATEPART(yy,opportunities.date_closed) = DATEPART(yy,( dateadd(yy, 1,GETDATE())))');
238 * testRangeSearchWithSavedReportValues
239 * This test attempts to simulate testing what would happen should a saved report be invoked against
240 * a range search field
243 public function testRangeSearchWithSavedReportValues()
245 require_once('include/SugarFields/Fields/Datetime/SugarFieldDatetime.php');
246 $parentFieldArray = 'fields';
249 $vardef['name'] = 'date_closed_advanced';
250 $vardef['vname'] = 'LBL_DATE_CLOSED';
252 $opportunity = new Opportunity();
253 $vardef = $opportunity->field_defs['date_closed'];
254 $vardef['name'] = 'date_closed_advanced';
255 $vardef['options'] = array
258 'not_equal' => 'Not On',
259 'greater_than' => ' After',
260 'less_than' => ' Before',
261 'last_7_days' => ' Last 7 Days',
262 'next_7_days' => ' Next 7 Days',
263 'last_30_days' => ' Last 30 Days',
264 'next_30_days' => ' Next 30 Days',
265 'last_month' => ' Last Month',
266 'this_month' => ' This Month',
267 'next_month' => ' Next Month',
268 'last_year' => ' Last Year',
269 'this_year' => ' This Year',
270 'next_year' => ' Next Year',
271 'between' => ' Is Between',
275 $displayParams = array('labelSpan'=>'', 'fieldSpan'=>'');
278 $sugarFieldDatetime = new SugarFieldDatetime('Datetime');
280 $_REQUEST['action'] = 'SearchForm';
281 $html = $sugarFieldDatetime->getSearchViewSmarty($parentFieldArray, $vardef, $displayParams, $tabindex);
283 //Write this widget's contents to a file
284 $this->smartyTestFile = 'tests/include/SearchForm/RangeSearchTest.tpl';
285 $handle = sugar_fopen($this->smartyTestFile, 'wb');
286 fwrite($handle, $html);
288 //Check that the template exists before we proceed with further tests
289 $this->assertTrue(file_exists($this->smartyTestFile));
291 //Stuff Smarty variables
292 $vardef['value'] = '';
294 $fields['date_closed_advanced'] = $vardef;
296 //Create Smarty instance
297 require_once('include/Sugar_Smarty.php');
298 $ss = new Sugar_Smarty();
300 //Assign Smarty variables
301 $ss->assign('fields', $fields);
302 $ss->assign('APP', $GLOBALS['app_strings']);
303 $ss->assign('CALENDAR_FORMAT', 'm-d-Y');
305 //Simulate the request with saved report value
306 $_REQUEST['date_closed_advanced'] = '07-03-2009';
308 $output = $ss->fetch($this->smartyTestFile);
309 $this->assertRegExp("/range_date_closed_advanced\"\s+?value\s*?\=s*?\'07\-03\-2009\'/", $output);
311 //Simulate the request with range search value
312 $_REQUEST['range_date_closed_advanced'] = '07-04-2009';
314 $output = $ss->fetch($this->smartyTestFile);
315 $this->assertRegExp("/range_date_closed_advanced\"\s+?value\s*?\=s*?\'07\-04\-2009\'/", $output);