/**
* Count DISTINCT Transactions
* @param $strAssetModel
* @param $strAssetCode
* @param $strAssetModelCode
* @param $strUser
* @param $intCheckedOutBy
* @param $intReservedBy
* @param $strCategory
* @param $strManufacturer
* @param $strDateModified
* @param $strDateModifiedFirst
* @param $strDateModifiedLast
* @param $arrTransactionTypes
* @param $objExpansionMap
* @return int
*/
public function CountTransactionsBySearch($strAssetModel = null, $strAssetCode = null, $strAssetModelCode = null, $strUser = null, $intCheckedOutBy = null, $intReservedBy = null, $strCategory = null, $strManufacturer = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $arrTransactionTypes = null, $objExpansionMap = null)
{
// Setup QueryExpansion
$objQueryExpansion = new QQueryExpansion();
if ($objExpansionMap) {
try {
AssetTransaction::ExpandQuery('asset_transaction', null, $objExpansionMap, $objQueryExpansion);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
$strTransactionTypes = "";
if ($arrTransactionTypes) {
$strTransactionTypes = sprintf("AND `asset_transaction__transaction_id`.`transaction_type_id` IN ('%s') ", implode("', '", $arrTransactionTypes));
}
if ($strAssetModel) {
$strAssetModel = "AND `asset_transaction__asset_id__asset_model_id`.`short_description` LIKE '%" . $strAssetModel . "%'\n";
}
if ($strAssetCode) {
$strAssetCode = "AND `asset_transaction__asset_id`.`asset_code` LIKE '%" . $strAssetCode . "%'\n";
}
if ($strAssetModelCode) {
$strAssetModelCode = "AND `asset_transaction__asset_id__asset_model_id`.`asset_model_code` LIKE '%" . $strAssetModelCode . "%'\n";
}
if ($strUser) {
$strUser = sprintf("AND (`asset_transaction__transaction_id__created_by`.`user_account_id` = '%s' OR `asset_transaction__transaction_id__modified_by`.`user_account_id` = '%s')\n", $strUser, $strUser);
}
$strCheckedOutBy = "";
if ($intCheckedOutBy) {
$strCheckedOutBy = sprintf("AND `asset_transaction__asset_id`.`checked_out_flag` = true\n");
if ($intCheckedOutBy != 'any') {
$strCheckedOutBy .= sprintf("AND `asset_transaction`.`created_by` = '%s'\n", $intCheckedOutBy);
}
}
$strReservedBy = "";
if ($intReservedBy) {
$strReservedBy = sprintf("AND `asset_transaction__asset_id`.`reserved_flag` = true\n");
if ($intReservedBy != 'any') {
$strReservedBy .= sprintf("AND `asset_transaction`.`created_by` = '%s'\n", $intReservedBy);
}
}
if ($strCategory) {
$strCategory = sprintf("AND `asset_transaction__asset_id__asset_model_id`.`category_id` = '%s'\n", $strCategory);
}
if ($strManufacturer) {
$strManufacturer = sprintf("AND `asset_transaction__asset_id__asset_model_id`.`manufacturer_id` = '%s'\n", $strManufacturer);
}
$arrSearchSql['strDateModifiedSql'] = null;
if ($strDateModified) {
if ($strDateModified == "before" && $strDateModifiedFirst instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
$arrSearchSql['strDateModifiedSql'] = sprintf("AND (UNIX_TIMESTAMP(`asset_transaction`.`modified_date`) < %s OR UNIX_TIMESTAMP(`asset_transaction`.`creation_date`) < %s)\n", $strDateModifiedFirst, $strDateModifiedFirst);
} elseif ($strDateModified == "after" && $strDateModifiedFirst instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
$arrSearchSql['strDateModifiedSql'] = sprintf("AND (UNIX_TIMESTAMP(`asset_transaction`.`modified_date`) > %s OR UNIX_TIMESTAMP(`asset_transaction`.`creation_date`) > %s)\n", $strDateModifiedFirst, $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_transaction`.`modified_date`) > %s AND UNIX_TIMESTAMP(`asset_transaction`.`modified_date`) < %s", $strDateModifiedFirst, $strDateModifiedLast);
$arrSearchSql['strDateModifiedSql'] .= sprintf(" OR UNIX_TIMESTAMP(`asset_transaction`.`creation_date`) > %s AND UNIX_TIMESTAMP(`asset_transaction`.`creation_date`))\n", $strDateModifiedFirst, $strDateModifiedLast);
}
}
$arrCustomFieldSql = CustomField::GenerateSql(EntityQtype::Asset);
/* $strQuery = sprintf('
SELECT
COUNT(DISTINCT `asset_transaction`.`transaction_id`) AS row_count
FROM
`asset_transaction` AS `asset_transaction`
%s
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
OR `asset_transaction__asset_id`.`archived_flag` is TRUE
', $objQueryExpansion->GetFromSql("", "\n "), str_replace("`asset`.`asset_id`", " `asset_transaction__asset_id`.`asset_id`", $arrCustomFieldSql['strFrom']),
$strTransactionTypes, $strAssetModel, $strAssetCode, $strAssetModelCode, $strUser, $strCheckedOutBy, $strReservedBy, $strCategory, $strManufacturer, $arrSearchSql['strDateModifiedSql']
);*/
$strQuery = sprintf('
SELECT
COUNT(DISTINCT `asset_transaction`.`transaction_id`) AS row_count
FROM
`asset_transaction` AS `asset_transaction`
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
', $objQueryExpansion->GetFromSql("", "\n\t\t\t\t\t"), $strTransactionTypes, $strAssetModel, $strAssetCode, $strAssetModelCode, $strUser, $strCheckedOutBy, $strReservedBy, $strCategory, $strManufacturer, $arrSearchSql['strDateModifiedSql']);
//echo($strQuery); exit;
$objDatabase = AssetTransaction::GetDatabase();
$objDbResult = $objDatabase->Query($strQuery);
$strDbRow = $objDbResult->FetchRow();
return QType::Cast($strDbRow[0], QType::Integer);
}
/**
* Internally called method to assist with early binding of objects
* on load methods. Can only early-bind references that this class owns in the database.
* @param string $strParentAlias the alias of the parent (if any)
* @param string $strAlias the alias of this object
* @param array $objExpansionMap map of referenced columns to be immediately expanded via early-binding
* @param QueryExpansion an already instantiated QueryExpansion object (used as a utility object to assist with object expansion)
*/
public static function ExpandQuery($strParentAlias, $strAlias, $objExpansionMap, QQueryExpansion $objQueryExpansion)
{
if ($strAlias) {
$objQueryExpansion->AddFromItem(sprintf('LEFT JOIN `asset_transaction` AS `%s__%s` ON `%s`.`%s` = `%s__%s`.`asset_transaction_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`asset_transaction_id` AS `%s__%s__asset_transaction_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`asset_id` AS `%s__%s__asset_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`transaction_id` AS `%s__%s__transaction_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`parent_asset_transaction_id` AS `%s__%s__parent_asset_transaction_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`source_location_id` AS `%s__%s__source_location_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`destination_location_id` AS `%s__%s__destination_location_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`new_asset_flag` AS `%s__%s__new_asset_flag`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`new_asset_id` AS `%s__%s__new_asset_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`schedule_receipt_flag` AS `%s__%s__schedule_receipt_flag`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`schedule_receipt_due_date` AS `%s__%s__schedule_receipt_due_date`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`created_by` AS `%s__%s__created_by`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`creation_date` AS `%s__%s__creation_date`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`modified_by` AS `%s__%s__modified_by`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`modified_date` AS `%s__%s__modified_date`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$strParentAlias = $strParentAlias . '__' . $strAlias;
}
if (is_array($objExpansionMap)) {
foreach ($objExpansionMap as $strKey => $objValue) {
switch ($strKey) {
case 'asset_id':
try {
Asset::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'transaction_id':
try {
Transaction::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'parent_asset_transaction_id':
try {
AssetTransaction::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'source_location_id':
try {
Location::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'destination_location_id':
try {
Location::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'new_asset_id':
try {
Asset::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'created_by':
try {
UserAccount::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'modified_by':
try {
UserAccount::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
default:
throw new QCallerException(sprintf('Unknown Object to Expand in %s: %s', $strParentAlias, $strKey));
}
}
}
}
/**
* Internally called method to assist with early binding of objects
* on load methods. Can only early-bind references that this class owns in the database.
* @param string $strParentAlias the alias of the parent (if any)
* @param string $strAlias the alias of this object
* @param array $objExpansionMap map of referenced columns to be immediately expanded via early-binding
* @param QueryExpansion an already instantiated QueryExpansion object (used as a utility object to assist with object expansion)
*/
public static function ExpandQuery($strParentAlias, $strAlias, $objExpansionMap, QQueryExpansion $objQueryExpansion)
{
if ($strAlias) {
$objQueryExpansion->AddFromItem(sprintf('LEFT JOIN `asset_transaction_checkout` AS `%s__%s` ON `%s`.`%s` = `%s__%s`.`asset_transaction_checkout_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`asset_transaction_checkout_id` AS `%s__%s__asset_transaction_checkout_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`asset_transaction_id` AS `%s__%s__asset_transaction_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`to_contact_id` AS `%s__%s__to_contact_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`to_user_id` AS `%s__%s__to_user_id`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`due_date` AS `%s__%s__due_date`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`created_by` AS `%s__%s__created_by`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`creation_date` AS `%s__%s__creation_date`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`modified_by` AS `%s__%s__modified_by`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$objQueryExpansion->AddSelectItem(sprintf('`%s__%s`.`modified_date` AS `%s__%s__modified_date`', $strParentAlias, $strAlias, $strParentAlias, $strAlias));
$strParentAlias = $strParentAlias . '__' . $strAlias;
}
if (is_array($objExpansionMap)) {
foreach ($objExpansionMap as $strKey => $objValue) {
switch ($strKey) {
case 'asset_transaction_id':
try {
AssetTransaction::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'to_contact_id':
try {
Contact::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'to_user_id':
try {
UserAccount::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'created_by':
try {
UserAccount::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
case 'modified_by':
try {
UserAccount::ExpandQuery($strParentAlias, $strKey, $objValue, $objQueryExpansion);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
default:
throw new QCallerException(sprintf('Unknown Object to Expand in %s: %s', $strParentAlias, $strKey));
}
}
}
}
