3
* Class to export all staffs and their latest resource types.
5
* It is called by the staff action class.
9
* LICENSE: This source file is subject to LGPLv2.1 license
10
* that is available through the world-wide-web at the following URI:
11
* http://www.gnu.org/licenses/lgpl-2.1.html
13
* @author Shirley Chan, CUNY SPS
15
* Copyright of the Sahana Software Foundation, sahanafoundation.org
21
* The construct() method sets up array variables for header mappings and lookup definitions.
23
function __construct() {
24
// The array index specify the order of the header in the export file.
25
$this->exportHeaders = array(
35
'Home Address Line 1',
36
'Home Address Line 2',
40
'Home Address Country',
43
'Work Address Line 1',
44
'Work Address Line 2',
49
'Work Address Country',
63
'Drivers License Class',
68
$this->lookUps = array(
70
'selectTable' => 'agAddressValue',
71
'selectColumn' => 'value',
72
'whereColumn' => 'address_element_id',
75
'resourcetype' => array(
76
'selectTable' => 'agStaffResourceType',
77
'selectColumn' => 'staff_resource_type',
78
'whereColumn' => null,
81
'resourcestatus' => array(
82
'selectTable' => 'agStaffResourceStatus',
83
'selectColumn' => 'staff_resource_status',
84
'whereColumn' => null,
88
'selectTable' => 'agLanguage',
89
'selectColumn' => 'language',
90
'whereColumn' => null,
93
'competency' => array(
94
'selectTable' => 'agLanguageCompetency',
95
'selectColumn' => 'language_competency',
96
'whereColumn' => null,
99
'organization' => array(
100
'selectTable' => 'agOrganization',
101
'selectColumn' => 'organization',
102
'whereColumn' => null,
107
// This array is reconstructed below when retrieving staff resoure information.
108
$this->staffResourceHeaderMapping = array();
110
$this->nameHeaderMapping = array(
111
'given' => 'First Name',
112
'middle' => 'Middle Name',
113
'family' => 'Last Name'
116
$this->phoneHeaderMapping = array(
117
'mobile' => 'Mobile Phone',
118
'home'=> 'Home Phone',
119
'work' => 'Work Phone'
122
$this->emailHeaderMapping = array(
123
'personal' => 'Home Email',
124
'work' => 'Work Email'
127
$this->addressLineHeaderMapping = array(
128
'line 1' => 'Address Line 1',
129
'line 2' => 'Address Line 2',
130
'city' => 'Address City',
131
'state' => 'Address State',
132
'zip5' => 'Address Zip',
133
'country' => 'Address Country',
134
'latitude' => 'Latitude',
135
'longitude' => 'Longitude'
138
$this->addressTypeHeaderRequirements = array('home' => 'Home', 'work' => 'Work');
140
$this->languageFormatTypeHeaderRequirements = array('read' => 'Read',
146
* A quick method to take in an array of person custom fields and return an array of person
149
* @param array $personCustomFields A simple array of person custom fields. Limit the select
150
* query to the fields specified in the array. If NULL, return all person custom fields.
151
* @return array $results An associative array, keyed by person custom field, with a value of
152
* person_custom_field_id.
154
public function getPersonCustomFields(array $personCustomFields = NULL)
156
$q = agDoctrineQuery::create()
157
->select('pcf.person_custom_field')
158
->addSelect('pcf.id')
159
->from('agPersonCustomField AS pcf');
161
if (!is_null($personCustomFields))
163
$q->whereIn('pcf.person_custom_field', $personCustomFields);
166
$results = $q->execute(array(), agDoctrineQuery::HYDRATE_KEY_VALUE_PAIR);
171
* Method to collect all associating staff information and return them in an associative array.
173
* @param array $staff_ids An array of staff ids.
174
* @return array $staffInfo An associative array of staff information.
176
private function getStaffResourceGeneralInfo(array $staff_ids = NULL)
178
$staffInfo = array();
180
$this->staffResourceHeaderMapping = array(
181
'Entity ID' => 'p_entity_id',
182
'Organization' => 'o_organization',
183
'Resource Type' => 'srt_staff_resource_type',
184
'Resource Status' => 'srs_staff_resource_status'
187
$q = agDoctrineQuery::create()
189
->addSelect('sr.staff_id')
190
->addSelect('s.person_id')
191
->addSelect('p.entity_id')
192
->addSelect('srt.staff_resource_type')
194
->addSelect('o.organization')
195
->addSelect('srs.id')
196
->addSelect('srs.staff_resource_status')
197
->addSelect('pcfv1.value')
198
->addSelect('pcfv2.value')
199
->addSelect('pcfv3.value')
200
->from('agStaffResource AS sr')
201
->innerJoin('sr.agStaff AS s')
202
->innerJoin('s.agPerson AS p')
203
->innerJoin('sr.agStaffResourceType AS srt')
204
->innerJoin('sr.agStaffResourceStatus AS srs')
205
->innerJoin('sr.agOrganization AS o');
207
// Query for person_custom_field and their ids.
208
$personCustomFields = array('Drivers License Class',
210
'Civil Service Title');
211
$personCustomFieldIds = $this->getPersonCustomFields($personCustomFields);
213
// Add person custom fields to query.
215
foreach ($personCustomFields AS $customField)
217
$tblAlias = 'pcfv' . $cnt++;
218
$q->leftJoin('p.agPersonCustomFieldValue AS ' . $tblAlias .
219
' WITH ' . $tblAlias . '.person_custom_field_id = ' .
220
$personCustomFieldIds[$customField]);
221
$this->staffResourceHeaderMapping[$customField] = $tblAlias . '_value';
224
if (!is_null($staff_ids))
226
$q->whereIn('sr.staff_id', $staff_ids);
229
$results = $q->execute(array(), DOCTRINE_CORE::HYDRATE_SCALAR);
231
// Construct the array of the staff's custom fields.
232
foreach ($results AS $row)
235
foreach ($personCustomFields AS $customField)
237
$tblAlias = 'pcfv' . $cnt++;
238
$staffCustomFields[$row['sr_id']][$customField] = $row[$tblAlias . '_value'];
242
// Construct arrays for later use from the query results
243
foreach ($results AS $index => $staffResource)
245
$staffResourceId = array_shift($staffResource);
246
$staffInfo[$staffResourceId] = $staffResource;
253
* A method to build the associate array for exporting staff information.
255
* @return array $content An associate array of staff resource information in the export format.
257
private function buildExportRecords()
259
$staffResources = agDoctrineQuery::create()
261
->addSelect('sr.staff_id')
262
->addSelect('s.person_id')
263
->addSelect('p.entity_id')
264
->from('agStaffResource AS sr')
265
->innerJoin('sr.agStaff AS s')
266
->innerJoin('s.agPerson p')
267
->execute(array(), Doctrine_Core::HYDRATE_SCALAR);
269
// Build person id and entity id arrays for later use to retrieve persons names and contacts.
270
$staff_ids = array();
271
$entity_ids = array();
272
$person_ids = array();
273
foreach ($staffResources AS $stfRsc)
275
$staff_ids[] = $stfRsc['sr_staff_id'];
276
$person_ids[] = $stfRsc['s_person_id'];
277
$entity_ids[] = $stfRsc['p_entity_id'];
279
unset($staffResources, $stfRsc);
281
// Process records by batch.
283
$defaultBatchSize = agGlobal::getParam('default_batch_size');
284
while (!empty($staff_ids))
286
// Process by batches.
287
$subsetSIds = array_slice($staff_ids, 0, $defaultBatchSize);
288
array_splice($staff_ids, 0, $defaultBatchSize);
289
$subsetPIds = array_slice($person_ids, 0, $defaultBatchSize);
290
array_splice($person_ids, 0, $defaultBatchSize);
291
$subsetEIds = array_slice($entity_ids, 0, $defaultBatchSize);
292
array_splice($entity_ids, 0, $defaultBatchSize);
294
$staffInfo = $this->getStaffResourceGeneralInfo($subsetSIds);
296
// Collect staffs' names, their contact information, and their language competency.
297
$personNameHelper = new agPersonNameHelper();
298
$staffNames = $personNameHelper->getPrimaryNameByType($subsetPIds);
299
$phoneHelper = new agEntityPhoneHelper();
300
$staffPhones = $phoneHelper->getEntityPhoneByType($subsetEIds, TRUE, TRUE,
301
agPhoneHelper::PHN_GET_COMPONENT);
302
$emailHelper = new agEntityEmailHelper();
303
$staffEmails = $emailHelper->getEntityEmailByType($subsetEIds, TRUE, TRUE,
304
agEmailHelper::EML_GET_VALUE);
305
$addressHelper = new agEntityAddressHelper();
306
$staffAddresses = $addressHelper->getEntityAddressByType($subsetEIds, TRUE, TRUE,
307
agAddressHelper::ADDR_GET_TYPE);
308
$languageHelper = new agPersonLanguageHelper();
309
$staffLanguages = $languageHelper->getPersonLanguage($subsetPIds, FALSE);
311
foreach($staffInfo AS $stfRscIds => $stfRsc)
314
foreach($this->exportHeaders AS $header)
316
$row[$header] = null;
319
// Populate staff resource information into $row array.
320
foreach ($this->staffResourceHeaderMapping AS $header => $field)
322
$row[$header] = $stfRsc[$field];
325
// Populate staff's name into $row array.
326
if (array_key_exists($stfRsc['s_person_id'], $staffNames))
328
foreach($staffNames[$stfRsc['s_person_id']] AS $nameType => $name)
330
// Populate the row with name components only for the ones that we care for.
331
if (array_key_exists($nameType, $this->nameHeaderMapping))
333
$row[$this->nameHeaderMapping[$nameType]] = $name;
336
unset($staffNames['s_person_id']);
339
// Populate staff's emails into $row array.
340
if (array_key_exists($stfRsc['p_entity_id'], $staffPhones))
342
foreach($staffPhones[$stfRsc['p_entity_id']] AS $phoneType => $phone)
344
// Populate the row with email only for the email type that we care for.
345
if (array_key_exists($phoneType, $this->phoneHeaderMapping))
347
$row[$this->phoneHeaderMapping[$phoneType]] = $phone[0][0];
350
unset($staffPhones['s_entity_id']);
353
// Populate staff's phone numbers into $row array.
354
if (array_key_exists($stfRsc['p_entity_id'], $staffEmails))
356
foreach($staffEmails[$stfRsc['p_entity_id']] AS $emailType => $email)
358
// Populate the row with email only for the email type that we care for.
359
if (array_key_exists($emailType, $this->emailHeaderMapping))
361
$row[$this->emailHeaderMapping[$emailType]] = $email[0][0];
364
unset($staffEmails['s_entity_id']);
367
// Populate staff's address & geo info into $row array.
368
if (array_key_exists($stfRsc['p_entity_id'], $staffAddresses))
370
foreach ($staffAddresses[$stfRsc['p_entity_id']] AS $addressType => $address)
372
// Populate the row with address & geo info only for the address types and address
373
// elements that we care for.
374
if (array_key_exists($addressType, $this->addressTypeHeaderRequirements))
376
foreach ($address[0][0] AS $elem => $value)
378
if (array_key_exists($elem, $this->addressLineHeaderMapping))
380
$type = $this->addressTypeHeaderRequirements[$addressType];
381
$component = $this->addressLineHeaderMapping[$elem];
382
$header = $type . ' ' . $component;
383
$row[$header] = $value;
388
unset($staffAddresses['p_entity_id']);
391
// Populate staff's languages into $row.
393
$max_language_count = 2;
394
if (array_key_exists($stfRsc['s_person_id'], $staffLanguages))
396
foreach($staffLanguages[$stfRsc['s_person_id']] AS $priority => $languageComponents)
398
if ($iteration <= $max_language_count)
400
$language = $languageComponents[0];
401
$languageHeader = 'Language ' . $iteration;
402
$row[$languageHeader] = $language;
404
if (isset($languageComponents[1]))
406
$languageFormats = $languageComponents[1];
407
foreach($languageFormats AS $format => $competency)
409
$formatType = $this->languageFormatTypeHeaderRequirements[$format];
410
$languageFormatHeader = 'L' . $iteration . ' ' . $formatType;
411
$row[$languageFormatHeader] = $competency;
423
// Add row to $content for exporting.
432
* This function calls the other functions needed to export staff data and
433
* returns the constructed XLS file.
435
* @return array() $exportResponse An associative array of two elements,
436
* fileName and filePath. fileName is the name
437
* of the XLS file that has been constructed
438
* and is held in temporary storage.
439
* filePath is the path to that file.
441
public function export()
443
$staffExportRecords = $this->buildExportRecords();
444
$lookUps = $this->gatherLookupValues($this->lookUps);
445
$exportResponse = $this->buildXls($staffExportRecords, $lookUps);
446
return $exportResponse;
451
* @param array() $staffExportRecords Complete set of staff export records.
452
* from buildExportRecords().
454
* @param array() $lookUpContent Values for the lookup columns in the last
455
* sheet of the generated XLS file. From
456
* gatherLookupValues().
458
* @return array An associative array of two elements,
459
* fileName and filePath. fileName is the
460
* name of the XLS file that has been
461
* constructed and is held in temporary
462
* storage. filePath is the path to that file.
464
private function buildXls($staffExportRecords, $lookUpContent)
466
require_once 'PHPExcel/Cell/AdvancedValueBinder.php';
467
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
469
$objPHPExcel = new sfPhpExcel();
470
$objPHPExcel->setActiveSheetIndex(0);
471
$objPHPExcel->getActiveSheet()->setTitle("Sheet 1");
473
$objPHPExcel->getProperties()->setCreator("Agasti 2.0");
474
$objPHPExcel->getProperties()->setLastModifiedBy("Agasti 2.0");
475
$objPHPExcel->getProperties()->setTitle("Facility List");
477
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial');
478
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12);
481
$this->buildSheetHeaders($objPHPExcel);
483
// Create the lookup/definition sheet. Function?
484
$lookUpSheet = new PHPExcel_Worksheet($objPHPExcel, 'Lookup');
485
// Populate the lookup sheet.
487
foreach($lookUpContent as $key => $column) {
488
$lookUpSheet->getCellByColumnAndRow($c, 1)->setValue($key);
489
foreach($column as $k => $value) {
490
$lookUpSheet->getCellByColumnAndRow($c, ($k + 2))->setValue($value);
495
$highestColumn = $lookUpSheet->getHighestColumn();
496
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
497
for ($i = $highestColumnIndex; $i >= 0; $i--) {
498
$lookUpSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
502
foreach ($staffExportRecords as $rKey => $staffExportRecord) {
503
if($rKey <> 0 && (($rKey) % 64000 == 0)) {
504
// check if the row limit has been reached (up this to 64,000 later)
505
// if we get in here, set the cell sizes on the sheet that was just finished.
506
// Then make a new sheet, set it to active, build its headers, and reset
508
$this->sizeColumns($objPHPExcel);
509
$objPHPExcel->createSheet();
510
$objPHPExcel->setActiveSheetIndex($objPHPExcel->getActiveSheetIndex() + 1);
511
$objPHPExcel->getActiveSheet()->setTitle("Sheet " . ($objPHPExcel->getActiveSheetIndex() + 1));
512
$this->buildSheetHeaders($objPHPExcel);
515
foreach ($this->exportHeaders as $hKey => $heading) {
516
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($hKey, $row)->setValue($staffExportRecord[$heading]);
517
if(array_key_exists($heading, $lookUpContent)) {
518
$columnNumber = array_search($heading, array_keys($lookUpContent));
519
$columnLetter = base_convert(($columnNumber +10), 10, 36);
520
$topRow = count($lookUpContent[$heading]) + 1;
521
$objValidation = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($hKey, $row)->getDataValidation();
522
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
523
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
524
$objValidation->setAllowBlank(true);
525
$objValidation->setShowInputMessage(true);
526
$objValidation->setShowErrorMessage(true);
527
$objValidation->setShowDropDown(true);
528
$objValidation->setErrorTitle('Input error');
529
$objValidation->setError('Value is not in list.');
530
$objValidation->setPromptTitle('Pick from list');
531
$objValidation->setPrompt('Please pick a value from the drop-down list.');
532
$objValidation->setFormula1('Lookup!$' . $columnLetter . '$2:$'. $columnLetter . '$' . $topRow);
537
$this->sizeColumns($objPHPExcel);
539
// Add the lookup sheet. The null argument makes it the last sheet.
540
$objPHPExcel->addSheet($lookUpSheet, null);
542
$objPHPExcel->setActiveSheetIndex(0);
543
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
544
$todaydate = date("d-m-y");
545
$todaydate = $todaydate . '-' . date("H-i-s");
546
$fileName = 'Staffs';
547
$fileName = $fileName . '-' . $todaydate;
548
$fileName = $fileName . '.xls';
549
$filePath = realpath(sys_get_temp_dir()) . '/' . $fileName;
550
$objWriter->save($filePath);
551
return array('fileName' => $fileName, 'filePath' => $filePath);
556
* Each of the column in the XLS's active sheet is sized to display all contents.
558
* @param sfPhpExcel object $objPHPExcel The sfPhpExcel object that is being
559
* populated by buildXls.
561
private function sizeColumns($objPHPExcel)
563
$highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
564
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
565
for ($i = $highestColumnIndex; $i >= 0; $i--) {
566
$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
571
* $this->exportHeaders are used to create the headers for each sheet in the XLS. This functions
572
* is called whenever a new sheet is added (aside from the final definition sheet).
574
* @param sfPhpExcel object $objPHPExcel The sfPhpExcel object that is being
575
* populated by buildXls.
577
private function buildSheetHeaders($objPHPExcel)
579
foreach ($this->exportHeaders as $hKey => $heading) {
580
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($hKey, 1)->setValue($heading);
585
* This function constructs a Doctrine Query based on the values of the parameter passed in.
587
* The query will return the values from a single column of a table, with the possiblity to
588
* add a where clause to the query.
590
* @param $lookups array() gatherLookupValues expects $lookups to be a two-dimensional array.
591
* Keys of the outer level are expected to be column headers for a
592
* lookup column, or some other kind of organized data list. However,
593
* submitting a non-associative array will not cause any errors.
595
* The expected structure of the array is something like this:
598
* 'Staff Resource Status' => array(
599
* 'selectTable' => 'agStaffResourceStatus',
600
* 'selectColumn' => 'staff_resource_status',
601
* 'whereColumn' => null,
602
* 'whereValue' => null
604
* 'Staff Resource Type' => array(
605
* 'selectTable' => 'agStaffResourceType',
606
* 'selectColumn' => 'staff_resource_type',
607
* 'whereColumn' => null,
608
* 'whereValue' => null
612
* Additional values of the $lookUps array can also be included.
613
* The keys of the inner array musy be set to selectTable, selectColumn,
614
* whereColumn, and whereValue.
616
private function gatherLookupValues($lookUps = null)
618
foreach ($lookUps as $key => $lookUp) {
619
$lookUpQuery = agDoctrineQuery::create()
620
->select($lookUp['selectColumn'])
621
->from($lookUp['selectTable']);
622
if (isset($lookUp['whereColumn']) && isset($lookUp['whereValue'])) {
623
//$lookUpQuery->where("'" . $lookUp['whereColumn'] . " = ?', " . $lookUp['whereValue']);
624
$lookUpQuery->where($lookUp['whereColumn'] . " = " . $lookUp['whereValue']);
626
$returnedLookups[$key] = $lookUpQuery->execute(null, 'single_value_array');
628
return $returnedLookups;
b'\\ No newline at end of file'
added
removed
apps/frontend/lib/util/agStaffExporter.class.php
