public function testTimeZoneIssues()
{
$tz = new DateTimeZone('America/Los_Angeles');
$dt1 = new QDateTime('11/02/14', $tz);
// dst boundary date
$this->assertEquals('America/Los_Angeles', $dt1->getTimezone()->getName());
$dt2 = new QDateTime($dt1, null, QDateTime::DateOnlyType);
$this->assertEquals('America/Los_Angeles', $dt2->getTimezone()->getName());
$this->assertTrue($dt2->IsTimeNull());
$dt2->setTime(7, 0, 0);
$this->assertEquals(7, $dt2->Hour);
$this->assertEquals('America/Los_Angeles', $dt2->getTimezone()->getName());
// Test a specific PHP 'bug'. Not sure if it is a bug, or just a way things work.
$dt2 = new QDateTime($dt1->format(DateTime::ISO8601), null, QDateTime::DateOnlyType);
$dt2->setTime(7, 0, 0);
$this->assertEquals(7, $dt2->Hour);
$dt2 = new QDateTime('1/1/14', new DateTimeZone('America/Los_Angeles'));
$dt2->Timestamp = 1288486753;
$this->assertEquals('America/Los_Angeles', $dt2->getTimezone()->getName());
// make sure timezone isn't changed
$this->assertEquals(1288486753, $dt2->Timestamp);
// this isn't always true. If this is a dst boundary, it will not be true. Just making sure it is true when its supposed to be
}
/**
* This is an internally called method that generates the SQL
* for the WHERE portion of the query for searching by Category,
* Manufacturer, Name, or Part Number. This is intended to be called
* from Asset::LoadArrayBySearch() and Asset::CountBySearch
* This has been updated for calls from LoadArrayBySimpleSearch() but will
* also work with the LoadArrayBySearch() method is well.
* This was done in case we revert back to the older, advanced search.
*
* @param string $strAssetCode
* @param int $intLocationId
* @param int $intAssetModelId
* @param int $intCategoryId
* @param int $intManufacturerId
* @param string $strAssetModelCode
* @param string $strShortDescription
* @return array with seven keys, strAssetCodeSql, strLocationSql, strAssetModelSql, strCategorySql, strManufacturerSql, strAssetModelCodeSql, strShortDescriptionSql
*/
protected static function GenerateSearchSql($strAssetCode = null, $intLocationId = null, $intAssetModelId = null, $intCategoryId = null, $intManufacturerId = null, $blnOffsite = false, $strAssetModelCode = null, $intReservedBy = null, $intCheckedOutBy = null, $strShortDescription = null, $arrCustomFields = null, $strDateModified = null, $strModifiedCreated, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null, $blnIncludeTBR = null, $blnIncludeShipped = null, $blnIncludeArchived = null, $intCheckedOutToUser = null, $intCheckedOutToContact = null, $blnChekcedOutPastDue = false, $intAssetId = null)
{
// Define all indexes for the array to be returned
$arrSearchSql = array("strAssetCodeSql" => "", "strLocationSql" => "", "strAssetModelSql" => "", "strCategorySql" => "", "strManufacturerSql" => "", "strOffsiteSql" => "", "strAssetModelCodeSql" => "", "strReservedBySql" => "", "strCheckedOutBySql" => "", "strShortDescriptionSql" => "", "strCustomFieldsSql" => "", "strDateModifiedSql" => "", "strAuthorizationSql" => "", "strAttachmentSql" => "", "strArchivedSql" => "", "strIncludeTBRSql" => "", "strIncludeShippedSql" => "", "strCheckedOutToUserSql" => "", "strCheckedOutToContactSql" => "", "strCheckedOutPastDueSql" => "", "strAssetIdSql" => "");
if ($strAssetCode) {
// Properly Escape All Input Parameters using Database->SqlVariable()
$strAssetCode = QApplication::$Database[1]->SqlVariable("%" . $strAssetCode . "%", false);
$arrSearchSql['strAssetCodeSql'] = "AND `asset` . `asset_code` LIKE {$strAssetCode}";
}
if ($intLocationId) {
$intLocationId = QApplication::$Database[1]->SqlVariable($intLocationId, true);
$arrSearchSql['strLocationSql'] = sprintf("AND `asset` . `location_id`%s", $intLocationId);
}
if ($intAssetModelId) {
$intAssetModelId = QApplication::$Database[1]->SqlVariable($intAssetModelId, true);
$arrSearchSql['strAssetModelSql'] = sprintf("AND `asset` . `asset_model_id`%s", $intAssetModelId);
}
if ($intCategoryId) {
$intCategoryId = QApplication::$Database[1]->SqlVariable($intCategoryId, true);
$arrSearchSql['strCategorySql'] = sprintf("AND `asset__asset_model_id__category_id`.`category_id`%s", $intCategoryId);
}
if ($intManufacturerId) {
$intManufacturerId = QApplication::$Database[1]->SqlVariable($intManufacturerId, true);
$arrSearchSql['strManufacturerSql'] = sprintf("AND `asset__asset_model_id__manufacturer_id`.`manufacturer_id`%s", $intManufacturerId);
}
if ($intAssetId) {
$intAssetId = QApplication::$Database[1]->SqlVariable($intAssetId, true);
$arrSearchSql['strAssetIdSql'] = sprintf("AND `asset` . `asset_id`%s", $intAssetId);
}
/*if (!$blnOffsite && !$intLocationId && !$blnIncludeShipped && !$blnIncludeTBR) {
$arrSearchSql['strOffsiteSql'] = "AND `asset` . `location_id` != 2 AND `asset` . `location_id` != 5";
}*/
if ($strShortDescription) {
$strShortDescription = QApplication::$Database[1]->SqlVariable("%" . $strShortDescription . "%", false);
$arrSearchSql['strShortDescriptionSql'] = "AND `asset__asset_model_id`.`short_description` LIKE {$strShortDescription}";
}
if ($strAssetModelCode) {
$strAssetModelCode = QApplication::$Database[1]->SqlVariable("%" . $strAssetModelCode . "%", false);
$arrSearchSql['strAssetModelCodeSql'] = "AND `asset__asset_model_id`.`asset_model_code` LIKE {$strAssetModelCode}";
}
/* if ($intReservedBy) {
$arrSearchSql['strReservedBySql'] = sprintf("AND `asset` . `reserved_flag` = true", $intReservedBy);
if ($intReservedBy != 'any') {
$intReservedBy = QApplication::$Database[1]->SqlVariable($intReservedBy, true);
// This uses a subquery, and as such cannot be converted to QQuery without hacking as of 2/22/07
$arrSearchSql['strReservedBySql'] .= sprintf("\nAND (SELECT `created_by` FROM `transaction` WHERE `transaction_type_id` = 8 ORDER BY creation_date DESC LIMIT 0,1)%s", $intReservedBy);
}
}
if ($intCheckedOutBy) {
$arrSearchSql['strCheckedOutBySql'] = sprintf("AND `asset` . `checked_out_flag` = true", $intCheckedOutBy);
if ($intCheckedOutBy != 'any') {
$intCheckedOutBy = QApplication::$Database[1]->SqlVariable($intCheckedOutBy, true);
// This uses a subquery, and as such cannot be converted to QQuery without hacking as of 2/22/07
$arrSearchSql['strCheckedOutBySql'] .= sprintf("\nAND (SELECT `created_by` FROM `transaction` WHERE `transaction_type_id` = 3 ORDER BY creation_date DESC LIMIT 0,1)%s", $intCheckedOutBy);
}
} */
if ($intReservedBy) {
$arrSearchSql['strReservedBySql'] = sprintf("AND `asset` . `reserved_flag` = true");
if ($intReservedBy != 'any') {
$intReservedBy = QApplication::$Database[1]->SqlVariable($intReservedBy, true);
// This uses a subquery, and as such cannot be converted to QQuery without hacking as of 2/22/07
$arrSearchSql['strReservedBySql'] .= sprintf("\nAND (SELECT `created_by` FROM `asset_transaction` WHERE `asset_transaction`.`asset_id` = `asset`.`asset_id` ORDER BY `asset_transaction`.`creation_date` DESC LIMIT 0,1)%s", $intReservedBy);
}
}
if ($intCheckedOutBy) {
$arrSearchSql['strCheckedOutBySql'] = sprintf("AND `asset` . `checked_out_flag` = true");
if ($intCheckedOutBy != 'any') {
$intCheckedOutBy = QApplication::$Database[1]->SqlVariable($intCheckedOutBy, true);
// This uses a subquery, and as such cannot be converted to QQuery without hacking as of 2/22/07
$arrSearchSql['strCheckedOutBySql'] .= sprintf("\nAND (SELECT `created_by` FROM `asset_transaction` WHERE `asset_transaction`.`asset_id` = `asset`.`asset_id` ORDER BY `asset_transaction`.`creation_date` DESC LIMIT 0,1)%s", $intCheckedOutBy);
}
}
if ($intCheckedOutToUser) {
// Excepts duplicates
//if (!$intCheckedOutBy)
$arrSearchSql['strCheckedOutToUserSql'] = sprintf("AND `asset` . `checked_out_flag` = true");
/*else
$arrSearchSql['strCheckedOutToUserSql'] = "";*/
if ($intCheckedOutToUser != 'any') {
$intCheckedOutToUser = QApplication::$Database[1]->SqlVariable($intCheckedOutToUser, true);
// This uses a subquery, and as such cannot be converted to QQuery without hacking as of 2/22/07
$arrSearchSql['strCheckedOutToUserSql'] .= sprintf("\nAND (SELECT `to_user_id` FROM `asset_transaction` LEFT JOIN `asset_transaction_checkout` ON `asset_transaction`.`asset_transaction_id` = `asset_transaction_checkout`.`asset_transaction_id` WHERE `asset_transaction`.`asset_id` = `asset`.`asset_id` ORDER BY `asset_transaction`.`creation_date` DESC LIMIT 0,1)%s", $intCheckedOutToUser);
} else {
$arrSearchSql['strCheckedOutToUserSql'] .= sprintf("\nAND (SELECT `to_user_id` FROM `asset_transaction` LEFT JOIN `asset_transaction_checkout` ON `asset_transaction`.`asset_transaction_id` = `asset_transaction_checkout`.`asset_transaction_id` WHERE `asset_transaction`.`asset_id` = `asset`.`asset_id` ORDER BY `asset_transaction`.`creation_date` DESC LIMIT 0,1)IS NOT NULL");
}
}
if ($intCheckedOutToContact) {
// Excepts duplicates
if (!$intCheckedOutBy && !$intCheckedOutToUser) {
$arrSearchSql['strCheckedOutToContactSql'] = sprintf("AND `asset` . `checked_out_flag` = true");
} else {
$arrSearchSql['strCheckedOutToContactSql'] = "";
}
if (strpos($intCheckedOutToContact, 'any') === false) {
$intCheckedOutToContact = QApplication::$Database[1]->SqlVariable($intCheckedOutToContact, true);
// This uses a subquery, and as such cannot be converted to QQuery without hacking as of 2/22/07
$arrSearchSql['strCheckedOutToContactSql'] .= sprintf("\nAND (SELECT `to_contact_id` FROM `asset_transaction` LEFT JOIN `asset_transaction_checkout` ON `asset_transaction`.`asset_transaction_id` = `asset_transaction_checkout`.`asset_transaction_id` WHERE `asset_transaction`.`asset_id` = `asset`.`asset_id` ORDER BY `asset_transaction`.`creation_date` DESC LIMIT 0,1)%s", $intCheckedOutToContact);
} elseif ($intCheckedOutToContact != 'any') {
// Gets company id
$intCompanyId = intval(substr($intCheckedOutToContact, 4));
$arrSearchSql['strCheckedOutToUserSql'] .= sprintf("\nAND (SELECT `to_contact_id` FROM `asset_transaction` LEFT JOIN `asset_transaction_checkout` ON `asset_transaction`.`asset_transaction_id` = `asset_transaction_checkout`.`asset_transaction_id` WHERE `asset_transaction`.`asset_id` = `asset`.`asset_id` ORDER BY `asset_transaction`.`creation_date` DESC LIMIT 0,1) IN (SELECT `contact_id` FROM `contact` WHERE `company_id`='%s')", $intCompanyId);
} else {
$arrSearchSql['strCheckedOutToUserSql'] .= sprintf("\nAND (SELECT `to_contact_id` FROM `asset_transaction` LEFT JOIN `asset_transaction_checkout` ON `asset_transaction`.`asset_transaction_id` = `asset_transaction_checkout`.`asset_transaction_id` WHERE `asset_transaction`.`asset_id` = `asset`.`asset_id` ORDER BY `asset_transaction`.`creation_date` DESC LIMIT 0,1)IS NOT NULL");
}
}
if ($blnChekcedOutPastDue) {
if (!$intCheckedOutBy && !$intCheckedOutToUser && !$intCheckedOutToContact) {
$arrSearchSql['strCheckedOutPastDueSql'] = sprintf("AND `asset` . `checked_out_flag` = true");
} else {
$arrSearchSql['strCheckedOutPastDueSql'] = "";
}
$dttNow = new QDateTime(QDateTime::Now);
// This uses a subquery, and as such cannot be converted to QQuery without hacking as of 2/22/07
$arrSearchSql['strCheckedOutPastDueSql'] .= sprintf("\nAND (SELECT `asset_transaction_checkout`.`due_date` FROM `asset_transaction` LEFT JOIN `asset_transaction_checkout` ON `asset_transaction`.`asset_transaction_id` = `asset_transaction_checkout`.`asset_transaction_id` WHERE `asset_transaction`.`asset_id` = `asset`.`asset_id` ORDER BY `asset_transaction`.`creation_date` DESC LIMIT 0,1)<'%s'", $dttNow->format('Y-m-d h:i:s'));
}
if ($strDateModified) {
if ($strDateModified == "before" && $strDateModifiedFirst instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
$arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`asset`.`%s`) < %s", $strModifiedCreated, $strDateModifiedFirst);
} elseif ($strDateModified == "after" && $strDateModifiedFirst instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
$arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`asset`.`%s`) > %s", $strModifiedCreated, $strDateModifiedFirst);
} elseif ($strDateModified == "between" && $strDateModifiedFirst instanceof QDateTime && $strDateModifiedLast instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
// Added 86399 (23 hrs., 59 mins., 59 secs) because the After variable needs to include the date given
// When only a date is given, conversion to a timestamp assumes 12:00am
$strDateModifiedLast = QApplication::$Database[1]->SqlVariable($strDateModifiedLast->Timestamp, false) + 86399;
$arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`asset`.`%s`) > %s", $strModifiedCreated, $strDateModifiedFirst);
$arrSearchSql['strDateModifiedSql'] .= sprintf("\nAND UNIX_TIMESTAMP(`asset`.`%s`) < %s", $strModifiedCreated, $strDateModifiedLast);
}
}
if ($blnAttachment) {
$arrSearchSql['strAttachmentSql'] = sprintf("AND attachment.attachment_id IS NOT NULL");
}
if (!$blnIncludeTBR) {
$arrSearchSql['strIncludeTBRSql'] = sprintf("AND `asset`.`location_id`!='5'");
}
if (!$blnIncludeShipped) {
$arrSearchSql['strIncludeShippedSql'] = sprintf("AND `asset`.`location_id`!='2'");
}
if (!$blnIncludeArchived) {
$arrSearchSql['strArchivedSql'] = sprintf("AND `asset`.`archived_flag` IS NOT TRUE");
}
if ($arrCustomFields) {
$arrSearchSql['strCustomFieldsSql'] = CustomField::GenerateSearchHelperSql($arrCustomFields, EntityQtype::Asset);
}
// Generate Authorization SQL based on the QApplication::$objRoleModule
$arrSearchSql['strAuthorizationSql'] = QApplication::AuthorizationSql('asset');
return $arrSearchSql;
/* This is what the SQL looks like for custom fields
SELECT
COUNT(asset.asset_id) AS row_count
FROM
`asset` AS `asset`
LEFT JOIN `asset_model` AS `asset__asset_model_id` ON `asset`.`asset_model_id` = `asset__asset_model_id`.`asset_model_id`
LEFT JOIN `category` AS `asset__asset_model_id__category_id` ON `asset__asset_model_id`.`category_id` = `asset__asset_model_id__category_id`.`category_id`
LEFT JOIN `manufacturer` AS `asset__asset_model_id__manufacturer_id` ON `asset__asset_model_id`.`manufacturer_id` = `asset__asset_model_id__manufacturer_id`.`manufacturer_id`
LEFT JOIN `location` AS `asset__location_id` ON `asset`.`location_id` = `asset__location_id`.`location_id`
LEFT JOIN `custom_field_selection` AS `custom_field_selection_1` ON `asset`.`asset_id` = `custom_field_selection_1` . `entity_id`
LEFT JOIN `custom_field_value` AS `custom_field_value_1` ON `custom_field_selection_1` . `custom_field_value_id` = `custom_field_value_1` . `custom_field_value_id`
LEFT JOIN `custom_field_selection` AS `custom_field_selection_5` ON `asset`.`asset_id` = `custom_field_selection_5` . `entity_id`
LEFT JOIN `custom_field_value` AS `custom_field_value_5` ON `custom_field_selection_5` . `custom_field_value_id` = `custom_field_value_5` . `custom_field_value_id`
WHERE
1=1
AND `custom_field_value_1` . `custom_field_id` = 1
AND `custom_field_value_1` . `short_description` LIKE '%1%'
AND `custom_field_value_5` . `custom_field_id` = 5
AND `custom_field_value_5` . `custom_field_value_id` = 6
*/
}
/**
* Construct a QDateTime. Does a few things differently than the php version:
* - Always stores timestamps in local or given timezone, so time extraction is easy
* - Has settings to determine if you want a date only or time only type
* - Will NOT throw exceptions. Errors simply result in a null datetime.
*
* @param null|integer|string|QDateTime|DateTime $mixValue
* @param DateTimeZone $objTimeZone
* @param int $intType
*
* @throws QCallerException
*/
public function __construct($mixValue = null, DateTimeZone $objTimeZone = null, $intType = QDateTime::UnknownType)
{
if ($mixValue instanceof QDateTime) {
// Cloning from another QDateTime object
if ($objTimeZone) {
throw new QCallerException('QDateTime cloning cannot take in a DateTimeZone parameter');
}
parent::__construct($mixValue->format('Y-m-d H:i:s'), $mixValue->GetTimeZone());
$this->blnDateNull = $mixValue->IsDateNull();
$this->blnTimeNull = $mixValue->IsTimeNull();
$this->ReinforceNullProperties();
} else {
if ($mixValue instanceof DateTime) {
// Subclassing from a PHP DateTime object
if ($objTimeZone) {
throw new QCallerException('QDateTime subclassing of a DateTime object cannot take in a DateTimeZone parameter');
}
parent::__construct($mixValue->format('Y-m-d H:i:s'), $mixValue->getTimezone());
// By definition, a DateTime object doesn't have anything nulled
$this->blnDateNull = false;
$this->blnTimeNull = false;
} else {
if (!$mixValue) {
// Set to "null date"
// And Do Nothing Else -- Default Values are already set to Nulled out
parent::__construct('2000-01-01 00:00:00', $objTimeZone);
} else {
if (strtolower($mixValue) == QDateTime::Now) {
// very common, so quickly deal with now string
parent::__construct('now', $objTimeZone);
$this->blnDateNull = false;
$this->blnTimeNull = false;
} else {
if (substr($mixValue, 0, 1) == '@') {
// unix timestamp. PHP superclass will always store ts in UTC. Our class will store in given timezone, or local tz
parent::__construct(date('Y-m-d H:i:s', substr($mixValue, 1)), $objTimeZone);
$this->blnDateNull = false;
$this->blnTimeNull = false;
} else {
// string relative date or time
if ($intTime = strtotime($mixValue)) {
// The documentation states that:
// The valid range of a timestamp is typically from
// Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT.
// (These are the dates that correspond to the minimum and maximum values
// for a 32-bit signed integer).
//
// But experimentally, 0000-01-01 00:00:00 is the least date displayed correctly
if ($intTime < -62167241486) {
// Set to "null date"
// And Do Nothing Else -- Default Values are already set to Nulled out
parent::__construct('2000-01-01 00:00:00', $objTimeZone);
} else {
parent::__construct(date('Y-m-d H:i:s', $intTime), $objTimeZone);
$this->blnDateNull = false;
$this->blnTimeNull = false;
}
} else {
// error
parent::__construct();
$this->blnDateNull = true;
$this->blnTimeNull = true;
}
}
}
}
}
}
// User is requesting to force a particular type.
switch ($intType) {
case QDateTime::DateOnlyType:
$this->blnTimeNull = true;
$this->ReinforceNullProperties();
return;
case QDateTime::TimeOnlyType:
$this->blnDateNull = true;
$this->ReinforceNullProperties();
return;
case QDateTime::DateAndTimeType:
// forcing both a date and time type to not be null
$this->blnDateNull = false;
$this->blnTimeNull = false;
break;
default:
break;
}
}
