/** * Deletes all associated ChildAssetTransactions * @return void */ public function DeleteAllChildAssetTransactions() { if (is_null($this->intAssetTransactionId)) { throw new QUndefinedPrimaryKeyException('Unable to call UnassociateChildAssetTransaction on this unsaved AssetTransaction.'); } // Get the Database Object for this Class $objDatabase = AssetTransaction::GetDatabase(); // Journaling if ($objDatabase->JournalingDatabase) { foreach (AssetTransaction::LoadArrayByParentAssetTransactionId($this->intAssetTransactionId) as $objAssetTransaction) { $objAssetTransaction->Journal('DELETE'); } } // Perform the SQL Query $objDatabase->NonQuery(' DELETE FROM `asset_transaction` WHERE `parent_asset_transaction_id` = ' . $objDatabase->SqlVariable($this->intAssetTransactionId) . ' '); }
/** * 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); }