$row_columns[$column] = $column_data;
unset($column, $column_data);
}
$rows[] = $row_columns;
unset($row_columns);
}
}
}
//var_dump($rows);
foreach ($filter_data['column_ids'] as $column_key) {
$filter_columns[Misc::trimSortPrefix($column_key)] = $columns[$column_key];
}
if ($action == 'export') {
if (isset($rows) and isset($filter_columns)) {
Debug::Text('Exporting as CSV', __FILE__, __LINE__, __METHOD__, 10);
$data = Misc::Array2CSV($rows, $filter_columns);
Misc::FileDownloadHeader('report.csv', 'application/csv', strlen($data));
echo $data;
} else {
echo TTi18n::gettext("No Data To Export!") . "<br>\n";
}
} else {
$smarty->assign_by_ref('generated_time', TTDate::getTime());
$smarty->assign_by_ref('pay_period_options', $pay_period_options);
$smarty->assign_by_ref('filter_data', $filter_data);
$smarty->assign_by_ref('columns', $filter_columns);
$smarty->assign_by_ref('rows', $rows);
$smarty->display('report/TimesheetSummaryReport.tpl');
}
break;
case 'delete':
$pd_obj = new PayrollDeduction($country, $province_code);
$pd_obj->setDate($effective_date);
$pd_obj->setAnnualPayPeriods($pay_periods);
$pd_obj->setEnableCPPAndEIDeduction(TRUE);
//Deduct CPP/EI.
$pd_obj->setFederalTotalClaimAmount($federal_claim);
$pd_obj->setProvincialTotalClaimAmount($provincial_claim);
$pd_obj->setEIExempt(FALSE);
$pd_obj->setCPPExempt(FALSE);
$pd_obj->setFederalTaxExempt(FALSE);
$pd_obj->setProvincialTaxExempt(FALSE);
$pd_obj->setYearToDateCPPContribution(0);
$pd_obj->setYearToDateEIContribution(0);
$pd_obj->setGrossPayPeriodIncome($income);
$retarr[] = array('country' => $country, 'province' => $province_code, 'date' => date('m/d/y', $effective_date), 'pay_periods' => $pay_periods, 'federal_claim' => $pd_obj->getFederalTotalClaimAmount(), 'provincial_claim' => $pd_obj->getProvincialTotalClaimAmount(), 'gross_income' => $income, 'federal_deduction' => Misc::MoneyFormat($pd_obj->getFederalPayPeriodDeductions(), FALSE), 'provincial_deduction' => Misc::MoneyFormat($pd_obj->getProvincialPayPeriodDeductions(), FALSE));
}
}
}
}
}
//generate column array.
$column_keys = array_keys($retarr[0]);
foreach ($column_keys as $column_key) {
$columns[$column_key] = $column_key;
}
//var_dump($test_data);
//var_dump($retarr);
echo Misc::Array2CSV($retarr, $columns, FALSE, $include_header = TRUE);
}
}
//Debug::Display();
//
//header
//
$data = 'TC' . "\n";
$data .= '00001' . "\n";
$export_column_map = array('pay_period_end_date' => 'Entry Date', 'employee_number' => 'Employee Number', 'last_name' => 'Last Name', 'first_name' => 'First Name', 'hour_code' => 'Payroll Code', 'value' => 'Hours');
foreach ($rows as $row) {
foreach ($setup_data['surepayroll']['columns'] as $column_id => $column_data) {
if (isset($row[$column_id]) and trim($column_data['hour_code']) != '') {
Debug::Arr($column_data, 'Output2', __FILE__, __LINE__, __METHOD__, 10);
$tmp_rows[] = array('pay_period_end_date' => date('m/d/Y', $row['pay_period_end_date']), 'employee_number' => $row['employee_number'], 'last_name' => $row['last_name'], 'first_name' => $row['first_name'], 'hour_code' => trim($column_data['hour_code']), 'value' => TTDate::getTimeUnit($row[$column_id], 20));
}
}
}
if (isset($tmp_rows)) {
$data .= Misc::Array2CSV($tmp_rows, $export_column_map, FALSE, FALSE);
$data = str_replace('"', '', $data);
}
unset($tmp_rows, $export_column_map, $column_id, $column_data, $rows, $row);
break;
default:
break;
}
}
}
if (Debug::getVerbosity() == 11) {
Debug::Arr($data, 'Output', __FILE__, __LINE__, __METHOD__, 10);
Debug::Display();
} else {
if (isset($data) and strlen($data) > 0) {
Debug::Text('Exporting as CSV', __FILE__, __LINE__, __METHOD__, 10);
function _outputPayrollExport($format = NULL)
{
$setup_data = $this->getFormConfig();
Debug::Text('Generating Payroll Export... Format: ' . $format, __FILE__, __LINE__, __METHOD__, 10);
if (isset($setup_data['export_type'])) {
Debug::Text('Export Type: ' . $setup_data['export_type'], __FILE__, __LINE__, __METHOD__, 10);
} else {
Debug::Text('No Export Type defined!', __FILE__, __LINE__, __METHOD__, 10);
return FALSE;
}
Debug::Arr($setup_data, 'Setup Data: ', __FILE__, __LINE__, __METHOD__, 10);
$rows = $this->data;
//Debug::Arr($rows, 'PreData: ', __FILE__, __LINE__, __METHOD__,10);
$file_name = strtolower(trim($setup_data['export_type'])) . '_' . date('Y_m_d') . '.txt';
$mime_type = 'application/text';
$data = NULL;
switch (strtolower(trim($setup_data['export_type']))) {
case 'adp':
//ADP export format.
//File format supports multiple rows per employee (file #) all using the same columns. No need to jump through nasty hoops to fit everything on row.
$export_column_map = array('company_code' => 'Co Code', 'batch_id' => 'Batch ID', 'temp_dept' => 'Temp Dept', 'employee_number' => 'File #', 'regular_time' => 'Reg Hours', 'overtime' => 'O/T Hours', '3_code' => 'Hours 3 Code', '3_amount' => 'Hours 3 Amount', '4_code' => 'Hours 4 Code', '4_amount' => 'Hours 4 Amount');
ksort($setup_data['adp']['columns']);
$setup_data['adp']['columns'] = Misc::trimSortPrefix($setup_data['adp']['columns']);
foreach ($setup_data['adp']['columns'] as $column_id => $column_data) {
$column_name = NULL;
if ($column_data['hour_column'] == 'regular_time') {
$export_data_map[$column_id] = 'regular_time';
} elseif ($column_data['hour_column'] == 'overtime') {
$export_data_map[$column_id] = 'overtime';
} elseif ($column_data['hour_column'] >= 3) {
$export_data_map[$column_id] = $column_data;
}
}
if (!isset($setup_data['adp']['company_code_value'])) {
$setup_data['adp']['company_code_value'] = NULL;
}
if (!isset($setup_data['adp']['batch_id_value'])) {
$setup_data['adp']['batch_id_value'] = NULL;
}
if (!isset($setup_data['adp']['temp_dept_value'])) {
$setup_data['adp']['temp_dept_value'] = NULL;
}
$company_code_column = Misc::trimSortPrefix($setup_data['adp']['company_code']);
$batch_id_column = Misc::trimSortPrefix($setup_data['adp']['batch_id']);
$temp_dept_column = Misc::trimSortPrefix($setup_data['adp']['temp_dept']);
foreach ($rows as $row) {
$static_columns = array('company_code' => isset($row[$company_code_column]) ? $row[$company_code_column] : $setup_data['adp']['company_code_value'], 'batch_id' => isset($row[$batch_id_column]) ? $row[$batch_id_column] : $setup_data['adp']['batch_id_value'], 'temp_dept' => isset($row[$temp_dept_column]) ? $row[$temp_dept_column] : $setup_data['adp']['temp_dept_value'], 'employee_number' => str_pad($row['employee_number'], 6, 0, STR_PAD_LEFT));
foreach ($setup_data['adp']['columns'] as $column_id => $column_data) {
$column_data = Misc::trimSortPrefix($column_data, TRUE);
Debug::Text('ADP Column ID: ' . $column_id . ' Hour Column: ' . $column_data['hour_column'] . ' Code: ' . $column_data['hour_code'], __FILE__, __LINE__, __METHOD__, 10);
if (isset($row[$column_id]) and $column_data['hour_column'] != '0') {
foreach ($export_column_map as $export_column_id => $export_column_name) {
Debug::Arr($row, 'Row: Column ID: ' . $column_id . ' Export Column ID: ' . $export_column_id . ' Name: ' . $export_column_name, __FILE__, __LINE__, __METHOD__, 10);
if (($column_data['hour_column'] == $export_column_id or $column_data['hour_column'] . '_code' == $export_column_id) and !in_array($export_column_id, array('company_code', 'batch_id', 'temp_dept', 'employee_number'))) {
if ((int) substr($export_column_id, 0, 1) > 0) {
$tmp_row[$column_data['hour_column'] . '_code'] = $column_data['hour_code'];
$tmp_row[$column_data['hour_column'] . '_amount'] = TTDate::getTimeUnit($row[$column_id], 20);
} else {
$tmp_row[$export_column_id] = TTDate::getTimeUnit($row[$column_id], 20);
}
//Break out every column onto its own row, that way its easier to handle multiple columns of the same type.
$tmp_rows[] = array_merge($static_columns, $tmp_row);
unset($tmp_row);
}
}
}
}
}
$file_name = 'EPI000000.csv';
if (isset($tmp_rows)) {
//File format supports multiple entries per employee (file #) all using the same columns. No need to jump through nasty hoops to fit everyone one row.
$file_name = 'EPI' . $tmp_rows[0]['company_code'] . $tmp_rows[0]['batch_id'] . '.csv';
$data = Misc::Array2CSV($tmp_rows, $export_column_map, FALSE);
}
unset($tmp_rows, $export_column_map, $column_id, $column_data, $rows, $row);
break;
case 'adp_old':
//ADP export format.
$file_name = 'EPI' . $setup_data['adp']['company_code'] . $setup_data['adp']['batch_id'] . '.csv';
$export_column_map = array();
$static_export_column_map = array('company_code' => 'Co Code', 'batch_id' => 'Batch ID', 'employee_number' => 'File #');
$static_export_data_map = array('company_code' => $setup_data['adp']['company_code'], 'batch_id' => $setup_data['adp']['batch_id']);
//
//Format allows for multiple duplicate columns.
//ie: Hours 3 Code, Hours 3 Amount, Hours 3 Code, Hours 3 Amount, ...
//However, we can only have a SINGLE O/T Hours column.
//We also need to combine hours with the same code together.
//
ksort($setup_data['adp']['columns']);
$setup_data['adp']['columns'] = Misc::trimSortPrefix($setup_data['adp']['columns']);
foreach ($setup_data['adp']['columns'] as $column_id => $column_data) {
$column_name = NULL;
if ($column_data['hour_column'] == 'regular_time') {
$column_name = 'Reg Hours';
$export_data_map[$column_id] = trim($setup_data['adp']['columns'][$column_id]['hour_code']);
} elseif ($column_data['hour_column'] == 'overtime') {
$column_name = 'O/T Hours';
$export_data_map[$column_id] = trim($setup_data['adp']['columns'][$column_id]['hour_code']);
} elseif ($column_data['hour_column'] >= 3) {
$column_name = 'Hours ' . $column_data['hour_column'] . ' Amount';
$export_column_map[$setup_data['adp']['columns'][$column_id]['hour_code'] . '_code'] = 'Hours ' . $column_data['hour_column'] . ' Code';
$export_data_map[$column_id] = trim($setup_data['adp']['columns'][$column_id]['hour_code']);
}
if ($column_name != '') {
$export_column_map[trim($setup_data['adp']['columns'][$column_id]['hour_code'])] = $column_name;
}
}
$export_column_map = Misc::prependArray($static_export_column_map, $export_column_map);
//
//Combine time from all columns with the same hours code.
//
$i = 0;
foreach ($rows as $row) {
foreach ($static_export_column_map as $column_id => $column_name) {
if (isset($static_export_data_map[$column_id])) {
//Copy over static config values like company code/batch_id.
$tmp_rows[$i][$column_id] = $static_export_data_map[$column_id];
} elseif (isset($row[$column_id])) {
if (isset($static_export_column_map[$column_id])) {
//Copy over employee_number. (File #)
$tmp_rows[$i][$column_id] = $row[$column_id];
}
}
}
foreach ($export_data_map as $column_id => $column_name) {
if (!isset($tmp_rows[$i][$column_name])) {
$tmp_rows[$i][$column_name] = 0;
}
if (isset($row[$column_id])) {
$tmp_rows[$i][$column_name] += $row[$column_id];
}
$tmp_rows[$i][$column_name . '_code'] = $column_name;
}
$i++;
}
//Convert time from seconds to hours.
$convert_unit_columns = array_keys($static_export_column_map);
foreach ($tmp_rows as $row => $data) {
foreach ($data as $column_id => $column_data) {
//var_dump($column_id,$column_data);
if (is_int($column_data) and !in_array($column_id, $convert_unit_columns)) {
$tmp_rows[$row][$column_id] = TTDate::getTimeUnit($column_data, 20);
}
}
}
unset($row, $data, $column_id, $column_data);
$data = Misc::Array2CSV($tmp_rows, $export_column_map, FALSE);
break;
case 'paychex_preview_advanced_job':
//PayChex Preview with job information
unset($rows);
//Ignore any existing timesheet summary data, we will be using our own job data below.
//Debug::Arr($setup_data, 'PayChex Advanced Job Setup Data: ', __FILE__, __LINE__, __METHOD__,10);
$config['columns'][] = 'employee_number';
$config['columns'][] = 'date_stamp';
$config['columns'] = array_merge($config['columns'], (array) $setup_data['paychex_preview_advanced_job']['job_columns']);
$config['columns'][] = $setup_data['paychex_preview_advanced_job']['state_columns'];
$config['columns'] += array_keys(Misc::trimSortPrefix($this->getOptions('dynamic_columns')));
$config['group'][] = 'employee_number';
$config['group'][] = 'date_stamp';
$config['group'] = array_merge($config['columns'], (array) $setup_data['paychex_preview_advanced_job']['job_columns']);
$config['group'][] = $setup_data['paychex_preview_advanced_job']['state_columns'];
$config['sort'][] = array('employee_number' => 'asc');
$config['sort'][] = array('date_stamp' => 'asc');
//Debug::Arr($config, 'Job Detail Report Config: ', __FILE__, __LINE__, __METHOD__,10);
//Get job data...
$jar = TTNew('JobDetailReport');
$jar->setAMFMessageID($this->getAMFMessageID());
$jar->setUserObject($this->getUserObject());
$jar->setPermissionObject($this->getPermissionObject());
$jar->setConfig($config);
$jar->setFilterConfig($this->getFilterConfig());
$jar->setSortConfig($config['sort']);
$jar->_getData();
$jar->_preProcess();
$jar->sort();
$rows = $jar->data;
//Debug::Arr($rows, 'Raw Rows: ', __FILE__, __LINE__, __METHOD__,10);
//Need to get job data from job report instead of TimeSheet Summary report.
if (!isset($setup_data['paychex_preview_advanced_job']['client_number'])) {
$setup_data['paychex_preview_advanced_job']['client_number'] = '0000';
}
$file_name = $setup_data['paychex_preview_advanced_job']['client_number'] . '_TA.txt';
ksort($setup_data['paychex_preview_advanced_job']['columns']);
$setup_data['paychex_preview_advanced_job']['columns'] = Misc::trimSortPrefix($setup_data['paychex_preview_advanced_job']['columns']);
$data = NULL;
foreach ($rows as $row) {
foreach ($setup_data['paychex_preview_advanced_job']['columns'] as $column_id => $column_data) {
if (isset($row[$column_id]) and trim($column_data['hour_code']) != '') {
$data .= str_pad($row['employee_number'], 6, ' ', STR_PAD_LEFT);
$data .= str_pad('', 31, ' ', STR_PAD_LEFT);
//Blank space.
if (isset($setup_data['paychex_preview_advanced_job']['job_columns']) and is_array($setup_data['paychex_preview_advanced_job']['job_columns'])) {
$job_column = array();
foreach ($setup_data['paychex_preview_advanced_job']['job_columns'] as $tmp_job_column) {
$job_column[] = isset($row[$tmp_job_column]) ? $row[$tmp_job_column] : NULL;
}
$data .= str_pad(substr(implode('-', $job_column), 0, 12), 12, ' ', STR_PAD_LEFT);
unset($job_column);
} else {
$data .= str_pad('', 12, ' ', STR_PAD_LEFT);
}
$data .= str_pad('', 1, ' ', STR_PAD_LEFT);
//Shift identifier.
//Allow user to specify three digit hour codes to specify their own E/D codes. If codes are two digit, always use E.
if (strlen(trim($column_data['hour_code'])) < 3) {
$column_data['hour_code'] = 'E' . trim($column_data['hour_code']);
}
//Should start at col51
$data .= str_pad(substr(trim($column_data['hour_code']), 0, 3), 3, ' ', STR_PAD_RIGHT);
if (isset($setup_data['paychex_preview_advanced_job']['include_hourly_rate']) and $setup_data['paychex_preview_advanced_job']['include_hourly_rate'] == TRUE) {
$data .= str_pad(isset($row[$column_id . '_hourly_rate']) ? number_format($row[$column_id . '_hourly_rate'], 4, '.', '') : NULL, 9, 0, STR_PAD_LEFT);
//Override rate
} else {
$data .= str_pad('', 9, 0, STR_PAD_LEFT);
//Override rate
}
$data .= str_pad(TTDate::getTimeUnit($row[$column_id], 20), 8, 0, STR_PAD_LEFT);
//Break out time by day.
$data .= str_pad(TTDate::getYear($row['time_stamp']), 4, 0, STR_PAD_LEFT);
//Year, based on time_stamp epoch column
$data .= str_pad(TTDate::getMonth($row['time_stamp']), 2, 0, STR_PAD_LEFT);
//Month, based on time_stamp epoch column. Can be space padded.
$data .= str_pad(TTDate::getDayOfMonth($row['time_stamp']), 2, 0, STR_PAD_LEFT);
//Day, based on time_stamp epoch column. Can be space padded.
$data .= str_pad('', 4, ' ', STR_PAD_LEFT);
//Filler
$data .= str_pad('', 9, ' ', STR_PAD_LEFT);
//Amount. This can always be calculated from hours and hourly rate above though.
$data .= str_pad('', 13, ' ', STR_PAD_LEFT);
//Blank space
if (isset($setup_data['paychex_preview_advanced_job']['state_columns'])) {
$data .= str_pad(isset($row[$setup_data['paychex_preview_advanced_job']['state_columns']]) ? $row[$setup_data['paychex_preview_advanced_job']['state_columns']] : NULL, 2, ' ', STR_PAD_LEFT);
//State
}
$data .= "\n";
}
}
}
break;
case 'paychex_preview':
//Paychex Preview export format.
//Add an advanced PayChex Preview format that supports rates perhaps?
//http://kb.idb-sys.com/KnowledgebaseArticle10013.aspx
if (!isset($setup_data['paychex_preview']['client_number'])) {
$setup_data['paychex_preview']['client_number'] = '0000';
}
$file_name = $setup_data['paychex_preview']['client_number'] . '_TA.txt';
ksort($setup_data['paychex_preview']['columns']);
$setup_data['paychex_preview']['columns'] = Misc::trimSortPrefix($setup_data['paychex_preview']['columns']);
$data = NULL;
foreach ($rows as $row) {
foreach ($setup_data['paychex_preview']['columns'] as $column_id => $column_data) {
if (isset($row[$column_id]) and trim($column_data['hour_code']) != '') {
$data .= str_pad($row['employee_number'], 6, ' ', STR_PAD_LEFT);
$data .= str_pad('E' . str_pad(trim($column_data['hour_code']), 2, ' ', STR_PAD_RIGHT), 47, ' ', STR_PAD_LEFT);
$data .= str_pad(str_pad(TTDate::getTimeUnit($row[$column_id], 20), 8, 0, STR_PAD_LEFT), 17, ' ', STR_PAD_LEFT) . "\n";
}
}
}
break;
case 'paychex_online':
//Paychex Online Payroll CSV
ksort($setup_data['paychex_online']['columns']);
$setup_data['paychex_online']['columns'] = Misc::trimSortPrefix($setup_data['paychex_online']['columns']);
$earnings = array();
//Find all the hours codes
foreach ($setup_data['paychex_online']['columns'] as $column_id => $column_data) {
$hour_code = $column_data['hour_code'];
$earnings[] = $hour_code;
}
$export_column_map['employee_number'] = '';
foreach ($earnings as $key => $value) {
$export_column_map[$value] = '';
}
$i = 0;
foreach ($rows as $row) {
if ($i == 0) {
//Include header.
$tmp_row['employee_number'] = 'Employee Number';
foreach ($earnings as $key => $value) {
$tmp_row[$value] = $value . ' Hours';
}
$tmp_rows[] = $tmp_row;
unset($tmp_row);
}
//Combine all hours from the same code together.
foreach ($setup_data['paychex_online']['columns'] as $column_id => $column_data) {
$hour_code = trim($column_data['hour_code']);
if (isset($row[$column_id]) and $hour_code != '') {
if (!isset($tmp_hour_codes[$hour_code])) {
$tmp_hour_codes[$hour_code] = 0;
}
$tmp_hour_codes[$hour_code] = bcadd($tmp_hour_codes[$column_data['hour_code']], $row[$column_id]);
//Use seconds for math here.
}
}
if (isset($tmp_hour_codes)) {
$tmp_row['employee_number'] = $row['employee_number'];
foreach ($tmp_hour_codes as $hour_code => $hours) {
$tmp_row[$hour_code] = TTDate::getTimeUnit($hours, 20);
}
$tmp_rows[] = $tmp_row;
unset($tmp_hour_codes, $hour_code, $hours, $tmp_row);
}
$i++;
}
if (isset($tmp_rows)) {
$data = Misc::Array2CSV($tmp_rows, $export_column_map, FALSE, FALSE);
}
unset($tmp_rows, $export_column_map, $column_id, $column_data, $rows, $row);
break;
case 'millenium':
//Millenium export format. Also used by Qqest.
ksort($setup_data['millenium']['columns']);
$setup_data['millenium']['columns'] = Misc::trimSortPrefix($setup_data['millenium']['columns']);
$export_column_map = array('employee_number' => '', 'transaction_code' => '', 'hour_code' => '', 'hours' => '');
foreach ($rows as $row) {
foreach ($setup_data['millenium']['columns'] as $column_id => $column_data) {
if (isset($row[$column_id]) and trim($column_data['hour_code']) != '') {
$tmp_rows[] = array('employee_number' => $row['employee_number'], 'transaction_code' => 'E', 'hour_code' => trim($column_data['hour_code']), 'hours' => TTDate::getTimeUnit($row[$column_id], 20));
}
}
}
if (isset($tmp_rows)) {
$data = Misc::Array2CSV($tmp_rows, $export_column_map, FALSE, FALSE);
}
unset($tmp_rows, $export_column_map, $column_id, $column_data, $rows, $row);
break;
case 'ceridian_insync':
//Ceridian InSync export format. Needs to be .IMP to import? DOS line endings?
if (!isset($setup_data['ceridian_insync']['employer_number']) or $setup_data['ceridian_insync']['employer_number'] == '') {
$setup_data['ceridian_insync']['employer_number'] = '0001';
}
$file_name = strtolower(trim($setup_data['export_type'])) . '_' . $setup_data['ceridian_insync']['employer_number'] . '_' . date('Y_m_d') . '.imp';
ksort($setup_data['ceridian_insync']['columns']);
$setup_data['ceridian_insync']['columns'] = Misc::trimSortPrefix($setup_data['ceridian_insync']['columns']);
$export_column_map = array('employer_number' => '', 'import_type_id' => '', 'employee_number' => '', 'check_type' => '', 'hour_code' => '', 'value' => '', 'distribution' => '', 'rate' => '', 'premium' => '', 'day' => '', 'pay_period' => '');
foreach ($rows as $row) {
foreach ($setup_data['ceridian_insync']['columns'] as $column_id => $column_data) {
if (isset($row[$column_id]) and trim($column_data['hour_code']) != '') {
$tmp_rows[] = array('employer_number' => $setup_data['ceridian_insync']['employer_number'], 'import_type_id' => 'COSTING', 'employee_number' => str_pad($row['employee_number'], 9, '0', STR_PAD_LEFT), 'check_type' => 'REG', 'hour_code' => trim($column_data['hour_code']), 'value' => TTDate::getTimeUnit($row[$column_id], 20), 'distribution' => NULL, 'rate' => NULL, 'premium' => NULL, 'day' => NULL, 'pay_period' => NULL);
}
}
}
if (isset($tmp_rows)) {
$data = Misc::Array2CSV($tmp_rows, $export_column_map, FALSE, FALSE, "\r\n");
//Use DOS line endings only.
}
unset($tmp_rows, $export_column_map, $column_id, $column_data, $rows, $row);
break;
case 'quickbooks':
//Quickbooks Pro export format.
//Quickbooks Pro export format.
case 'quickbooks_advanced':
//Quickbooks Pro export format.
$file_name = 'payroll_export.iif';
ksort($setup_data['quickbooks']['columns']);
$setup_data['quickbooks']['columns'] = Misc::trimSortPrefix($setup_data['quickbooks']['columns']);
//
// Quickbooks header
//
/*
Company Create Time can be found by first running an Timer Activity export in QuickBooks and viewing the output.
PITEM field needs to be populated, as that is the PAYROLL ITEM in quickbooks. It can be the same as the ITEM field.
ITEM is the service item, can be mapped to department/task?
PROJ could be mapped to the default department/branch?
*/
$data = "!TIMERHDR\tVER\tREL\tCOMPANYNAME\tIMPORTEDBEFORE\tFROMTIMER\tCOMPANYCREATETIME\n";
$data .= "TIMERHDR\t8\t0\t" . trim($setup_data['quickbooks']['company_name']) . "\tN\tY\t" . trim($setup_data['quickbooks']['company_created_date']) . "\n";
$data .= "!TIMEACT\tDATE\tJOB\tEMP\tITEM\tPITEM\tDURATION\tPROJ\tNOTE\tXFERTOPAYROLL\tBILLINGSTATUS\n";
foreach ($rows as $row) {
foreach ($setup_data['quickbooks']['columns'] as $column_id => $column_data) {
if (isset($row[$column_id]) and trim($column_data['hour_code']) != '') {
//Make sure employee name is in format: LastName, FirstName MiddleInitial
$tmp_employee_name = $row['last_name'] . ', ' . $row['first_name'];
if (isset($row['middle_name']) and strlen($row['middle_name']) > 0) {
$tmp_employee_name .= ' ' . substr(trim($row['middle_name']), 0, 1);
}
$proj = NULL;
if (isset($row[$setup_data['quickbooks']['proj']])) {
$proj = $row[$setup_data['quickbooks']['proj']];
}
$item = NULL;
if (isset($row[$setup_data['quickbooks']['item']])) {
$item = $row[$setup_data['quickbooks']['item']];
}
$job = NULL;
if (isset($row[$setup_data['quickbooks']['job']])) {
$job = $row[$setup_data['quickbooks']['job']];
}
$data .= "TIMEACT\t" . date('n/j/y', $row['pay_period_end_date']) . "\t" . $job . "\t" . $tmp_employee_name . "\t" . $item . "\t" . trim($column_data['hour_code']) . "\t" . TTDate::getTimeUnit($row[$column_id], 10) . "\t" . $proj . "\t\tY\t0\n";
unset($tmp_employee_name);
}
}
}
break;
case 'surepayroll':
//SurePayroll Export format.
ksort($setup_data['surepayroll']['columns']);
$setup_data['surepayroll']['columns'] = Misc::trimSortPrefix($setup_data['surepayroll']['columns']);
//
//header
//
$data = 'TC' . "\n";
$data .= '00001' . "\n";
$export_column_map = array('pay_period_end_date' => 'Entry Date', 'employee_number' => 'Employee Number', 'last_name' => 'Last Name', 'first_name' => 'First Name', 'hour_code' => 'Payroll Code', 'value' => 'Hours');
foreach ($rows as $row) {
foreach ($setup_data['surepayroll']['columns'] as $column_id => $column_data) {
if (isset($row[$column_id]) and trim($column_data['hour_code']) != '') {
//Debug::Arr($column_data,'Output2', __FILE__, __LINE__, __METHOD__,10);
$tmp_rows[] = array('pay_period_end_date' => date('m/d/Y', $row['pay_period_end_date']), 'employee_number' => $row['employee_number'], 'last_name' => $row['last_name'], 'first_name' => $row['first_name'], 'hour_code' => trim($column_data['hour_code']), 'value' => TTDate::getTimeUnit($row[$column_id], 20));
}
}
}
if (isset($tmp_rows)) {
$data .= Misc::Array2CSV($tmp_rows, $export_column_map, FALSE, FALSE);
$data = str_replace('"', '', $data);
}
unset($tmp_rows, $export_column_map, $column_id, $column_data, $rows, $row);
break;
case 'chris21':
//Chris21 Export format.
//Columns required: Employee_number (2), Date (10), ADJUSTMENT_CODE (12), HOURS (13), SIGNED_HOURS(15)[?]
//Use SIGNED_HOURS only, as it provides more space?
//When using absences a leave start/end date must be specified other it won't be imported.
ksort($setup_data['chris21']['columns']);
$setup_data['chris21']['columns'] = Misc::trimSortPrefix($setup_data['chris21']['columns']);
$data = '';
foreach ($rows as $row) {
foreach ($setup_data['chris21']['columns'] as $column_id => $column_data) {
if (isset($row[$column_id]) and trim($column_data['hour_code']) != '') {
//Debug::Arr($column_data,'Output2: ID: '. $column_id, __FILE__, __LINE__, __METHOD__,10);
$data .= str_repeat(' ', 8);
//8 digits Blank
$data .= str_pad(substr($row['employee_number'], 0, 7), 7, ' ', STR_PAD_RIGHT);
//7 digits
$data .= str_repeat(' ', 11);
//14 digits Blank
$data .= date('dmy', $row['pay_period_end_date']);
//4 digits Date
$data .= str_repeat(' ', 4);
//4 digits Blank
$data .= str_pad(substr(trim($column_data['hour_code']), 0, 4), 4, ' ', STR_PAD_RIGHT);
//4 digits
$data .= '0000';
//4 digits HOURS field, always be 0, use SIGNED_HOURS instead.
$data .= str_repeat(' ', 4);
//CC_CODE: 4 digits Blank
$data .= str_pad(str_replace('.', '', TTDate::getTimeUnit($row[$column_id], 20)), 6, 0, STR_PAD_LEFT) . '+';
//SIGNED_HOURS: Hours without decimal padded to 6 digits, with '+' on the end.
//$data .= '+000000000'; //Filler: Redefintion of SIGNED_HOURS.
$data .= '000000000';
//RATE: 9 chars
$data .= str_repeat(' ', 20);
//ACCT_NO: 20 chars
$data .= str_repeat(' ', 16);
//JOB_NUMBER: 16 chars
if (strpos($column_id, 'absence') !== FALSE) {
//Absence column, include LEAVE dates.
$data .= date('dmy', $row['pay_period_end_date']);
//LEAVE Start Date: 6 digits
$data .= date('dmy', $row['pay_period_end_date']);
//LEAVE End Date: 6 digits
}
$data .= "\n";
}
}
}
unset($tmp_rows, $column_id, $column_data, $rows, $row);
break;
case 'csv':
//Generic CSV.
$file_name = strtolower(trim($setup_data['export_type'])) . '_' . date('Y_m_d') . '.csv';
//If this needs to be customized, they can just export any regular report. This could probably be removed completely except for the Hour Code mapping...
ksort($setup_data['csv']['columns']);
$setup_data['csv']['columns'] = Misc::trimSortPrefix($setup_data['csv']['columns']);
$export_column_map = array('employee' => '', 'employee_number' => '', 'default_branch' => '', 'default_department' => '', 'pay_period' => '', 'branch_name' => '', 'department_name' => '', 'hour_code' => '', 'hours' => '');
$i = 0;
foreach ($rows as $row) {
if ($i == 0) {
//Include header.
$tmp_rows[] = array('employee' => 'Employee', 'employee_number' => 'Employee Number', 'default_branch' => 'Default Branch', 'default_department' => 'Default Department', 'pay_period' => 'Pay Period', 'branch_name' => 'Branch', 'department_name' => 'Department', 'hour_code' => 'Hours Code', 'hours' => 'Hours');
}
//Combine all hours from the same code together.
foreach ($setup_data['csv']['columns'] as $column_id => $column_data) {
$hour_code = trim($column_data['hour_code']);
if (isset($row[$column_id]) and $hour_code != '') {
if (!isset($tmp_hour_codes[$hour_code])) {
$tmp_hour_codes[$hour_code] = 0;
}
$tmp_hour_codes[$hour_code] = bcadd($tmp_hour_codes[$column_data['hour_code']], $row[$column_id]);
//Use seconds for math here.
}
}
if (isset($tmp_hour_codes)) {
foreach ($tmp_hour_codes as $hour_code => $hours) {
$tmp_rows[] = array('employee' => isset($row['full_name']) ? $row['full_name'] : NULL, 'employee_number' => isset($row['employee_number']) ? $row['employee_number'] : NULL, 'default_branch' => isset($row['default_branch']) ? $row['default_branch'] : NULL, 'default_department' => isset($row['default_department']) ? $row['default_department'] : NULL, 'pay_period' => isset($row['pay_period']['display']) ? $row['pay_period']['display'] : NULL, 'branch_name' => isset($row['branch_name']) ? $row['branch_name'] : NULL, 'department_name' => isset($row['department_name']) ? $row['department_name'] : NULL, 'hour_code' => $hour_code, 'hours' => TTDate::getTimeUnit($hours, 20));
}
unset($tmp_hour_codes, $hour_code, $hours);
}
$i++;
}
if (isset($tmp_rows)) {
$data = Misc::Array2CSV($tmp_rows, $export_column_map, FALSE, FALSE);
}
unset($tmp_rows, $export_column_map, $column_id, $column_data, $rows, $row);
break;
case 'csv_advanced':
//Generic CSV.
unset($rows);
//Ignore any existing timesheet summary data, we will be using our own job data below.
//If this needs to be customized, they can just export any regular report. This could probably be removed completely except for the Hour Code mapping...
if (!isset($setup_data['csv_advanced']['export_columns']) or isset($setup_data['csv_advanced']['export_columns']) and !is_array($setup_data['csv_advanced']['export_columns'])) {
$setup_data['csv_advanced']['export_columns'] = array('full_name', 'employee_number', 'default_branch', 'default_department', 'pay_period', 'date_stamp');
}
if (isset($setup_data['csv_advanced']['export_columns']) and is_array($setup_data['csv_advanced']['export_columns'])) {
//Debug::Arr($setup_data['csv_advanced']['export_columns'], 'Custom Columns defined: ', __FILE__, __LINE__, __METHOD__,10);
$config['columns'] = $config['group'] = $setup_data['csv_advanced']['export_columns'];
//Force sorting...
foreach ($setup_data['csv_advanced']['export_columns'] as $export_column) {
$config['sort'][] = array($export_column => 'asc');
}
$config['columns'] += array_keys(Misc::trimSortPrefix($this->getOptions('dynamic_columns')));
}
Debug::Arr($config, 'Job Detail Report Config: ', __FILE__, __LINE__, __METHOD__, 10);
//Get job data...
if (is_object($this->getUserObject()) and is_object($this->getUserObject()->getCompanyObject()) and $this->getUserObject()->getCompanyObject()->getProductEdition() >= TT_PRODUCT_CORPORATE) {
Debug::Text('Using Job Detail Report...', __FILE__, __LINE__, __METHOD__, 10);
$jar = TTNew('JobDetailReport');
} else {
Debug::Text('Using TimeSheet Detail Report...', __FILE__, __LINE__, __METHOD__, 10);
$jar = TTNew('TimesheetDetailReport');
}
$jar->setAMFMessageID($this->getAMFMessageID());
$jar->setUserObject($this->getUserObject());
$jar->setPermissionObject($this->getPermissionObject());
$jar->setConfig($config);
$jar->setFilterConfig($this->getFilterConfig());
$jar->setSortConfig($config['sort']);
$jar->_getData();
$jar->_preProcess();
$jar->group();
$jar->sort();
$columns = Misc::trimSortPrefix($jar->getOptions('columns'));
$rows = $jar->data;
//Debug::Arr($rows, 'Raw Rows: ', __FILE__, __LINE__, __METHOD__,10);
$file_name = strtolower(trim($setup_data['export_type'])) . '_' . date('Y_m_d') . '.csv';
//If this needs to be customized, they can just export any regular report. This could probably be removed completely except for the Hour Code mapping...
ksort($setup_data['csv_advanced']['columns']);
$setup_data['csv_advanced']['columns'] = Misc::trimSortPrefix($setup_data['csv_advanced']['columns']);
foreach ($setup_data['csv_advanced']['export_columns'] as $export_column) {
$export_column_map[$export_column] = '';
}
$export_column_map['hour_code'] = '';
$export_column_map['hours'] = '';
$i = 0;
foreach ($rows as $row) {
if ($i == 0) {
//Include header.
foreach ($setup_data['csv_advanced']['export_columns'] as $export_column) {
Debug::Text('Header Row: ' . $export_column, __FILE__, __LINE__, __METHOD__, 10);
$tmp_rows[$i][$export_column] = isset($columns[$export_column]) ? $columns[$export_column] : NULL;
}
$tmp_rows[$i]['hour_code'] = 'Hours Code';
$tmp_rows[$i]['hours'] = 'Hours';
$i++;
}
//Combine all hours from the same code together.
foreach ($setup_data['csv_advanced']['columns'] as $column_id => $column_data) {
$hour_code = trim($column_data['hour_code']);
if (isset($row[$column_id]) and $hour_code != '') {
if (!isset($tmp_hour_codes[$hour_code])) {
$tmp_hour_codes[$hour_code] = 0;
}
$tmp_hour_codes[$hour_code] = bcadd($tmp_hour_codes[$column_data['hour_code']], $row[$column_id]);
//Use seconds for math here.
}
}
if (isset($tmp_hour_codes)) {
foreach ($tmp_hour_codes as $hour_code => $hours) {
foreach ($setup_data['csv_advanced']['export_columns'] as $export_column) {
$tmp_rows[$i][$export_column] = isset($row[$export_column]) ? isset($row[$export_column]['display']) ? $row[$export_column]['display'] : $row[$export_column] : NULL;
$tmp_rows[$i]['hour_code'] = $hour_code;
$tmp_rows[$i]['hours'] = TTDate::getTimeUnit($hours, 20);
}
}
unset($tmp_hour_codes, $hour_code, $hours);
}
$i++;
}
//Debug::Arr($tmp_rows, 'Tmp Rows: ', __FILE__, __LINE__, __METHOD__,10);
if (isset($tmp_rows)) {
$data = Misc::Array2CSV($tmp_rows, $export_column_map, FALSE, FALSE);
}
unset($tmp_rows, $export_column_map, $column_id, $column_data, $rows, $row);
break;
default:
//Send raw data so plugin can capture it and change it if needed.
$data = $this->data;
break;
}
//Debug::Arr($data, 'Export Data: ', __FILE__, __LINE__, __METHOD__,10);
return array('file_name' => $file_name, 'mime_type' => $mime_type, 'data' => $data);
}
function _output($format = NULL)
{
Debug::Text('Format: ' . $format, __FILE__, __LINE__, __METHOD__, 10);
if ($format == 'raw') {
//Should we rekey this array so the order can be presevered, which is critical to outputting it properly?
return $this->data;
} elseif ($format == 'csv' or $format == 'xml') {
//Make sure we use the full readable column name when exporting to CSV.
$column_options = (array) Misc::trimSortPrefix($this->getOptions('columns'));
$column_config = (array) $this->getReportColumns();
$columns = array();
foreach ($column_config as $column => $tmp) {
if (isset($column_options[$column])) {
$columns[$column] = $column_options[$column];
}
}
//Debug::Arr($columns, 'Columns: '. $format, __FILE__, __LINE__, __METHOD__,10);
if ($format == 'csv') {
$data = Misc::Array2CSV($this->data, $columns, FALSE, TRUE);
$file_extension = 'csv';
} elseif ($format == 'xml') {
//Include report name with non-alphanumerics stripped out.
$data = Misc::Array2XML($this->data, $columns, $this->getColumnFormatConfig(), FALSE, FALSE, preg_replace('/[^A-Za-z0-9]/', '', $this->config['other']['report_name']), 'row');
$file_extension = 'xml';
}
return array('data' => $data, 'file_name' => $this->file_name . '_' . date('Y_m_d') . '.' . $file_extension, 'mime_type' => 'text/' . $file_extension);
} else {
Debug::Text('Exporting PDF format: ' . $format, __FILE__, __LINE__, __METHOD__, 10);
return $this->_pdf();
}
return FALSE;
}
