| 1 | <?php |
| 2 | /* |
| 3 | +--------------------------------------------------------------------+ |
| 4 | | CiviCRM version 4.7 | |
| 5 | +--------------------------------------------------------------------+ |
| 6 | | Copyright CiviCRM LLC (c) 2004-2015 | |
| 7 | +--------------------------------------------------------------------+ |
| 8 | | This file is a part of CiviCRM. | |
| 9 | | | |
| 10 | | CiviCRM is free software; you can copy, modify, and distribute it | |
| 11 | | under the terms of the GNU Affero General Public License | |
| 12 | | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | |
| 13 | | | |
| 14 | | CiviCRM is distributed in the hope that it will be useful, but | |
| 15 | | WITHOUT ANY WARRANTY; without even the implied warranty of | |
| 16 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | |
| 17 | | See the GNU Affero General Public License for more details. | |
| 18 | | | |
| 19 | | You should have received a copy of the GNU Affero General Public | |
| 20 | | License and the CiviCRM Licensing Exception along | |
| 21 | | with this program; if not, contact CiviCRM LLC | |
| 22 | | at info[AT]civicrm[DOT]org. If you have questions about the | |
| 23 | | GNU Affero General Public License or the licensing of CiviCRM, | |
| 24 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | |
| 25 | +--------------------------------------------------------------------+ |
| 26 | */ |
| 27 | |
| 28 | /** |
| 29 | * |
| 30 | * @package CRM |
| 31 | * @copyright CiviCRM LLC (c) 2004-2015 |
| 32 | * $Id$ |
| 33 | * |
| 34 | */ |
| 35 | class CRM_Report_Form_Campaign_SurveyDetails extends CRM_Report_Form { |
| 36 | |
| 37 | protected $_emailField = FALSE; |
| 38 | |
| 39 | protected $_phoneField = FALSE; |
| 40 | |
| 41 | protected $_locationBasedPhoneField = FALSE; |
| 42 | |
| 43 | protected $_summary = NULL; |
| 44 | protected $_customGroupGroupBy = FALSE; |
| 45 | protected $_customGroupExtends = array( |
| 46 | 'Contact', |
| 47 | 'Individual', |
| 48 | 'Household', |
| 49 | 'Organization', |
| 50 | 'Activity', |
| 51 | ); |
| 52 | public $_drilldownReport = array('contact/detail' => 'Link to Detail Report'); |
| 53 | |
| 54 | private static $_surveyRespondentStatus; |
| 55 | |
| 56 | /** |
| 57 | */ |
| 58 | /** |
| 59 | */ |
| 60 | public function __construct() { |
| 61 | //filter options for survey activity status. |
| 62 | $responseStatus = array('' => '- Any -'); |
| 63 | self::$_surveyRespondentStatus = array(); |
| 64 | $activityStatus = CRM_Core_PseudoConstant::activityStatus('name'); |
| 65 | if ($statusId = array_search('Scheduled', $activityStatus)) { |
| 66 | $responseStatus[$statusId] = ts('Reserved'); |
| 67 | self::$_surveyRespondentStatus[$statusId] = 'Reserved'; |
| 68 | } |
| 69 | if ($statusId = array_search('Completed', $activityStatus)) { |
| 70 | $responseStatus[$statusId] = ts('Interviewed'); |
| 71 | self::$_surveyRespondentStatus[$statusId] = 'Interviewed'; |
| 72 | } |
| 73 | |
| 74 | $optionGroups = CRM_Campaign_BAO_Survey::getResultSets('name'); |
| 75 | $resultOptions = array(); |
| 76 | foreach ($optionGroups as $gid => $name) { |
| 77 | if ($name) { |
| 78 | $value = array(); |
| 79 | $value = CRM_Core_OptionGroup::values($name); |
| 80 | if (!empty($value)) { |
| 81 | $value = array_combine($value, $value); |
| 82 | } |
| 83 | $resultOptions = $resultOptions + $value; |
| 84 | } |
| 85 | } |
| 86 | asort($resultOptions); |
| 87 | |
| 88 | //get all interviewers. |
| 89 | $allSurveyInterviewers = CRM_Campaign_BAO_Survey::getInterviewers(); |
| 90 | |
| 91 | $this->_columns = array( |
| 92 | 'civicrm_activity_contact' => array( |
| 93 | 'dao' => 'CRM_Activity_DAO_ActivityContact', |
| 94 | 'fields' => array('contact_id' => array('title' => ts('Interviewer Name'))), |
| 95 | 'filters' => array( |
| 96 | 'contact_id' => array( |
| 97 | 'name' => 'contact_id', |
| 98 | 'title' => ts('Interviewer Name'), |
| 99 | 'type' => CRM_Utils_Type::T_INT, |
| 100 | 'operatorType' => CRM_Report_Form::OP_SELECT, |
| 101 | 'options' => array( |
| 102 | '' => ts('- any interviewer -'), |
| 103 | ) + $allSurveyInterviewers, |
| 104 | ), |
| 105 | ), |
| 106 | 'grouping' => 'survey-interviewer-fields', |
| 107 | ), |
| 108 | 'civicrm_contact' => array( |
| 109 | 'dao' => 'CRM_Contact_DAO_Contact', |
| 110 | 'fields' => array( |
| 111 | 'id' => array( |
| 112 | 'title' => ts('Contact ID'), |
| 113 | 'no_display' => TRUE, |
| 114 | 'required' => TRUE, |
| 115 | ), |
| 116 | 'sort_name' => array( |
| 117 | 'title' => ts('Respondent Name'), |
| 118 | 'required' => TRUE, |
| 119 | 'no_repeat' => TRUE, |
| 120 | ), |
| 121 | ), |
| 122 | 'filters' => array( |
| 123 | 'sort_name' => array( |
| 124 | 'title' => ts('Respondent Name'), |
| 125 | 'operator' => 'like', |
| 126 | ), |
| 127 | ), |
| 128 | 'grouping' => 'contact-fields', |
| 129 | 'order_bys' => array( |
| 130 | 'sort_name' => array( |
| 131 | 'title' => ts('Respondent Name'), |
| 132 | 'required' => TRUE, |
| 133 | ), |
| 134 | ), |
| 135 | ), |
| 136 | 'civicrm_phone' => array( |
| 137 | 'dao' => 'CRM_Core_DAO_Phone', |
| 138 | 'fields' => array( |
| 139 | 'phone' => array( |
| 140 | 'name' => 'phone', |
| 141 | 'title' => ts('Phone'), |
| 142 | ), |
| 143 | ), |
| 144 | 'grouping' => 'location-fields', |
| 145 | ), |
| 146 | 'civicrm_address' => array( |
| 147 | 'dao' => 'CRM_Core_DAO_Address', |
| 148 | 'fields' => array( |
| 149 | 'street_number' => array( |
| 150 | 'name' => 'street_number', |
| 151 | 'title' => ts('Street Number'), |
| 152 | 'type' => 1, |
| 153 | ), |
| 154 | 'street_name' => array( |
| 155 | 'name' => 'street_name', |
| 156 | 'title' => ts('Street Name'), |
| 157 | 'type' => 1, |
| 158 | ), |
| 159 | 'street_unit' => array( |
| 160 | 'name' => 'street_unit', |
| 161 | 'title' => ts('Street Unit'), |
| 162 | 'type' => 1, |
| 163 | ), |
| 164 | 'postal_code' => array( |
| 165 | 'name' => 'postal_code', |
| 166 | 'title' => ts('Postal Code'), |
| 167 | 'type' => 1, |
| 168 | ), |
| 169 | 'city' => array( |
| 170 | 'name' => 'city', |
| 171 | 'title' => ts('City'), |
| 172 | 'type' => 1, |
| 173 | ), |
| 174 | 'state_province_id' => array( |
| 175 | 'name' => 'state_province_id', |
| 176 | 'title' => ts('State/Province'), |
| 177 | ), |
| 178 | 'country_id' => array( |
| 179 | 'name' => 'country_id', |
| 180 | 'title' => ts('Country'), |
| 181 | ), |
| 182 | ), |
| 183 | 'filters' => array( |
| 184 | 'street_number' => array( |
| 185 | 'title' => ts('Street Number'), |
| 186 | 'type' => 1, |
| 187 | 'name' => 'street_number', |
| 188 | ), |
| 189 | 'street_name' => array( |
| 190 | 'title' => ts('Street Name'), |
| 191 | 'name' => 'street_name', |
| 192 | 'operator' => 'like', |
| 193 | ), |
| 194 | 'postal_code' => array( |
| 195 | 'title' => ts('Postal Code'), |
| 196 | 'type' => 1, |
| 197 | 'name' => 'postal_code', |
| 198 | ), |
| 199 | 'city' => array( |
| 200 | 'title' => ts('City'), |
| 201 | 'operator' => 'like', |
| 202 | 'name' => 'city', |
| 203 | ), |
| 204 | 'state_province_id' => array( |
| 205 | 'name' => 'state_province_id', |
| 206 | 'title' => ts('State/Province'), |
| 207 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
| 208 | 'options' => CRM_Core_PseudoConstant::stateProvince(), |
| 209 | ), |
| 210 | 'country_id' => array( |
| 211 | 'name' => 'country_id', |
| 212 | 'title' => ts('Country'), |
| 213 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
| 214 | 'options' => CRM_Core_PseudoConstant::country(), |
| 215 | ), |
| 216 | ), |
| 217 | 'order_bys' => array( |
| 218 | 'street_name' => array('title' => ts('Street Name')), |
| 219 | 'street_number_odd_even' => array( |
| 220 | 'title' => ts('Odd / Even Street Number'), |
| 221 | 'name' => 'street_number', |
| 222 | 'dbAlias' => 'address_civireport.street_number%2', |
| 223 | ), |
| 224 | 'street_number' => array('title' => 'Street Number'), |
| 225 | ), |
| 226 | 'grouping' => 'location-fields', |
| 227 | ), |
| 228 | 'civicrm_email' => array( |
| 229 | 'dao' => 'CRM_Core_DAO_Email', |
| 230 | 'fields' => array( |
| 231 | 'email' => array( |
| 232 | 'name' => 'email', |
| 233 | 'title' => ts('Email'), |
| 234 | ), |
| 235 | ), |
| 236 | 'grouping' => 'location-fields', |
| 237 | ), |
| 238 | 'civicrm_activity' => array( |
| 239 | 'dao' => 'CRM_Activity_DAO_Activity', |
| 240 | 'alias' => 'survey_activity', |
| 241 | 'fields' => array( |
| 242 | 'survey_id' => array( |
| 243 | 'name' => 'source_record_id', |
| 244 | 'title' => ts('Survey'), |
| 245 | 'type' => CRM_Utils_Type::T_INT, |
| 246 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
| 247 | 'options' => CRM_Campaign_BAO_Survey::getSurveys(), |
| 248 | ), |
| 249 | 'survey_response' => array( |
| 250 | 'name' => 'survey_response', |
| 251 | 'title' => ts('Survey Responses'), |
| 252 | ), |
| 253 | 'details' => array( |
| 254 | 'name' => 'details', |
| 255 | 'title' => ts('Note'), |
| 256 | 'type' => 1, |
| 257 | ), |
| 258 | 'result' => array( |
| 259 | 'name' => 'result', |
| 260 | 'required' => TRUE, |
| 261 | 'title' => ts('Survey Result'), |
| 262 | ), |
| 263 | ), |
| 264 | 'filters' => array( |
| 265 | 'survey_id' => array( |
| 266 | 'name' => 'source_record_id', |
| 267 | 'title' => ts('Survey'), |
| 268 | 'type' => CRM_Utils_Type::T_INT, |
| 269 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
| 270 | 'options' => CRM_Campaign_BAO_Survey::getSurveys(), |
| 271 | ), |
| 272 | 'status_id' => array( |
| 273 | 'name' => 'status_id', |
| 274 | 'title' => ts('Respondent Status'), |
| 275 | 'type' => CRM_Utils_Type::T_INT, |
| 276 | 'operatorType' => CRM_Report_Form::OP_SELECT, |
| 277 | 'options' => $responseStatus, |
| 278 | ), |
| 279 | 'result' => array( |
| 280 | 'title' => ts('Survey Result'), |
| 281 | 'type' => CRM_Utils_Type::T_STRING, |
| 282 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
| 283 | 'options' => $resultOptions, |
| 284 | ), |
| 285 | ), |
| 286 | 'grouping' => 'survey-activity-fields', |
| 287 | ), |
| 288 | ); |
| 289 | parent::__construct(); |
| 290 | } |
| 291 | |
| 292 | public function preProcess() { |
| 293 | parent::preProcess(); |
| 294 | } |
| 295 | |
| 296 | public function select() { |
| 297 | $select = array(); |
| 298 | |
| 299 | //add the survey response fields. |
| 300 | $this->_addSurveyResponseColumns(); |
| 301 | |
| 302 | $this->_columnHeaders = array(); |
| 303 | foreach ($this->_columns as $tableName => $table) { |
| 304 | if (!isset($table['fields'])) { |
| 305 | continue; |
| 306 | } |
| 307 | foreach ($table['fields'] as $fieldName => $field) { |
| 308 | if (!empty($field['required']) || |
| 309 | !empty($this->_params['fields'][$fieldName]) || |
| 310 | CRM_Utils_Array::value('is_required', $field) |
| 311 | ) { |
| 312 | |
| 313 | $fieldsName = CRM_Utils_Array::value(1, explode('_', $tableName)); |
| 314 | if ($fieldsName) { |
| 315 | $this->{"_$fieldsName" . 'Field'} = TRUE; |
| 316 | } |
| 317 | |
| 318 | //need to pickup custom data/survey response fields. |
| 319 | if ($fieldName == 'survey_response') { |
| 320 | continue; |
| 321 | } |
| 322 | |
| 323 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; |
| 324 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field); |
| 325 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field); |
| 326 | } |
| 327 | } |
| 328 | } |
| 329 | |
| 330 | $this->_select = "SELECT " . implode(",\n", $select) . " "; |
| 331 | } |
| 332 | |
| 333 | public function from() { |
| 334 | $this->_from = " FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} "; |
| 335 | $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name'); |
| 336 | $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts); |
| 337 | $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts); |
| 338 | |
| 339 | //get the activity table joins. |
| 340 | $this->_from .= " INNER JOIN civicrm_activity_contact civicrm_activity_target ON |
| 341 | ( {$this->_aliases['civicrm_contact']}.id = civicrm_activity_target.contact_id AND civicrm_activity_target.record_type_id = {$targetID}) \n"; |
| 342 | $this->_from .= " INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON |
| 343 | ( {$this->_aliases['civicrm_activity']}.id = civicrm_activity_target.activity_id )\n"; |
| 344 | $this->_from .= " INNER JOIN civicrm_activity_contact activity_contact_civireport ON |
| 345 | ( {$this->_aliases['civicrm_activity']}.id = activity_contact_civireport.activity_id AND activity_contact_civireport.record_type_id = {$assigneeID} )\n"; |
| 346 | |
| 347 | //get the address table. |
| 348 | $this->_from .= " LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} ON |
| 349 | {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND {$this->_aliases['civicrm_address']}.is_primary = 1\n"; |
| 350 | |
| 351 | if ($this->_emailField) { |
| 352 | $this->_from .= "LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} ON |
| 353 | {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1\n"; |
| 354 | } |
| 355 | |
| 356 | if ($this->_phoneField) { |
| 357 | $this->_from .= "LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} ON |
| 358 | {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND {$this->_aliases['civicrm_phone']}.is_primary = 1\n"; |
| 359 | } |
| 360 | |
| 361 | if ($this->_locationBasedPhoneField) { |
| 362 | foreach ($this->_surveyResponseFields as $key => $value) { |
| 363 | if (substr($key, 0, 5) == 'phone' && !empty($value['location_type_id']) |
| 364 | ) { |
| 365 | $fName = str_replace('-', '_', $key); |
| 366 | $this->_from .= "LEFT JOIN civicrm_phone " . |
| 367 | $this->_aliases["civicrm_phone_{$fName}"] . |
| 368 | " ON {$this->_aliases['civicrm_contact']}.id = " . |
| 369 | $this->_aliases["civicrm_phone_{$fName}"] . ".contact_id AND " . |
| 370 | $this->_aliases["civicrm_phone_{$fName}"] . |
| 371 | ".location_type_id = {$value['location_type_id']} AND " . |
| 372 | $this->_aliases["civicrm_phone_{$fName}"] . |
| 373 | ".phone_type_id = {$value['phone_type_id']}\n"; |
| 374 | } |
| 375 | } |
| 376 | } |
| 377 | } |
| 378 | |
| 379 | public function where() { |
| 380 | $clauses = array(); |
| 381 | foreach ($this->_columns as $tableName => $table) { |
| 382 | if (array_key_exists('filters', $table)) { |
| 383 | foreach ($table['filters'] as $fieldName => $field) { |
| 384 | $clause = NULL; |
| 385 | |
| 386 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { |
| 387 | $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params); |
| 388 | $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params); |
| 389 | $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params); |
| 390 | |
| 391 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); |
| 392 | } |
| 393 | else { |
| 394 | $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params); |
| 395 | if ($op) { |
| 396 | $clause = $this->whereClause($field, |
| 397 | $op, |
| 398 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), |
| 399 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), |
| 400 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) |
| 401 | ); |
| 402 | } |
| 403 | } |
| 404 | |
| 405 | if (!empty($clause)) { |
| 406 | $clauses[] = $clause; |
| 407 | } |
| 408 | } |
| 409 | } |
| 410 | } |
| 411 | |
| 412 | //apply survey activity types filter. |
| 413 | $surveyActivityTypes = CRM_Campaign_BAO_Survey::getSurveyActivityType(); |
| 414 | if (!empty($surveyActivityTypes)) { |
| 415 | $clauses[] = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN ( " . |
| 416 | implode(' , ', array_keys($surveyActivityTypes)) . ' ) )'; |
| 417 | } |
| 418 | |
| 419 | // always filter out deleted activities (so contacts that have been released |
| 420 | // don't show up in the report). |
| 421 | $clauses[] = "( {$this->_aliases['civicrm_activity']}.is_deleted = 0 )"; |
| 422 | |
| 423 | if (empty($clauses)) { |
| 424 | $this->_where = "WHERE ( 1 ) "; |
| 425 | } |
| 426 | else { |
| 427 | $this->_where = "WHERE " . implode(' AND ', $clauses); |
| 428 | } |
| 429 | |
| 430 | if ($this->_aclWhere) { |
| 431 | $this->_where .= " AND {$this->_aclWhere} "; |
| 432 | } |
| 433 | } |
| 434 | |
| 435 | public function postProcess() { |
| 436 | // get the acl clauses built before we assemble the query |
| 437 | $this->buildACLClause($this->_aliases['civicrm_contact']); |
| 438 | |
| 439 | // get ready with post process params |
| 440 | $this->beginPostProcess(); |
| 441 | |
| 442 | // build query |
| 443 | $sql = $this->buildQuery(); |
| 444 | |
| 445 | // build array of result based on column headers. This method also allows |
| 446 | // modifying column headers before using it to build result set i.e $rows. |
| 447 | $rows = array(); |
| 448 | $this->buildRows($sql, $rows); |
| 449 | |
| 450 | // format result set. |
| 451 | $this->formatDisplay($rows); |
| 452 | |
| 453 | //call local post process for only print and pdf. |
| 454 | //we do need special formatted o/p only when we do have grouping |
| 455 | $orderBys = CRM_Utils_Array::value('order_bys', $this->_params, array()); |
| 456 | if (in_array($this->_outputMode, array( |
| 457 | 'print', |
| 458 | 'pdf', |
| 459 | ))) { |
| 460 | |
| 461 | $outPut = array(); |
| 462 | $templateFile = parent::getTemplateFileName(); |
| 463 | if (array_key_exists('street_name', $orderBys) || |
| 464 | array_key_exists('street_number', $orderBys) |
| 465 | ) { |
| 466 | $orderByStreetName = CRM_Utils_Array::value('street_name', $orderBys); |
| 467 | $orderByStreetNum = CRM_Utils_Array::value('street_number', $orderBys); |
| 468 | |
| 469 | $pageCnt = 0; |
| 470 | $dataPerPage = array(); |
| 471 | $lastStreetName = $lastStreetNum = NULL; |
| 472 | foreach ($rows as $row) { |
| 473 | //do we need to take new page. |
| 474 | if ($orderByStreetName && |
| 475 | ($lastStreetName != |
| 476 | CRM_Utils_Array::value('civicrm_address_street_name', $row)) |
| 477 | ) { |
| 478 | $pageCnt++; |
| 479 | } |
| 480 | elseif ($orderByStreetNum && |
| 481 | ($lastStreetNum != |
| 482 | CRM_Utils_Array::value('civicrm_address_street_number', $row) % 2 |
| 483 | ) |
| 484 | ) { |
| 485 | $pageCnt++; |
| 486 | } |
| 487 | |
| 488 | //get the data per page. |
| 489 | $dataPerPage[$pageCnt][] = $row; |
| 490 | $lastStreetName = CRM_Utils_Array::value('civicrm_address_street_name', $row); |
| 491 | $lastStreetNum = CRM_Utils_Array::value('civicrm_address_street_number', $row) % 2; |
| 492 | } |
| 493 | |
| 494 | foreach ($dataPerPage as $page) { |
| 495 | // assign variables to templates |
| 496 | $this->doTemplateAssignment($page); |
| 497 | $outPut[] = CRM_Core_Form::$_template->fetch($templateFile); |
| 498 | } |
| 499 | } |
| 500 | else { |
| 501 | $this->doTemplateAssignment($rows); |
| 502 | $outPut[] = CRM_Core_Form::$_template->fetch($templateFile); |
| 503 | } |
| 504 | |
| 505 | $header = $this->_formValues['report_header']; |
| 506 | $footer = $this->_formValues['report_footer']; |
| 507 | |
| 508 | //get the cover sheet. |
| 509 | $coverSheet = $this->_surveyCoverSheet(); |
| 510 | $footerImage = preg_replace('/<\/html>|<\/body>|<\/div>/i', '', $footer); |
| 511 | |
| 512 | $outPut = $header . $coverSheet . |
| 513 | "<div style=\"page-break-after: always\"></div>" . |
| 514 | implode($footerImage . |
| 515 | "<div style=\"page-break-after: always\"></div>", |
| 516 | $outPut |
| 517 | ) . $footer; |
| 518 | |
| 519 | if ($this->_outputMode == 'print') { |
| 520 | echo $outPut; |
| 521 | } |
| 522 | else { |
| 523 | CRM_Utils_PDF_Utils::html2pdf($outPut, "CiviReport.pdf"); |
| 524 | } |
| 525 | |
| 526 | CRM_Utils_System::civiExit(); |
| 527 | } |
| 528 | else { |
| 529 | $this->doTemplateAssignment($rows); |
| 530 | $this->endPostProcess($rows); |
| 531 | } |
| 532 | |
| 533 | } |
| 534 | |
| 535 | /** |
| 536 | * @return bool|mixed|null|string |
| 537 | */ |
| 538 | private function _surveyCoverSheet() { |
| 539 | $coverSheet = NULL; |
| 540 | $surveyIds = CRM_Utils_Array::value('survey_id_value', $this->_params); |
| 541 | if (CRM_Utils_System::isNull($surveyIds)) { |
| 542 | return $coverSheet; |
| 543 | } |
| 544 | |
| 545 | $fieldIds = array(); |
| 546 | |
| 547 | $surveyResponseFields = array(); |
| 548 | foreach ($this->_columns as $tableName => $values) { |
| 549 | if (!is_array($values['fields'])) { |
| 550 | continue; |
| 551 | } |
| 552 | foreach ($values['fields'] as $name => $field) { |
| 553 | if (!empty($field['isSurveyResponseField'])) { |
| 554 | $fldId = substr($name, 7); |
| 555 | $fieldIds[$fldId] = $fldId; |
| 556 | $title = CRM_Utils_Array::value('label', $field, $field['title']); |
| 557 | $surveyResponseFields[$name] = array( |
| 558 | 'id' => $fldId, |
| 559 | 'title' => $title, |
| 560 | 'name' => "{$tableName}_{$name}", |
| 561 | ); |
| 562 | } |
| 563 | } |
| 564 | } |
| 565 | |
| 566 | //now pickup all options. |
| 567 | if (!empty($fieldIds)) { |
| 568 | $query = ' |
| 569 | SELECT field.id as id, |
| 570 | val.label as label, |
| 571 | val.value as value |
| 572 | FROM civicrm_custom_field field |
| 573 | INNER JOIN civicrm_option_value val ON ( val.option_group_id = field.option_group_id ) |
| 574 | WHERE field.id IN (' . implode(' , ', $fieldIds) . ' ) |
| 575 | Order By val.weight'; |
| 576 | $field = CRM_Core_DAO::executeQuery($query); |
| 577 | $options = array(); |
| 578 | while ($field->fetch()) { |
| 579 | $name = "custom_{$field->id}"; |
| 580 | $surveyResponseFields[$name]['options'][$field->value] = $field->label; |
| 581 | } |
| 582 | } |
| 583 | |
| 584 | //get the result values. |
| 585 | $query = ' |
| 586 | SELECT survey.id as id, |
| 587 | survey.title as title, |
| 588 | val.label as label, |
| 589 | val.value as value |
| 590 | FROM civicrm_survey survey |
| 591 | INNER JOIN civicrm_option_value val ON ( val.option_group_id = survey.result_id ) |
| 592 | WHERE survey.id IN ( ' . implode(' , ', array_values($surveyIds)) . ' ) |
| 593 | Order By val.weight'; |
| 594 | $resultSet = CRM_Core_DAO::executeQuery($query); |
| 595 | $surveyResultFields = array(); |
| 596 | while ($resultSet->fetch()) { |
| 597 | $surveyResultFields[$resultSet->id]['title'] = $resultSet->title; |
| 598 | $surveyResultFields[$resultSet->id]['options'][$resultSet->value] = $resultSet->label; |
| 599 | } |
| 600 | |
| 601 | $this->assign('surveyResultFields', $surveyResultFields); |
| 602 | $this->assign('surveyResponseFields', $surveyResponseFields); |
| 603 | |
| 604 | $templateFile = 'CRM/Report/Form/Campaign/SurveyCoverSheet.tpl'; |
| 605 | $coverSheet = CRM_Core_Form::$_template->fetch($templateFile); |
| 606 | |
| 607 | return $coverSheet; |
| 608 | } |
| 609 | |
| 610 | /** |
| 611 | * Alter display of rows. |
| 612 | * |
| 613 | * Iterate through the rows retrieved via SQL and make changes for display purposes, |
| 614 | * such as rendering contacts as links. |
| 615 | * |
| 616 | * @param array $rows |
| 617 | * Rows generated by SQL, with an array for each row. |
| 618 | */ |
| 619 | public function alterDisplay(&$rows) { |
| 620 | |
| 621 | $this->_formatSurveyResult($rows); |
| 622 | $this->_formatSurveyResponseData($rows); |
| 623 | |
| 624 | $entryFound = FALSE; |
| 625 | foreach ($rows as $rowNum => $row) { |
| 626 | // handle state province |
| 627 | if (array_key_exists('civicrm_address_state_province_id', $row)) { |
| 628 | if ($value = $row['civicrm_address_state_province_id']) { |
| 629 | $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value); |
| 630 | } |
| 631 | $entryFound = TRUE; |
| 632 | } |
| 633 | |
| 634 | // handle country |
| 635 | if (array_key_exists('civicrm_address_country_id', $row)) { |
| 636 | if ($value = $row['civicrm_address_country_id']) { |
| 637 | $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value); |
| 638 | } |
| 639 | $entryFound = TRUE; |
| 640 | } |
| 641 | |
| 642 | // convert display name to links |
| 643 | if (array_key_exists('civicrm_contact_sort_name', $row) && |
| 644 | array_key_exists('civicrm_contact_id', $row) |
| 645 | ) { |
| 646 | $url = CRM_Report_Utils_Report::getNextUrl('contact/detail', |
| 647 | 'reset=1&force=1&id_op=eq&id_value=' . |
| 648 | $row['civicrm_contact_id'], |
| 649 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport |
| 650 | ); |
| 651 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; |
| 652 | $entryFound = TRUE; |
| 653 | } |
| 654 | |
| 655 | if (array_key_exists('civicrm_activity_contact_contact_id', $row)) { |
| 656 | $rows[$rowNum]['civicrm_activity_contact_contact_id'] = CRM_Utils_Array::value($row['civicrm_activity_contact_contact_id'], |
| 657 | CRM_Campaign_BAO_Survey::getInterviewers() |
| 658 | ); |
| 659 | $entryFound = TRUE; |
| 660 | } |
| 661 | |
| 662 | if (array_key_exists('civicrm_activity_survey_id', $row)) { |
| 663 | $rows[$rowNum]['civicrm_activity_survey_id'] = CRM_Utils_Array::value($row['civicrm_activity_survey_id'], |
| 664 | CRM_Campaign_BAO_Survey::getSurveys() |
| 665 | ); |
| 666 | $entryFound = TRUE; |
| 667 | } |
| 668 | |
| 669 | // skip looking further in rows, if first row itself doesn't |
| 670 | // have the column we need |
| 671 | if (!$entryFound) { |
| 672 | break; |
| 673 | } |
| 674 | } |
| 675 | } |
| 676 | |
| 677 | /** |
| 678 | * @param $rows |
| 679 | */ |
| 680 | private function _formatSurveyResult(&$rows) { |
| 681 | $surveyIds = CRM_Utils_Array::value('survey_id_value', $this->_params); |
| 682 | if (CRM_Utils_System::isNull($surveyIds) || |
| 683 | empty($this->_params['fields']['result']) || |
| 684 | !in_array($this->_outputMode, array('print', 'pdf')) |
| 685 | ) { |
| 686 | return; |
| 687 | } |
| 688 | |
| 689 | //swap the survey result label w/ value. |
| 690 | $query = ' |
| 691 | SELECT survey.id as id, |
| 692 | val.label as label, |
| 693 | val.value as value |
| 694 | FROM civicrm_option_value val |
| 695 | INNER JOIN civicrm_option_group grp ON ( grp.id = val.option_group_id ) |
| 696 | INNER JOIN civicrm_survey survey ON ( survey.result_id = grp.id ) |
| 697 | WHERE survey.id IN (' . implode(' , ', array_values($surveyIds)) . ' ) |
| 698 | Order By val.weight'; |
| 699 | |
| 700 | $result = CRM_Core_DAO::executeQuery($query); |
| 701 | $resultSet = array(); |
| 702 | while ($result->fetch()) { |
| 703 | $resultSet[$result->id][$result->value] = $result->label; |
| 704 | } |
| 705 | |
| 706 | $statusId = CRM_Utils_Array::value('status_id_value', $this->_params); |
| 707 | $respondentStatus = CRM_Utils_Array::value($statusId, self::$_surveyRespondentStatus); |
| 708 | |
| 709 | $surveyId = CRM_Utils_Array::value(0, $surveyIds); |
| 710 | foreach ($rows as & $row) { |
| 711 | if (!empty($row['civicrm_activity_survey_id'])) { |
| 712 | $surveyId = $row['civicrm_activity_survey_id']; |
| 713 | } |
| 714 | $result = CRM_Utils_Array::value($surveyId, $resultSet, array()); |
| 715 | $resultLabel = CRM_Utils_Array::value('civicrm_activity_result', $row); |
| 716 | if ($respondentStatus == 'Reserved') { |
| 717 | $row['civicrm_activity_result'] = implode(' | ', array_keys($result)); |
| 718 | } |
| 719 | elseif ($resultLabel) { |
| 720 | $resultValue = array_search($resultLabel, $result); |
| 721 | if ($resultValue) { |
| 722 | $row['civicrm_activity_result'] = $resultValue; |
| 723 | } |
| 724 | } |
| 725 | } |
| 726 | } |
| 727 | |
| 728 | /** |
| 729 | * @param $rows |
| 730 | */ |
| 731 | private function _formatSurveyResponseData(&$rows) { |
| 732 | $surveyIds = CRM_Utils_Array::value('survey_id_value', $this->_params); |
| 733 | if (CRM_Utils_System::isNull($surveyIds) || |
| 734 | empty($this->_params['fields']['survey_response']) |
| 735 | ) { |
| 736 | return; |
| 737 | } |
| 738 | |
| 739 | $surveyResponseFields = array(); |
| 740 | $surveyResponseFieldIds = array(); |
| 741 | foreach ($this->_columns as $tableName => $values) { |
| 742 | if (!is_array($values['fields'])) { |
| 743 | continue; |
| 744 | } |
| 745 | foreach ($values['fields'] as $name => $field) { |
| 746 | if (!empty($field['isSurveyResponseField'])) { |
| 747 | $fldId = substr($name, 7); |
| 748 | $surveyResponseFields[$name] = "{$tableName}_{$name}"; |
| 749 | $surveyResponseFieldIds[$fldId] = $fldId; |
| 750 | } |
| 751 | } |
| 752 | } |
| 753 | |
| 754 | if (empty($surveyResponseFieldIds)) { |
| 755 | return; |
| 756 | } |
| 757 | |
| 758 | $hasResponseData = FALSE; |
| 759 | foreach ($surveyResponseFields as $fldName) { |
| 760 | foreach ($rows as $row) { |
| 761 | if (!empty($row[$fldName])) { |
| 762 | $hasResponseData = TRUE; |
| 763 | break; |
| 764 | } |
| 765 | } |
| 766 | } |
| 767 | |
| 768 | //do check respondent status. |
| 769 | $statusId = CRM_Utils_Array::value('status_id_value', $this->_params); |
| 770 | $respondentStatus = CRM_Utils_Array::value($statusId, self::$_surveyRespondentStatus); |
| 771 | |
| 772 | if (!$hasResponseData && |
| 773 | ($respondentStatus != 'Reserved') |
| 774 | ) { |
| 775 | return; |
| 776 | } |
| 777 | |
| 778 | //start response data formatting. |
| 779 | $query = ' |
| 780 | SELECT cf.id, |
| 781 | cf.data_type, |
| 782 | cf.html_type, |
| 783 | cg.table_name, |
| 784 | cf.column_name, |
| 785 | ov.value, ov.label, |
| 786 | cf.option_group_id |
| 787 | FROM civicrm_custom_field cf |
| 788 | INNER JOIN civicrm_custom_group cg ON ( cg.id = cf.custom_group_id ) |
| 789 | LEFT JOIN civicrm_option_value ov ON ( cf.option_group_id = ov.option_group_id ) |
| 790 | WHERE cf.id IN ( ' . implode(' , ', $surveyResponseFieldIds) . ' ) |
| 791 | Order By ov.weight'; |
| 792 | |
| 793 | $responseFields = array(); |
| 794 | $fieldValueMap = array(); |
| 795 | $properties = array( |
| 796 | 'id', |
| 797 | 'data_type', |
| 798 | 'html_type', |
| 799 | 'column_name', |
| 800 | 'option_group_id', |
| 801 | ); |
| 802 | |
| 803 | $responseField = CRM_Core_DAO::executeQuery($query); |
| 804 | while ($responseField->fetch()) { |
| 805 | $reponseFldName = $responseField->table_name . '_custom_' . |
| 806 | $responseField->id; |
| 807 | foreach ($properties as $prop) { |
| 808 | $responseFields[$reponseFldName][$prop] = $responseField->$prop; |
| 809 | } |
| 810 | if ($responseField->option_group_id) { |
| 811 | //show value for print and pdf. |
| 812 | $value = $responseField->label; |
| 813 | if (in_array($this->_outputMode, array( |
| 814 | 'print', |
| 815 | 'pdf', |
| 816 | ))) { |
| 817 | $value = $responseField->value; |
| 818 | } |
| 819 | $fieldValueMap[$responseField->option_group_id][$responseField->value] = $value; |
| 820 | } |
| 821 | } |
| 822 | $responseField->free(); |
| 823 | |
| 824 | //actual data formatting. |
| 825 | $hasData = FALSE; |
| 826 | foreach ($rows as & $row) { |
| 827 | if (!is_array($row)) { |
| 828 | continue; |
| 829 | } |
| 830 | |
| 831 | foreach ($row as $name => & $value) { |
| 832 | if (!array_key_exists($name, $responseFields)) { |
| 833 | continue; |
| 834 | } |
| 835 | $hasData = TRUE; |
| 836 | if ($respondentStatus == 'Reserved' && |
| 837 | in_array($this->_outputMode, array('print', 'pdf')) |
| 838 | ) { |
| 839 | $optGrpId = CRM_Utils_Array::value('option_group_id', $responseFields[$name]); |
| 840 | $options = CRM_Utils_Array::value($optGrpId, $fieldValueMap, array()); |
| 841 | $value = implode(' | ', array_keys($options)); |
| 842 | } |
| 843 | else { |
| 844 | $value = $this->formatCustomValues($value, |
| 845 | $responseFields[$name], |
| 846 | $fieldValueMap |
| 847 | ); |
| 848 | } |
| 849 | } |
| 850 | |
| 851 | if (!$hasData) { |
| 852 | break; |
| 853 | } |
| 854 | } |
| 855 | } |
| 856 | |
| 857 | private function _addSurveyResponseColumns() { |
| 858 | $surveyIds = CRM_Utils_Array::value('survey_id_value', $this->_params); |
| 859 | if (CRM_Utils_System::isNull($surveyIds) || |
| 860 | empty($this->_params['fields']['survey_response']) |
| 861 | ) { |
| 862 | return; |
| 863 | } |
| 864 | |
| 865 | $responseFields = array(); |
| 866 | foreach ($surveyIds as $surveyId) { |
| 867 | $responseFields += CRM_Campaign_BAO_Survey::getSurveyResponseFields($surveyId); |
| 868 | $this->_surveyResponseFields = $responseFields; |
| 869 | } |
| 870 | foreach ($responseFields as $key => $value) { |
| 871 | if (substr($key, 0, 5) == 'phone' && !empty($value['location_type_id'])) { |
| 872 | $fName = str_replace('-', '_', $key); |
| 873 | $this->_columns["civicrm_{$fName}"] = array( |
| 874 | 'dao' => 'CRM_Core_DAO_Phone', |
| 875 | 'alias' => "phone_civireport_{$fName}", |
| 876 | 'fields' => array( |
| 877 | $fName => array_merge($value, array( |
| 878 | 'is_required' => '1', |
| 879 | 'alias' => "phone_civireport_{$fName}", |
| 880 | 'dbAlias' => "phone_civireport_{$fName}.phone", |
| 881 | 'no_display' => TRUE, |
| 882 | ) |
| 883 | ), |
| 884 | ), |
| 885 | ); |
| 886 | $this->_aliases["civicrm_phone_{$fName}"] = $this->_columns["civicrm_{$fName}"]['alias']; |
| 887 | $this->_locationBasedPhoneField = TRUE; |
| 888 | } |
| 889 | } |
| 890 | $responseFieldIds = array(); |
| 891 | foreach (array_keys($responseFields) as $key) { |
| 892 | $cfId = CRM_Core_BAO_CustomField::getKeyID($key); |
| 893 | if ($cfId) { |
| 894 | $responseFieldIds[$cfId] = $cfId; |
| 895 | } |
| 896 | } |
| 897 | if (empty($responseFieldIds)) { |
| 898 | return; |
| 899 | } |
| 900 | |
| 901 | $query = ' |
| 902 | SELECT cg.extends, |
| 903 | cf.data_type, |
| 904 | cf.html_type, |
| 905 | cg.table_name, |
| 906 | cf.column_name, |
| 907 | cf.time_format, |
| 908 | cf.id as cfId, |
| 909 | cf.option_group_id |
| 910 | FROM civicrm_custom_group cg |
| 911 | INNER JOIN civicrm_custom_field cf ON ( cg.id = cf.custom_group_id ) |
| 912 | WHERE cf.id IN ( ' . implode(' , ', $responseFieldIds) . |
| 913 | ' ) ORDER BY cf.weight'; |
| 914 | $response = CRM_Core_DAO::executeQuery($query); |
| 915 | $fildCnt = 1; |
| 916 | while ($response->fetch()) { |
| 917 | $resTable = $response->table_name; |
| 918 | $fieldName = "custom_{$response->cfId}"; |
| 919 | |
| 920 | //need to check does these custom data already included. |
| 921 | |
| 922 | if (!array_key_exists($resTable, $this->_columns)) { |
| 923 | $this->_columns[$resTable]['dao'] = 'CRM_Contact_DAO_Contact'; |
| 924 | $this->_columns[$resTable]['extends'] = $response->extends; |
| 925 | } |
| 926 | if (empty($this->_columns[$resTable]['alias'])) { |
| 927 | $this->_columns[$resTable]['alias'] = "{$resTable}_survey_response"; |
| 928 | } |
| 929 | if (!is_array(CRM_Utils_Array::value('fields', $this->_columns[$resTable]))) { |
| 930 | $this->_columns[$resTable]['fields'] = array(); |
| 931 | } |
| 932 | if (array_key_exists($fieldName, $this->_columns[$resTable]['fields'])) { |
| 933 | $this->_columns[$resTable]['fields'][$fieldName]['required'] = TRUE; |
| 934 | $this->_columns[$resTable]['fields'][$fieldName]['isSurveyResponseField'] = TRUE; |
| 935 | continue; |
| 936 | } |
| 937 | |
| 938 | $title = $responseFields[$fieldName]['title']; |
| 939 | if (in_array($this->_outputMode, array( |
| 940 | 'print', |
| 941 | 'pdf', |
| 942 | ))) { |
| 943 | $title = 'Q' . $fildCnt++; |
| 944 | } |
| 945 | |
| 946 | $fldType = 'CRM_Utils_Type::T_STRING'; |
| 947 | if ($response->time_format) { |
| 948 | $fldType = CRM_Utils_Type::T_TIMESTAMP; |
| 949 | } |
| 950 | $field = array( |
| 951 | 'name' => $response->column_name, |
| 952 | 'type' => $fldType, |
| 953 | 'title' => $title, |
| 954 | 'label' => $responseFields[$fieldName]['title'], |
| 955 | 'dataType' => $response->data_type, |
| 956 | 'htmlType' => $response->html_type, |
| 957 | 'required' => TRUE, |
| 958 | 'alias' => ($response->data_type == 'ContactReference') ? $this->_columns[$resTable]['alias'] . |
| 959 | '_contact' : $this->_columns[$resTable]['alias'], |
| 960 | 'dbAlias' => $this->_columns[$resTable]['alias'] . '.' . |
| 961 | $response->column_name, |
| 962 | 'no_display' => TRUE, |
| 963 | 'isSurveyResponseField' => TRUE, |
| 964 | ); |
| 965 | |
| 966 | $this->_columns[$resTable]['fields'][$fieldName] = $field; |
| 967 | $this->_aliases[$resTable] = $this->_columns[$resTable]['alias']; |
| 968 | } |
| 969 | } |
| 970 | |
| 971 | } |