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); } } }