function ToDateTime($vValue)
{
$oDate = new DCLTimestamp();
// 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 GetHoursText($oStartedOn = null, $oLoggedOn = null)
{
$start = new DCLTimestamp();
if ($oStartedOn === null) {
$start->SetFromDB($this->f('startedon'));
} else {
$start->SetFromDB($oStartedOn);
}
$end = new DCLTimestamp();
if ($oLoggedOn === null) {
$end->SetFromDB($this->f('loggedon'));
} else {
$end->SetFromDB($oLoggedOn);
}
$tempHours = $end->time - $start->time;
$hh = intval($tempHours / 3600);
$tempHours -= $hh * 3600;
$mm = intval($tempHours / 60);
$tempHours -= $mm * 60;
$ss = intval($tempHours);
return sprintf('%02d:%02d:%02d', $hh, $mm, $ss);
}
function ShowUserVsProductStatus()
{
global $dcl_info;
commonHeader();
global $products, $people, $begindate, $enddate, $activity, $byaccount;
$objProduct = CreateObject('dcl.dbProducts');
$objStatuses = CreateObject('dcl.dbStatuses');
$objTickets = CreateObject('dcl.dbTickets');
$doingActivity = isset($activity) && $activity == '1';
$doingAccounts = isset($byaccount) && $byaccount == '1';
if ($doingAccounts) {
$vsTable = 'dcl_org';
$vsDesc = 'name';
$vsField = 'account';
$objPersonnel = CreateObject('dcl.dbOrg');
} else {
$vsTable = 'personnel';
$vsDesc = 'short';
$vsField = 'responsible';
$objPersonnel = CreateObject('dcl.dbPersonnel');
}
if (count($products) < 1) {
$query = 'SELECT id FROM products ORDER BY name';
$objProduct->Query($query);
$products = $objProduct->FetchAllRows();
}
$doingClosed = FALSE;
if ($begindate != '') {
$doingClosed = TRUE;
}
if ($enddate != '') {
$doingClosed = TRUE;
}
if ($doingActivity) {
$statii = array(1 => 'PR', 2 => 'OP', 3 => 'CL', 4 => 'FW');
} else {
$query = 'SELECT id FROM statuses';
$query .= ' WHERE dcl_status_type ';
if ($doingClosed) {
$query .= '= 2';
} else {
$query .= '!= 2';
}
$objStatuses->Query($query);
$statii = $objStatuses->FetchAllRows();
}
if ($doingAccounts) {
$query = 'SELECT org_id FROM dcl_org ORDER BY name';
} else {
if (count($people) < 1) {
$idWhere = 'id > 1';
} else {
$idWhere = 'id in (';
for ($i = 0; $i < count($people); $i++) {
if ($i > 0) {
$idWhere .= ',';
}
$idWhere .= $people[$i];
}
$idWhere .= ')';
}
$query = "SELECT id FROM personnel WHERE {$idWhere} ORDER BY short";
}
$objPersonnel->Query($query);
$person = $objPersonnel->FetchAllRows();
$query = "SELECT product,status,{$vsField}";
if ($doingActivity) {
$query .= ',createdon,closedon FROM tickets, statuses WHERE tickets.status = statuses.id AND (statuses.dcl_status_type != 2 OR (statuses.dcl_status_type = 2 AND ';
if ($begindate != '' && $enddate != '') {
$query .= 'closedon between ' . $objTickets->DisplayToSQL($begindate . ' 00:00:00') . ' AND ' . $objTickets->DisplayToSQL($enddate . ' 23:59:59');
} else {
if ($begindate != '') {
$query .= 'closedon >=' . $objTickets->DisplayToSQL($begindate . ' 00:00:00');
} else {
if ($enddate != '') {
$query .= 'closedon <=' . $objTickets->DisplayToSQL($enddate . ' 23:59:59');
}
}
}
$query .= '))';
} else {
$query .= ' FROM tickets, statuses WHERE tickets.status = statuses.id AND statuses.dcl_status_type ';
if ($doingClosed) {
$query .= '= 2';
if ($begindate != '' && $enddate != '') {
$query .= ' AND closedon between ' . $objTickets->DisplayToSQL($begindate . ' 00:00:00') . ' AND ' . $objTickets->DisplayToSQL($enddate . ' 23:59:59');
} else {
if ($begindate != '') {
$query .= ' AND closedon >=' . $objTickets->DisplayToSQL($begindate . ' 00:00:00');
} else {
if ($enddate != '') {
$query .= ' AND closedon <=' . $objTickets->DisplayToSQL($enddate . ' 23:59:59');
}
}
}
} else {
$query .= '!= 2';
}
}
$objTimestamp = new DCLTimestamp();
$objNow = new DCLTimestamp();
$objNow->SetFromDisplay($begindate . ' 00:00:00');
$myArrayUnits = array();
for ($i = 0; $i <= count($person) + 1; $i++) {
for ($j = 0; $j <= count($products) * count($statii) + count($statii); $j++) {
$myArrayUnits[$j][$i] = 0;
}
}
trigger_error($query);
$objTickets->Query($query);
while ($objTickets->next_record()) {
$thisProduct = -1;
$thisStatus = -1;
$thisPerson = -1;
$thisOpened = '';
$thisClosed = '';
for ($j = 0; $j < count($products); $j++) {
if ($products[$j][0] == $objTickets->f(0)) {
$thisProduct = $j;
break;
}
}
for ($j = 0; $j < count($person); $j++) {
if ($person[$j][0] == $objTickets->f(2)) {
$thisPerson = $j;
break;
}
}
if ($doingActivity) {
if ($thisProduct > -1 && $thisPerson > -1) {
$objTimestamp->SetFromDB($objTickets->f(3));
// Opened before begin date?
if ($objTimestamp->time < $objNow->time) {
$myArrayUnits[$thisProduct * count($statii)][$thisPerson]++;
$myArrayUnits[count($products) * count($statii)][$thisPerson]++;
$myArrayUnits[$thisProduct * count($statii)][count($person)]++;
$myArrayUnits[count($products) * count($statii)][count($person)]++;
} else {
$myArrayUnits[$thisProduct * count($statii) + 1][$thisPerson]++;
$myArrayUnits[count($products) * count($statii) + 1][$thisPerson]++;
$myArrayUnits[$thisProduct * count($statii) + 1][count($person)]++;
$myArrayUnits[count($products) * count($statii) + 1][count($person)]++;
}
// Closed or forward?
if ($objStatuses->GetStatusType($objTickets->f(1)) == 2) {
$myArrayUnits[$thisProduct * count($statii) + 2][$thisPerson]++;
$myArrayUnits[count($products) * count($statii) + 2][$thisPerson]++;
$myArrayUnits[$thisProduct * count($statii) + 2][count($person)]++;
$myArrayUnits[count($products) * count($statii) + 2][count($person)]++;
} else {
$myArrayUnits[$thisProduct * count($statii) + 3][$thisPerson]++;
$myArrayUnits[count($products) * count($statii) + 3][$thisPerson]++;
$myArrayUnits[$thisProduct * count($statii) + 3][count($person)]++;
$myArrayUnits[count($products) * count($statii) + 3][count($person)]++;
}
$myArrayUnits[count($products) * count($statii) + count($statii)][$thisPerson]++;
$myArrayUnits[$thisProduct * count($statii)][count($person) + 1]++;
$myArrayUnits[count($products) * count($statii) + count($statii)][count($person)]++;
}
} else {
for ($j = 0; $j < count($statii); $j++) {
if ($statii[$j][0] == $objTickets->f(1)) {
$thisStatus = $j;
break;
}
}
if ($thisProduct > -1 && $thisStatus > -1 && $thisPerson > -1) {
$myArrayUnits[$thisProduct * count($statii) + $thisStatus][$thisPerson]++;
$myArrayUnits[count($products) * count($statii) + $thisStatus][$thisPerson]++;
$myArrayUnits[count($products) * count($statii) + count($statii)][$thisPerson]++;
$myArrayUnits[$thisProduct * count($statii) + $thisStatus][count($person)]++;
$myArrayUnits[$thisProduct * count($statii)][count($person) + 1]++;
$myArrayUnits[count($products) * count($statii) + $thisStatus][count($person)]++;
$myArrayUnits[count($products) * count($statii) + count($statii)][count($person)]++;
}
}
}
print '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=1>';
print '<TR><TD>';
print '<TABLE BORDER=0 CELLSPACING=2 CELLPADDING=1>';
print '<TR>';
print '<TH ROWSPAN=2>';
print STR_CMMN_LOGIN;
print '</TH>';
$ii = 0;
for ($i = 0; $i < count($products); $i++) {
if ($myArrayUnits[$i * count($statii)][count($person) + 1] == 0) {
continue;
}
print '<TH COLSPAN=' . count($statii);
print '>';
$objProduct->Load($products[$i][0]);
print $objProduct->name;
print '</TH>';
$ii++;
}
print '<TH COLSPAN=' . (count($statii) + 1);
print '>';
print STR_CMMN_TOTALS;
print '</TH></TR><TR>';
$statusCol = '';
for ($j = 0; $j < count($statii); $j++) {
$statusCol .= '<TH>';
if ($doingActivity) {
$statusCol .= $statii[$j + 1];
} else {
$objStatuses->Load($statii[$j][0]);
$statusCol .= $objStatuses->short;
}
$statusCol .= '</TH>';
}
for ($i = 0; $i < count($products); $i++) {
if ($myArrayUnits[$i * count($statii)][count($person) + 1] == 0) {
continue;
}
print $statusCol;
}
// For the totals, dontcha know?
print $statusCol;
printf('<TH>%s</TH>', STR_WOST_ALL);
print '</TR>';
for ($i = 0; $i < count($person) + 2; $i++) {
if ($i < count($person) && $myArrayUnits[count($products) * count($statii) + count($statii)][$i] == 0) {
continue;
}
if ($i < count($person)) {
$objPersonnel->Load($person[$i][0]);
print '<TR><TD>' . $objPersonnel->short . '</TD>';
} elseif ($i == count($person)) {
print '<TR><TD>' . STR_TCK_STATUS . '</TD>';
} else {
print '<TR><TD>' . STR_TCK_PRODUCT . '</TD>';
}
for ($j = 0; $j < count($products) + 1; $j++) {
if ($j < count($products) && $myArrayUnits[$j * count($statii)][count($person) + 1] == 0) {
continue;
}
for ($k = 0; $k < count($statii) + 1; $k++) {
if ($i < count($person) + 1 && ($j < count($products) && $k < count($statii) || $j == count($products)) || $i == count($person) + 1 && $k == 0 && $j < count($products)) {
$units = $myArrayUnits[$j * count($statii) + $k][$i];
print '<TD';
if ($i == count($person) + 1) {
print ' ALIGN=CENTER COLSPAN=' . count($statii);
}
print '>';
if ($units > 0) {
$sMenuAction = 'menuAction=htmlTicketStatistics.SearchFromStat';
if ($k < count($statii) && $i < count($person) + 1) {
$sMenuAction .= '&status=' . $statii[$k][0];
}
if ($j < count($products)) {
$sMenuAction .= '&product=' . $products[$j][0];
}
if ($i < count($person)) {
$sMenuAction .= '&responsible=' . $person[$i][0];
}
if ($begindate != '') {
$sMenuAction .= '&begindate=' . $begindate;
}
if ($enddate != '') {
$sMenuAction .= '&enddate=' . $enddate;
}
printf('<a href="%s">%s</a>', menuLink('', $sMenuAction), $units);
} else {
print ' ';
}
print '</TD>';
}
}
}
print '</TR>';
}
print '</TABLE>';
print '</TD>';
print '</TR>';
print '</TABLE>';
}
function dbadd()
{
global $dcl_info, $g_oSec;
commonHeader();
if (($iID = @DCL_Sanitize::ToInt($_REQUEST['ticketid'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
if (!$g_oSec->HasPerm(DCL_ENTITY_TICKET, DCL_PERM_ACTION, $iID)) {
return PrintPermissionDenied();
}
$this->oDB->InitFromGlobals();
$this->oDB->loggedby = $GLOBALS['DCLID'];
$this->oDB->loggedon = date($dcl_info['DCL_TIMESTAMP_FORMAT']);
$this->oDB->is_public = @DCL_Sanitize::ToYN($_REQUEST['is_public']);
$obj =& CreateObject('dcl.dbTickets');
if ($obj->Load($this->oDB->ticketid) == -1) {
return;
}
$obj->lastactionon = date($dcl_info['DCL_TIMESTAMP_FORMAT']);
$notify = '4';
if ($this->oDB->status != $obj->status) {
$oStatus =& CreateObject('dcl.dbStatuses');
$notify .= ',3';
$obj->statuson = date($dcl_info['DCL_TIMESTAMP_FORMAT']);
if ($oStatus->GetStatusType($this->oDB->status) == 2) {
$notify .= ',2';
$obj->closedby = $GLOBALS['DCLID'];
$obj->closedon = date($dcl_info['DCL_TIMESTAMP_FORMAT']);
}
if ($oStatus->GetStatusType($this->oDB->status) == 1) {
$notify .= ',1';
}
$obj->status = $this->oDB->status;
}
if (isset($_REQUEST['escalate']) && $_REQUEST['escalate'] == '1') {
$objP =& CreateObject('dcl.dbProducts');
$objP->Load($obj->product);
if ($obj->responsible != $objP->ticketsto) {
$obj->responsible = $objP->ticketsto;
$objDP =& CreateObject('dcl.dbPersonnel');
$objDP->Load($obj->responsible);
$this->oDB->resolution = '*** ' . STR_BO_ESCALATEDTO . ': ' . $objDP->short . ' ***' . phpCrLf . phpCrLf . $this->oDB->resolution;
}
} else {
if ($g_oSec->HasPerm(DCL_ENTITY_TICKET, DCL_PERM_ASSIGN)) {
$iReassignTo = @DCL_Sanitize::ToInt($_REQUEST['reassign_to_id']);
if ($iReassignTo > 0 && $obj->responsible != $iReassignTo) {
$obj->responsible = $iReassignTo;
}
}
}
if (isset($_REQUEST['tags']) && $g_oSec->HasPerm(DCL_ENTITY_TICKET, DCL_PERM_MODIFY)) {
$oTag =& CreateObject('dcl.dbEntityTag');
$oTag->serialize(DCL_ENTITY_TICKET, $iID, 0, $_REQUEST['tags']);
}
$this->oDB->BeginTransaction();
$this->oDB->Add();
$start = new DCLTimestamp();
$start->SetFromDisplay($this->oDB->startedon);
$end = new DCLTimestamp();
$end->SetFromDisplay($this->oDB->loggedon);
$obj->seconds += $end->time - $start->time;
$obj->Edit();
$this->oDB->EndTransaction();
$objWtch =& CreateObject('dcl.boWatches');
$objWtch->sendTicketNotification($obj, $notify);
@$this->sendCustomerResponseEmail($obj);
$objH =& CreateObject('dcl.htmlTicketDetail');
$objH->Show($obj);
}
function showgraph()
{
commonHeader();
// GD is required, so short-circuit if not installed
if (!extension_loaded('gd')) {
trigger_error(STR_BO_GRAPHNEEDSGD);
return;
}
if (($iDays = @DCL_Sanitize::ToInt($_REQUEST['days'])) === null || ($dateFrom = @DCL_Sanitize::ToDate($_REQUEST['dateFrom'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$iProduct = 0;
if (($iProduct = @DCL_Sanitize::ToInt($_REQUEST['product'])) === null) {
$iProduct = 0;
}
$objG =& CreateObject('dcl.boGraph');
$obj =& CreateObject('dcl.dbWorkorders');
$beginDate = new DCLTimestamp();
$endDate = new DCLTimestamp();
$testDate = new DCLDate();
$testTS = new DCLTimestamp();
$endDate->SetFromDisplay($dateFrom . ' 23:59:59');
$beginDate->SetFromDisplay($dateFrom . ' 00:00:00');
$beginDate->time -= ($iDays - 1) * 86400;
$query = 'SELECT ' . $obj->ConvertTimestamp('createdon', 'createdon') . ', ' . $obj->ConvertTimestamp('closedon', 'closedon') . ' FROM workorders WHERE ';
if ($iProduct > 0) {
$query .= 'product = ' . $iProduct . ' AND ';
}
$query .= '(createdon between ' . $obj->DisplayToSQL($beginDate->ToDisplay());
$query .= ' AND ' . $obj->DisplayToSQL($endDate->ToDisplay());
$query .= ') OR (closedon between ' . $obj->DisplayToSQL($beginDate->ToDisplay());
$query .= ' AND ' . $obj->DisplayToSQL($endDate->ToDisplay()) . ')';
$obj->Query($query);
$objG->data[0] = array();
// Open
$objG->data[1] = array();
// Closed
$daysBack = array();
$testDate->time = $beginDate->time;
for ($i = 0; $i < $iDays; $i++) {
$daysBack[$i] = $testDate->time;
// Set the relevant object properties while we're at it
$objG->line_captions_x[$i] = date('m/d', $testDate->time);
$objG->data[0][$i] = 0;
$objG->data[1][$i] = 0;
$testDate->time += 86400;
}
while ($obj->next_record()) {
$iTime = 0;
for ($y = 0; $y < 2; $y++) {
if ($y == 0) {
$testTS->SetFromDB($obj->f($y));
$iTime = $testTS->time;
} else {
$testDate->SetFromDB($obj->f($y));
$iTime = $testDate->time;
}
$j = $iDays - 1;
while ($j >= 0) {
if ($iTime >= $daysBack[$j]) {
if (!isset($objG->data[$y][$j])) {
$objG->data[$y][$j] = 0;
}
$objG->data[$y][$j]++;
break;
}
$j--;
}
}
}
$objG->title = STR_BO_WOGRAPHTITLE;
if ($iProduct > 0) {
$oDB =& CreateObject('dcl.dbProducts');
if ($oDB->Load($iProduct) != -1) {
$objG->title .= ' ' . $oDB->name;
}
}
$objG->caption_y = STR_BO_WOGRAPHCAPTIONY;
$objG->caption_x = STR_BO_GRAPHCAPTIONX;
$objG->num_lines_y = 15;
$objG->num_lines_x = $iDays;
$objG->colors = array('red', 'blue');
print '<center>';
echo '<img border="0" src="', menuLink('', 'menuAction=boGraph.Show&' . $objG->ToURL()), '">';
print '</center>';
}
function showgraph()
{
commonHeader();
// GD is required, so short-circuit if not installed
if (!extension_loaded('gd')) {
trigger_error(STR_BO_GRAPHNEEDSGD);
return;
}
$objG =& CreateObject('dcl.boGraph');
$obj =& CreateObject('dcl.dbTickets');
$beginDate = new DCLTimestamp();
$endDate = new DCLTimestamp();
$testDate = new DCLTimestamp();
if (($iDays = @DCL_Sanitize::ToInt($_REQUEST['days'])) === null || ($dateFrom = @DCL_Sanitize::ToDate($_REQUEST['dateFrom'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$endDate->SetFromDisplay($dateFrom . ' 23:59:59');
$beginDate->SetFromDisplay($dateFrom . ' 00:00:00');
$beginDate->time -= ($iDays - 1) * 86400;
$product_id = 0;
if (($product_id = @DCL_Sanitize::ToInt($_REQUEST['product'])) === null) {
$product_id = 0;
}
if ($obj->LoadDatesByRange($beginDate->ToDisplay(), $endDate->ToDisplay(), $product_id) == -1) {
return;
}
$objG->data[0] = array();
// Open
$objG->data[1] = array();
// Closed
$daysBack = array();
$testDate->time = $beginDate->time;
for ($i = 0; $i < $iDays; $i++) {
$daysBack[$i] = $testDate->time;
// Set the relevant object properties while we're at it
$objG->line_captions_x[$i] = date('m/d', $testDate->time);
$objG->data[0][$i] = 0;
$objG->data[1][$i] = 0;
$testDate->time += 86400;
}
while ($obj->next_record()) {
for ($y = 0; $y < 2; $y++) {
$testDate->SetFromDB($obj->f($y));
$j = $iDays - 1;
while ($j >= 0) {
if ($testDate->time >= $daysBack[$j]) {
if (!isset($objG->data[$y][$j])) {
$objG->data[$y][$j] = 0;
}
$objG->data[$y][$j]++;
break;
}
$j--;
}
}
}
$objG->title = STR_BO_GRAPHTITLE;
if ($product_id > 0) {
$oDB =& CreateObject('dcl.dbProducts');
if ($oDB->Load($product_id) != -1) {
$objG->title .= ' ' . $oDB->name;
}
}
$objG->caption_y = STR_BO_GRAPHCAPTIONY;
$objG->caption_x = STR_BO_GRAPHCAPTIONX;
$objG->num_lines_y = 15;
$objG->num_lines_x = $iDays;
$objG->colors = array('red', 'blue');
print '<center>';
echo '<img border="0" src="', menuLink('', 'menuAction=boGraph.Show&' . $objG->ToURL()), '">';
print '</center>';
}