function ToDate($vValue)
{
$oDate = new DCLDate();
// Set to 0 to invalidate by default. If parsing doesn't succeed, it will remain 0
$oDate->time = 0;
$oDate->SetFromDisplay($vValue);
return $oDate->ToDisplay();
}
function ShowReport($query, $iGroupColumn)
{
$bExport = isset($_REQUEST['export']) && $_REQUEST['export'] == '1';
$objS = CreateObject('dcl.dbStatuses');
$objPr = CreateObject('dcl.dbPriorities');
$objSe = CreateObject('dcl.dbSeverities');
$objW = CreateObject('dcl.dbWorkorders');
$oPM = CreateObject('dcl.dbProjectmap');
$objDB = new dclDB();
$aGroupOptions = array('1' => 'Project', '2' => 'Action', '3' => 'Date', '4' => 'Product', '5' => 'by');
$groupBy = $_REQUEST['groupby'];
if (!array_key_exists($groupBy, $aGroupOptions)) {
$groupBy = '0';
}
$oMeta =& CreateObject('dcl.DCL_MetadataDisplay');
$responsible = 0;
$department = 0;
if ($_REQUEST['bytype'] == '1') {
if (($responsible = DCL_Sanitize::ToInt($_REQUEST['responsible'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$sReportFor = $oMeta->GetPersonnel($responsible);
} else {
if (($department = DCL_Sanitize::ToInt($_REQUEST['department'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$sReportFor = $oMeta->GetDepartment($department);
}
if (($begindate = DCL_Sanitize::ToDate($_REQUEST['begindate'])) === null || ($enddate = DCL_Sanitize::ToDate($_REQUEST['enddate'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
if ($objDB->Query($query) != -1) {
if ($objDB->next_record()) {
$lastJCN = 0;
$lastSeq = 0;
$lastGroup = $thisGroup = '<< undefined >>';
$arrayIndex = -1;
$count = 0;
$subEstHours = 0.0;
$subAppliedHours = 0.0;
$subEtcHours = 0.0;
$subTimeHours = 0.0;
$totalEstHours = 0.0;
$totalAppliedHours = 0.0;
$totalEtcHours = 0.0;
$totalTimeHours = 0.0;
$oDate = new DCLDate();
$aByDate = array();
do {
$thisJCN = $objDB->f('jcn');
$thisSeq = $objDB->f('seq');
if ($groupBy == '3') {
$oDate->SetFromDB($objDB->f('actionon'));
$thisGroup = $oDate->ToDisplay();
} else {
if ($groupBy == '5') {
$thisGroup = $objDB->f('short');
} else {
if ($groupBy != '0') {
if ($objDB->IsFieldNull('name')) {
$thisGroup = ' ';
} else {
$thisGroup = $objDB->f('name');
}
}
}
}
// Skip multiple time cards
if ($thisJCN != $lastJCN || $thisSeq != $lastSeq || $thisGroup != $lastGroup) {
if ($groupBy != '0' && $thisGroup != $lastGroup && $lastGroup != '<< undefined >>') {
// Subtotals
$arrayIndex++;
if ($bExport) {
$reportArray[$arrayIndex][0] = 'Subtotal for ' . $lastGroup;
} else {
$reportArray[$arrayIndex][0] = '<b>Subtotal for ' . $lastGroup . '</b>';
}
if ($groupBy != '1') {
$reportArray[$arrayIndex][] = '';
}
if ($_REQUEST['bytype'] == '2') {
if ($groupBy == '5') {
$reportArray[$arrayIndex][] = $lastGroup;
} else {
$reportArray[$arrayIndex][] = '';
}
}
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $subEstHours;
$reportArray[$arrayIndex][] = $subEtcHours;
$reportArray[$arrayIndex][] = $subAppliedHours;
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $subTimeHours;
$ouHours = -($subEstHours - $subAppliedHours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($subEstHours > 0) {
$ouPct = $diffHours / $subEstHours * 100;
if ($subEstHours > $subAppliedHours && $subEstHours > 0) {
$sign = '-';
} else {
if ($subAppliedHours > $subEstHours && $subAppliedHours > 0) {
$sign = '+';
}
}
}
if ($bExport) {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
} else {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
}
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$reportArray[$arrayIndex][] = $lastGroup;
}
$subEstHours = 0.0;
$subAppliedHours = 0.0;
$subEtcHours = 0.0;
$subTimeHours = 0.0;
}
$arrayIndex++;
$objW->Load($thisJCN, $thisSeq);
$objS->Load($objW->status);
$objPr->Load($objW->priority);
$objSe->Load($objW->severity);
if ($bExport) {
$reportArray[$arrayIndex][0] = '[' . $thisJCN . '-' . $thisSeq . '] ' . $objW->summary;
} else {
$reportArray[$arrayIndex][0] = '[<a href="main.php?menuAction=boWorkorders.viewjcn&jcn=' . $thisJCN . '&seq=' . $thisSeq . '">' . $thisJCN . '-' . $thisSeq . '</a>] ' . htmlentities($objW->summary);
}
if ($groupBy != '1') {
if ($oPM->LoadByWO($thisJCN, $thisSeq) != -1) {
if ($bExport) {
$reportArray[$arrayIndex][] = '[' . $oPM->projectid . '] ' . $oMeta->GetProject($oPM->projectid);
} else {
$reportArray[$arrayIndex][] = '[<a href="main.php?menuAction=boProjects.viewproject&project=' . $oPM->projectid . '">' . $oPM->projectid . '</a>] ' . htmlentities($oMeta->GetProject($oPM->projectid));
}
} else {
$reportArray[$arrayIndex][] = '';
}
}
if ($_REQUEST['bytype'] == '2') {
$reportArray[$arrayIndex][] = $objDB->f('short');
}
$reportArray[$arrayIndex][] = $objS->name;
$reportArray[$arrayIndex][] = $objPr->name;
$reportArray[$arrayIndex][] = $objSe->name;
$reportArray[$arrayIndex][] = (double) $objW->esthours;
$reportArray[$arrayIndex][] = (double) $objW->etchours;
$reportArray[$arrayIndex][] = (double) $objW->totalhours;
$reportArray[$arrayIndex][] = $objW->eststarton;
$reportArray[$arrayIndex][] = $objW->estendon;
$reportArray[$arrayIndex][] = $objW->starton;
$reportArray[$arrayIndex][] = $objW->closedon;
$reportArray[$arrayIndex][] = (double) $objDB->f('hours');
$ouHours = -($objW->esthours - $objW->totalhours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($objW->esthours > 0) {
$ouPct = $diffHours / $objW->esthours * 100;
if ($objW->esthours > $objW->totalhours && $objW->esthours > 0) {
$sign = '-';
} else {
if ($objW->totalhours > $objW->esthours && $objW->totalhours > 0) {
$sign = '+';
}
}
}
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$reportArray[$arrayIndex][] = $thisGroup;
}
$sKey = sprintf('%d-%d', $thisJCN, $thisSeq);
if ($groupBy != '3' || !isset($aByDate[$sKey])) {
$subEstHours += (double) $objW->esthours;
$subAppliedHours += (double) $objW->totalhours;
$subEtcHours += (double) $objW->etchours;
$totalEstHours += (double) $objW->esthours;
$totalAppliedHours += (double) $objW->totalhours;
$totalEtcHours += (double) $objW->etchours;
$aByDate[$sKey] = true;
}
$lastJCN = $thisJCN;
$lastSeq = $thisSeq;
$lastGroup = $thisGroup;
} else {
$iOrdinal = 11;
if ($groupBy != '1') {
$iOrdinal++;
}
if ($_REQUEST['bytype'] == '2') {
$iOrdinal++;
}
$reportArray[$arrayIndex][$iOrdinal] += (double) $objDB->f('hours');
}
$subTimeHours += $objDB->f('hours');
$totalTimeHours += $objDB->f('hours');
$count++;
} while ($objDB->next_record());
// Subtotals
$arrayIndex++;
if ($bExport) {
$reportArray[$arrayIndex][0] = 'Subtotal for ' . $lastGroup;
} else {
$reportArray[$arrayIndex][0] = '<b>Subtotal for ' . $lastGroup . '</b>';
}
if ($groupBy != '1') {
$reportArray[$arrayIndex][] = '';
}
if ($_REQUEST['bytype'] == '2') {
if ($groupBy == '5') {
$reportArray[$arrayIndex][] = $lastGroup;
} else {
$reportArray[$arrayIndex][] = '';
}
}
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $subEstHours;
$reportArray[$arrayIndex][] = $subEtcHours;
$reportArray[$arrayIndex][] = $subAppliedHours;
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $subTimeHours;
$ouHours = -($subEstHours - $subAppliedHours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($subEstHours > 0) {
$ouPct = $diffHours / $subEstHours * 100;
if ($subEstHours > $subAppliedHours && $subEstHours > 0) {
$sign = '-';
} else {
if ($subAppliedHours > $subEstHours && $subAppliedHours > 0) {
$sign = '+';
}
}
}
if ($bExport) {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
} else {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
}
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$reportArray[$arrayIndex][] = $lastGroup;
}
$subEstHours = 0.0;
$subAppliedHours = 0.0;
$subEtcHours = 0.0;
$subTimeHours = 0.0;
if ($bExport) {
$arrayIndex++;
$reportArray[$arrayIndex][0] = 'Totals';
if ($groupBy != '1') {
$reportArray[$arrayIndex][] = '';
}
if ($_REQUEST['bytype'] == '2') {
$reportArray[$arrayIndex][] = '';
}
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $totalEstHours;
$reportArray[$arrayIndex][] = $totalEtcHours;
$reportArray[$arrayIndex][] = $totalAppliedHours;
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $totalTimeHours;
$ouHours = -($totalEstHours - $totalAppliedHours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($totalEstHours > 0) {
$ouPct = $diffHours / $totalEstHours * 100;
if ($totalEstHours > $totalAppliedHours && $totalEstHours > 0) {
$sign = '-';
} else {
if ($totalAppliedHours > $totalEstHours && $totalAppliedHours > 0) {
$sign = '+';
}
}
}
if ($bExport) {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
} else {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
}
if ($groupBy != '0') {
$reportArray[$arrayIndex][] = '';
}
$nameArray = array();
$nameArray[] = STR_WOST_SUMMARY;
if ($groupBy != '1') {
$nameArray[] = STR_WO_PROJECT;
}
if ($_REQUEST['bytype'] == '2') {
$nameArray[] = STR_CMMN_BY;
}
$nameArray[] = STR_WO_STATUS;
$nameArray[] = STR_WO_PRIORITY;
$nameArray[] = STR_WO_SEVERITY;
$nameArray[] = STR_WOST_BUDGET;
$nameArray[] = STR_WOST_ETC;
$nameArray[] = STR_WOST_TODATE;
$nameArray[] = STR_WOST_ESTSTART;
$nameArray[] = STR_WOST_ESTEND;
$nameArray[] = STR_WOST_START;
$nameArray[] = STR_WOST_END;
$nameArray[] = STR_WOST_TIME;
$nameArray[] = '+ / -';
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$nameArray[] = '';
}
ExportArray($nameArray, $reportArray);
} else {
$oTable = CreateObject('dcl.htmlTable');
$oTable->addFooter('Totals');
if ($groupBy != '1') {
$oTable->addFooter('');
}
if ($_REQUEST['bytype'] == '2') {
$oTable->addFooter('');
}
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter($totalEstHours);
$oTable->addFooter($totalEtcHours);
$oTable->addFooter($totalAppliedHours);
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter($totalTimeHours);
$ouHours = -($totalEstHours - $totalAppliedHours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($totalEstHours > 0) {
$ouPct = $diffHours / $totalEstHours * 100;
if ($totalEstHours > $totalAppliedHours && $totalEstHours > 0) {
$sign = '-';
} else {
if ($totalAppliedHours > $totalEstHours && $totalAppliedHours > 0) {
$sign = '+';
}
}
}
$oTable->addFooter(sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct)));
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$oTable->addFooter('');
}
$oTable->addColumn(STR_WOST_SUMMARY, 'html');
if ($groupBy != '1') {
$oTable->addColumn(STR_WO_PROJECT, 'html');
}
if ($_REQUEST['bytype'] == '2') {
$oTable->addColumn(STR_CMMN_BY, 'string');
}
$oTable->addColumn(STR_WO_STATUS, 'string');
$oTable->addColumn(STR_WO_PRIORITY, 'string');
$oTable->addColumn(STR_WO_SEVERITY, 'string');
$oTable->addColumn(STR_WOST_BUDGET, 'numeric');
$oTable->addColumn(STR_WOST_ETC, 'numeric');
$oTable->addColumn(STR_WOST_TODATE, 'numeric');
$oTable->addColumn(STR_WOST_ESTSTART, 'string');
$oTable->addColumn(STR_WOST_ESTEND, 'string');
$oTable->addColumn(STR_WOST_START, 'string');
$oTable->addColumn(STR_WOST_END, 'string');
$oTable->addColumn(STR_WOST_TIME, 'numeric');
$oTable->addColumn('+ / -', 'html');
if (array_key_exists($groupBy, $aGroupOptions) && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$oTable->addColumn($aGroupOptions[$groupBy], 'string');
}
$oTable->setData($reportArray);
$oTable->setCaption(sprintf(STR_WOST_ACTIVITYTITLE, $sReportFor, $_REQUEST['begindate'], $_REQUEST['enddate']));
$oTable->addToolbar(menuLink('', sprintf('menuAction=reportPersonnelActivity.execute&export=1&responsible=%s&begindate=%s&enddate=%s&bytype=%d&groupby=%d&department=%d', $responsible, $begindate, $enddate, $_REQUEST['bytype'], $groupBy, $department)), STR_VW_EXPORTRESULTS);
$oTable->addGroup($iGroupColumn);
$oTable->setShowRownum(true);
$oTable->render();
}
} else {
if ($bExport) {
commonHeader();
}
trigger_error(STR_WOST_NOACTIVITY, E_USER_NOTICE);
$this->getparameters(false);
}
} else {
if ($bExport) {
commonHeader();
}
trigger_error(STR_WOST_QUERYERR, E_USER_ERROR);
}
}
function SetTasks($wostatus, $woresponsible, $bIsGrouping)
{
global $dcl_domain, $dcl_domain_info, $dcl_info;
$cols = array('a.jcn', 'a.seq', 'h.type_name', 'b.short', 'c.name', 'g.module_name', 'd.name', 'e.name', 'a.deadlineon', 'a.totalhours', 'a.etchours', 'a.esthours', '(a.totalhours + a.etchours) - a.esthours', 'a.summary', 'f.sort');
$sql = 'Select a.jcn, a.seq, h.type_name, b.short, c.name, g.module_name, d.name, e.name, ' . $this->entityHotlist->ConvertDate('a.deadlineon', 'deadlineon') . ', a.totalhours, a.etchours, a.esthours, (a.totalhours + a.etchours) - a.esthours, a.summary, f.sort';
if ($dcl_info['DCL_WO_SECONDARY_ACCOUNTS_ENABLED'] == 'Y') {
$sql .= ', (select count(*) from dcl_wo_account where wo_id = a.jcn And seq = a.seq) As num_accounts';
}
$sql .= ' From workorders a ';
$sql .= $this->entityHotlist->JoinKeyword . ' personnel b ON a.responsible = b.id ';
$sql .= $this->entityHotlist->JoinKeyword . ' products c ON a.product = c.id';
$sql .= ' LEFT JOIN dcl_wo_account i ON a.jcn = i.wo_id AND a.seq = i.seq';
$sql .= ' LEFT JOIN dcl_org d ON i.account_id = d.org_id ';
$sql .= $this->entityHotlist->JoinKeyword . ' statuses e ON a.status = e.id ';
$sql .= $this->entityHotlist->JoinKeyword . ' dcl_entity_hotlist f ON f.entity_id =' . DCL_ENTITY_WORKORDER . ' and a.jcn = f.entity_key_id and a.seq = f.entity_key_id2 ';
$sql .= ' LEFT JOIN dcl_product_module g ON a.module_id = g.product_module_id ';
$sql .= $this->entityHotlist->JoinKeyword . ' dcl_wo_type h ON a.wo_type_id = h.wo_type_id';
$sql .= ' Where f.hotlist_id=' . $this->hotlist->hotlist_id;
if ($wostatus > 0) {
$sql .= ' And a.status=' . $wostatus;
} else {
if ($wostatus == -1) {
$sql .= ' And e.dcl_status_type != 2';
} else {
if ($wostatus == -2) {
$sql .= ' And e.dcl_status_type = 2';
}
}
}
if ($woresponsible > 0) {
$sql .= ' And a.responsible=' . $woresponsible;
}
if ($dcl_info['DCL_WO_SECONDARY_ACCOUNTS_ENABLED'] == 'Y') {
$sql .= ' And (i.account_id is null Or i.account_id = ';
$sql .= '(Select min(account_id) From dcl_wo_account where wo_id = a.jcn And seq = a.seq))';
}
if ($bIsGrouping) {
$sql .= ' Order By ' . $cols[$_REQUEST['wogroupby']] . ', f.sort, a.jcn, a.seq';
} else {
$sql .= ' Order By f.sort, a.jcn, a.seq';
}
if ($this->entityHotlist->Query($sql) != -1) {
$allRecs = $this->entityHotlist->FetchAllRows();
$this->entityHotlist->FreeResult();
if (count($allRecs) > 0) {
$aTasks = array();
$objHWO = CreateObject('dcl.htmlWorkorders');
$objWOAcct = CreateObject('dcl.dbWorkOrderAccount');
$oDate = new DCLDate();
for ($i = 0; $i < count($allRecs); $i++) {
$oDate->SetFromDB($allRecs[$i][8]);
$ouHours = -($allRecs[$i][11] - $allRecs[$i][9]);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($allRecs[$i][11] > 0) {
$ouPct = $diffHours / $allRecs[$i][11] * 100;
if ($allRecs[$i][11] > $allRecs[$i][9] && $allRecs[$i][11] > 0) {
$sign = '-';
} else {
if ($allRecs[$i][9] > $allRecs[$i][11] && $allRecs[$i][9] > 0) {
$sign = '+';
}
}
}
$fPctComplete = 0.0;
if ($allRecs[$i][10] + $allRecs[$i][9] > 0) {
$fPctComplete = $allRecs[$i][9] / ($allRecs[$i][10] + $allRecs[$i][9]) * 100;
} elseif ($allRecs[$i][10] == 0.0) {
$fPctComplete = 100.0;
}
$aTasks[] = array('woid' => $allRecs[$i][0], 'seq' => $allRecs[$i][1], 'type' => $allRecs[$i][2], 'responsible' => $allRecs[$i][3], 'product' => $allRecs[$i][4], 'module' => $allRecs[$i][5], 'org' => $allRecs[$i][6], 'status' => $allRecs[$i][7], 'hours' => $allRecs[$i][9], 'etc' => $allRecs[$i][10], 'projected' => $allRecs[$i][11], 'summary' => $allRecs[$i][13], 'deadline' => $oDate->ToDisplay(), 'plusminus' => sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct)), 'pctcomplete' => sprintf("%0.2f%%", $fPctComplete), 'secorgs' => $dcl_info['DCL_WO_SECONDARY_ACCOUNTS_ENABLED'] == 'Y' && $allRecs[$i][15] > 1, 'sort' => $allRecs[$i][14] != 999999 ? $allRecs[$i][14] : '?');
}
if ($bIsGrouping) {
$sGroupBy = null;
switch ($_REQUEST['wogroupby']) {
case '3':
$sGroupBy = 'responsible';
break;
case '7':
$sGroupBy = 'status';
break;
case '4':
$sGroupBy = 'product';
break;
case '5':
$sGroupBy = 'module';
break;
case '2':
$sGroupBy = 'type';
break;
}
$this->oSmarty->assign('VAL_GROUPBY', $sGroupBy);
}
$this->oSmarty->assign_by_ref('VAL_TASKS', $aTasks);
}
}
}
