function execute()
{
global $g_oSession;
$bExport = isset($_REQUEST['export']) && $_REQUEST['export'] == '1';
if (!$bExport) {
commonHeader();
}
$begindate = @DCL_Sanitize::ToDate($_REQUEST['begindate']);
$enddate = @DCL_Sanitize::ToDate($_REQUEST['enddate']);
if ($begindate === null || $enddate === null) {
if ($bExport) {
commonHeader();
}
trigger_error(STR_WOST_DATEERR, E_USER_ERROR);
$this->GetParameters(false);
return;
}
$g_oSession->Register('personnel_activity_begindate', $begindate);
$g_oSession->Register('personnel_activity_enddate', $enddate);
$g_oSession->Register('personnel_activity_bytype', $_REQUEST['bytype']);
$g_oSession->Register('personnel_activity_groupby', $_REQUEST['groupby']);
$g_oSession->Register('personnel_activity_responsible', $_REQUEST['responsible']);
$g_oSession->Register('personnel_activity_department', $_REQUEST['department']);
$g_oSession->Register('personnel_activity_timesheet', isset($_REQUEST['timesheet']) ? $_REQUEST['timesheet'] : 'N');
$g_oSession->Edit();
$bTimesheet = isset($_REQUEST['timesheet']) && $_REQUEST['timesheet'] == 'Y';
if ($bTimesheet && $_REQUEST['groupby'] != '1' && $_REQUEST['groupby'] != '2' && $_REQUEST['groupby'] != '4' && $_REQUEST['groupby'] != '5') {
if ($bExport) {
commonHeader();
}
trigger_error('Timesheet report must by grouped by project, action, action by, or product.', E_USER_ERROR);
$this->GetParameters(false);
return;
}
if ($_REQUEST['groupby'] == '5' && $_REQUEST['bytype'] != '2') {
trigger_error('Grouping by Action By must use report by department.', E_USER_ERROR);
$this->GetParameters(false);
return;
}
$objDB = new dclDB();
$sReportFor = '';
$sCols = 'timecards.jcn, timecards.seq, timecards.hours';
if ($_REQUEST['bytype'] == '2' || $_REQUEST['groupby'] == '5') {
$sCols .= ', personnel.short';
if ($bTimesheet && $_REQUEST['groupby'] == '5') {
$sCols .= ' AS name';
}
}
if ($_REQUEST['groupby'] == '1') {
$sCols .= ', dcl_projects.name';
} else {
if ($_REQUEST['groupby'] == '2') {
$sCols .= ', actions.name';
} else {
if ($_REQUEST['groupby'] == '3') {
$sCols .= ', ' . $objDB->ConvertDate('timecards.actionon', 'actionon');
} else {
if ($_REQUEST['groupby'] == '4') {
$sCols .= ', products.name';
}
}
}
}
if ($bTimesheet) {
$sCols .= ', ' . $objDB->ConvertDate('timecards.actionon', 'actionon');
}
$iGroupColumn = -1;
$query = "select {$sCols} from timecards ";
if ($_REQUEST['groupby'] == '0' || $_REQUEST['groupby'] == '3' || $_REQUEST['groupby'] == '5') {
// None (0) or date (3) or action by (5)
if ($_REQUEST['bytype'] == '1') {
if (($responsible = DCL_Sanitize::ToInt($_REQUEST['responsible'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$query .= ' where actionby=' . $responsible;
$query .= ' and actionon between ' . $objDB->DisplayToSQL($begindate) . ' and ' . $objDB->DisplayToSQL($enddate);
} else {
if (($department = DCL_Sanitize::ToInt($_REQUEST['department'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$query .= $objDB->JoinKeyword . ' personnel on actionby = personnel.id ';
$query .= 'where personnel.department=' . $department;
$query .= ' and actionon between ' . $objDB->DisplayToSQL($begindate) . ' and ' . $objDB->DisplayToSQL($enddate);
}
if ($_REQUEST['groupby'] == '0') {
$query .= ' order by jcn, seq';
} else {
if ($_REQUEST['groupby'] == '5') {
$query .= ' order by personnel.short, jcn, seq';
$iGroupColumn = 2;
} else {
$query .= ' order by actionon, jcn, seq';
$iGroupColumn = 13;
if ($_REQUEST['bytype'] != '1') {
$iGroupColumn++;
}
if ($_REQUEST['groupby'] != '1') {
$iGroupColumn++;
}
}
}
} else {
if ($_REQUEST['groupby'] == '1') {
// projects
if ($_REQUEST['bytype'] == '1') {
if (($responsible = DCL_Sanitize::ToInt($_REQUEST['responsible'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$query .= 'left join projectmap on timecards.jcn = projectmap.jcn and projectmap.seq in (timecards.seq, 0) ';
$query .= 'left join dcl_projects on dcl_projects.projectid = projectmap.projectid ';
$query .= ' where timecards.actionby=' . $responsible;
$query .= ' and timecards.actionon between ' . $objDB->DisplayToSQL($begindate) . ' and ' . $objDB->DisplayToSQL($enddate);
$iGroupColumn = 13;
} else {
if (($department = DCL_Sanitize::ToInt($_REQUEST['department'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$query .= $objDB->JoinKeyword . ' personnel on actionby = personnel.id ';
$query .= 'left join projectmap on timecards.jcn = projectmap.jcn and projectmap.seq in (timecards.seq, 0) ';
$query .= 'left join dcl_projects on dcl_projects.projectid = projectmap.projectid ';
$query .= 'where personnel.department=' . $department;
$query .= ' and actionon between ' . $objDB->DisplayToSQL($begindate) . ' and ' . $objDB->DisplayToSQL($enddate);
$iGroupColumn = 14;
}
$query .= ' order by dcl_projects.name, timecards.jcn, timecards.seq';
} else {
if ($_REQUEST['groupby'] == '2') {
// actions
if ($_REQUEST['bytype'] == '1') {
if (($responsible = DCL_Sanitize::ToInt($_REQUEST['responsible'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$query .= $objDB->JoinKeyword . ' actions on timecards.action = actions.id ';
$query .= ' where timecards.actionby=' . $responsible;
$query .= ' and timecards.actionon between ' . $objDB->DisplayToSQL($begindate) . ' and ' . $objDB->DisplayToSQL($enddate);
$iGroupColumn = 14;
} else {
if (($department = DCL_Sanitize::ToInt($_REQUEST['department'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$query .= $objDB->JoinKeyword . ' personnel on actionby = personnel.id ';
$query .= $objDB->JoinKeyword . ' actions on timecards.action = actions.id ';
$query .= 'where personnel.department=' . $department;
$query .= ' and actionon between ' . $objDB->DisplayToSQL($begindate) . ' and ' . $objDB->DisplayToSQL($enddate);
$iGroupColumn = 15;
}
$query .= ' order by actions.name, timecards.jcn, timecards.seq';
} else {
// product
if ($_REQUEST['bytype'] == '1') {
if (($responsible = DCL_Sanitize::ToInt($_REQUEST['responsible'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$query .= $objDB->JoinKeyword . ' workorders on timecards.jcn = workorders.jcn and timecards.seq = workorders.seq ';
$query .= $objDB->JoinKeyword . ' products on workorders.product = products.id ';
$query .= ' where timecards.actionby=' . $responsible;
$query .= ' and timecards.actionon between ' . $objDB->DisplayToSQL($begindate) . ' and ' . $objDB->DisplayToSQL($enddate);
$iGroupColumn = 14;
} else {
if (($department = DCL_Sanitize::ToInt($_REQUEST['department'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$query .= $objDB->JoinKeyword . ' personnel on actionby = personnel.id ';
$query .= $objDB->JoinKeyword . ' workorders on timecards.jcn = workorders.jcn and timecards.seq = workorders.seq ';
$query .= $objDB->JoinKeyword . ' products on workorders.product = products.id ';
$query .= 'where personnel.department=' . $department;
$query .= ' and actionon between ' . $objDB->DisplayToSQL($begindate) . ' and ' . $objDB->DisplayToSQL($enddate);
$iGroupColumn = 15;
}
$query .= ' order by products.name, timecards.jcn, timecards.seq';
}
}
}
if (isset($_REQUEST['timesheet']) && $_REQUEST['timesheet'] == 'Y') {
$this->ShowTimesheet($query, $iGroupColumn);
} else {
$this->ShowReport($query, $iGroupColumn);
}
}
function _GetSQL()
{
$aItems = $this->_aTypeInfo[$this->group][$this->sub];
$orderby = isset($_REQUEST['order']) && $_REQUEST['order'] == 'count' ? 'count(*)' : $aItems[3];
$sql = sprintf('select count(*), %s, %s, t.dcl_status_type_id, t.dcl_status_type_name from ', $aItems[2], $aItems[3]);
$sql .= sprintf('%s a, %s w, dcl_status_type t, statuses s', $aItems[0], $this->group);
if ($this->group == 'workorders' && $this->sub == 'account') {
$sql .= ', dcl_org ac';
}
$sql .= sprintf(' where %s = %s ', $aItems[4], $aItems[2]);
if ($this->group == 'workorders' && $this->sub == 'account') {
$sql .= ' AND w.jcn = a.wo_id AND w.seq = a.seq ';
}
$sql .= 'and w.status = s.id and s.dcl_status_type = t.dcl_status_type_id and ';
if (isset($_REQUEST['chkLimitByDate']) && $_REQUEST['chkLimitByDate'] == 1) {
$dateFrom = DCL_Sanitize::ToDate($_REQUEST['dateFrom']);
$dateTo = DCL_Sanitize::ToDate($_REQUEST['dateTo']);
$oDB = new dclDB();
// for sql side date formatting
$sql .= '((t.dcl_status_type_id = 1 and w.createdon between ' . $oDB->DisplayToSQL($dateFrom) . ' and ' . $oDB->DisplayToSQL($dateTo) . ') or ';
$sql .= '(t.dcl_status_type_id = 2 and w.closedon between ' . $oDB->DisplayToSQL($dateFrom) . ' and ' . $oDB->DisplayToSQL($dateTo) . ')) ';
} else {
$sql .= 't.dcl_status_type_id != 3 ';
}
if ($aItems[5] != '') {
$sql .= ' and (' . $aItems[5] . ') ';
}
$sql .= sprintf('group by %s, %s, t.dcl_status_type_id, t.dcl_status_type_name order by %s', $aItems[2], $aItems[3], $orderby);
return $sql;
}
function SearchFromStat()
{
global $dcl_domain_info, $dcl_domain, $g_oSec;
commonHeader();
if (!$g_oSec->HasPerm(DCL_ENTITY_WORKORDER, DCL_PERM_VIEW)) {
return PrintPermissionDenied();
}
$responsible = DCL_Sanitize::ToInt($_REQUEST['responsible']);
$product = DCL_Sanitize::ToInt($_REQUEST['product']);
$status = DCL_Sanitize::ToInt($_REQUEST['status']);
$begindate = DCL_Sanitize::ToDate($_REQUEST['begindate']);
$enddate = DCL_Sanitize::ToDate($_REQUEST['enddate']);
$obj = new dclDB();
$objView = CreateObject('dcl.boView');
$objView->style = 'report';
$objView->title = STR_WOST_SEARCHRESULTS;
$objView->AddDef('columns', '', array('jcn', 'seq', 'responsible.short', 'products.name', 'statuses.name', 'eststarton', 'deadlineon', 'etchours', 'totalhours', 'summary'));
$objView->AddDef('columnhdrs', '', array(STR_WO_JCN, STR_WO_SEQ, STR_WO_RESPONSIBLE, STR_WO_PRODUCT, STR_WO_STATUS, STR_WO_ESTSTART, STR_WO_DEADLINE, STR_WO_ETCHOURS, STR_WO_ACTHOURS, STR_WO_SUMMARY));
$objView->AddDef('order', '', array('priorities.weight', 'severities.weight', 'jcn', 'seq'));
if ($begindate !== null || $enddate !== null) {
$objView->AddDef('filter', 'statuses.dcl_status_type', '2');
$objView->AddDef('filterdate', 'closedon', array($obj->DisplayToSQL($begindate), $obj->DisplayToSQL($enddate)));
} else {
if ($status !== null) {
$objView->AddDef('filter', 'status', $status);
} else {
$objView->AddDef('filternot', 'statuses.dcl_status_type', '2');
}
}
if ($responsible !== null) {
$objView->AddDef('filter', 'responsible', $responsible);
}
if ($product !== null) {
$objView->AddDef('filter', 'product', $product);
}
$obj = CreateViewObject($objView->table);
$obj->Render($objView);
}
