Exemple #1
0
 protected static function GenerateSearchSql($strFirstName, $strLastName = null, $strCompany = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null)
 {
     $arrSearchSql = array("strFirstNameSql" => "", "strLastNameSql" => "", "strCompanySql" => "", "strCustomFieldsSql" => "", "strDateModifiedSql" => "", "strAttachmentSql" => "", "strAuthorizationSql" => "");
     if ($strFirstName) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strFirstName = QApplication::$Database[1]->SqlVariable("%" . $strFirstName . "%", false);
         $arrSearchSql['strFirstNameSql'] = "AND `contact` . `first_name` LIKE {$strFirstName}";
     }
     if ($strLastName) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strLastName = QApplication::$Database[1]->SqlVariable("%" . $strLastName . "%", false);
         $arrSearchSql['strLastNameSql'] = "AND `contact` . `last_name` LIKE {$strLastName}";
     }
     if ($strCompany) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strCompany = QApplication::$Database[1]->SqlVariable("%" . $strCompany . "%", false);
         $arrSearchSql['strCompanySql'] = "AND `contact__company_id` . `short_description` LIKE {$strCompany}";
     }
     if ($strDateModified) {
         if ($strDateModified == "before" && $strDateModifiedFirst instanceof QDateTime) {
             $strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
             $arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`contact`.`modified_date`) < %s", $strDateModifiedFirst);
         } elseif ($strDateModified == "after" && $strDateModifiedFirst instanceof QDateTime) {
             $strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
             $arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`contact`.`modified_date`) > %s", $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(`contact`.`modified_date`) > %s", $strDateModifiedFirst);
             $arrSearchSql['strDateModifiedSql'] .= sprintf("\nAND UNIX_TIMESTAMP(`contact`.`modified_date`) < %s", $strDateModifiedLast);
         }
     }
     if ($blnAttachment) {
         $arrSearchSql['strAttachmentSql'] = sprintf("AND attachment.attachment_id IS NOT NULL");
     }
     if ($arrCustomFields) {
         $arrSearchSql['strCustomFieldsSql'] = CustomField::GenerateSearchHelperSql($arrCustomFields, EntityQtype::Contact);
     }
     // Generate Authorization SQL based on the QApplication::$objRoleModule
     $arrSearchSql['strAuthorizationSql'] = QApplication::AuthorizationSql('REPLACE!!!!');
     return $arrSearchSql;
 }
Exemple #2
0
 protected static function GenerateSearchSql($strToCompany = null, $strToContact = null, $strFromCompany = null, $strFromContact = null, $strShipmentNumber = null, $strAssetCode = null, $strInventoryModelCode = null, $intStatus = null, $strTrackingNumber = null, $intCourierId = null, $strNote = null, $strShipmentDate = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null)
 {
     $arrSearchSql = array("strToCompanySql" => "", "strToContactSql" => "", "strFromCompanySql" => "", "strFromContactSql" => "", "strShipmentNumberSql" => "", "strAssetCodeFromSql" => "", "strAssetCodeSql" => "", "strInventoryModelCodeFromSql" => "", "strInventoryModelCodeSql" => "", "strStatusSql" => "", "strTrackingNumberSql" => "", "strCourierSql" => "", "strNoteSql" => "", "strShipmentDateSql" => "", "strCustomFieldsSql" => "", "strDateModifiedSql" => "", "strAttachmentSql" => "");
     if ($strToCompany) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strToCompany = QApplication::$Database[1]->SqlVariable("%" . $strToCompany . "%", false);
         $arrSearchSql['strToCompanySql'] = "AND `shipment__to_company_id` . `short_description` LIKE {$strToCompany}";
     }
     if ($strToContact) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strToContact = QApplication::$Database[1]->SqlVariable("%" . $strToContact . "%", false);
         $arrSearchSql['strToContactSql'] = "AND (`shipment__to_contact_id` . `first_name` LIKE {$strToContact}";
         $arrSearchSql['strToContactSql'] .= " OR `shipment__to_contact_id` . `last_name` LIKE {$strToContact}";
         $arrSearchSql['strToContactSql'] .= " OR CONCAT(`shipment__to_contact_id` . `first_name`, ' ', `shipment__to_contact_id` . `last_name`) LIKE {$strToContact})";
     }
     if ($strFromCompany) {
         // Properly escape all input parameters using Database->SqlVariable()
         $strFromCompany = QApplication::$Database[1]->SqlVariable("%" . $strFromCompany . "%", false);
         $arrSearchSql['strFromCompanySql'] = "AND `shipment__from_company_id` . `short_description` LIKE {$strFromCompany}";
     }
     if ($strFromContact) {
         // Properly escape all input parameters using Database->SqlVariable()
         $strFromContact = QApplication::$Database[1]->SqlVariable("%" . $strFromContact . "%", false);
         $arrSearchSql['strFromContactSql'] = "AND (`shipment__from_contact_id` . `first_name` LIKE {$strFromContact}";
         $arrSearchSql['strFromContactSql'] .= " OR `shipment__from_contact_id` . `last_name` LIKE {$strFromContact}";
         $arrSearchSql['strFromContactSql'] .= " OR CONCAT(`shipment__from_contact_id` . `first_name`, ' ', `shipment__from_contact_id` . `last_name`) LIKE {$strFromContact})";
     }
     if ($strShipmentNumber) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strShipmentNumber = QApplication::$Database[1]->SqlVariable("%" . $strShipmentNumber . "%", false);
         $arrSearchSql['strShipmentNumberSql'] = "AND `shipment` . `shipment_number` LIKE {$strShipmentNumber}";
     }
     if ($strAssetCode) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strAssetCode = QApplication::$Database[1]->SqlVariable("%" . $strAssetCode . "%", false);
         $arrSearchSql['strAssetCodeFromSql'] = ",`asset_transaction`, `asset`";
         $arrSearchSql['strAssetCodeSql'] = "AND `shipment` . `transaction_id`=`asset_transaction`.`transaction_id` AND `asset_transaction`.`asset_id`=`asset`.`asset_id` AND `asset`.`asset_code` LIKE {$strAssetCode}";
     }
     if ($strInventoryModelCode) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strInventoryModelCode = QApplication::$Database[1]->SqlVariable("%" . $strInventoryModelCode . "%", false);
         $arrSearchSql['strInventoryModelCodeFromSql'] = ",`inventory_transaction`, `inventory_location`, `inventory_model`";
         $arrSearchSql['strInventoryModelCodeSql'] = "AND `shipment` . `transaction_id`=`inventory_transaction`.`transaction_id` AND `inventory_transaction`.`inventory_location_id`=`inventory_location`.`inventory_location_id` AND `inventory_location`.`inventory_model_id`=`inventory_model`.`inventory_model_id` AND `inventory_model`.`inventory_model_code` LIKE {$strInventoryModelCode}";
     }
     if ($intStatus) {
         // Pending
         if ($intStatus == 1) {
             $intStatus = QApplication::$Database[1]->SqlVariable($intStatus, true);
             $arrSearchSql['strStatusSql'] = "AND `shipment` . `shipped_flag` = false";
         } elseif ($intStatus == 2) {
             $intStatus = QApplication::$Database[1]->SqlVariable($intStatus, true);
             $arrSearchSql['strStatusSql'] = "AND `shipment` . `shipped_flag` = true";
         }
     }
     if ($strTrackingNumber) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strTrackingNumber = QApplication::$Database[1]->SqlVariable("%" . $strTrackingNumber . "%", false);
         $arrSearchSql['strTrackingNumberSql'] = "AND `shipment` . `tracking_number` LIKE {$strTrackingNumber}";
     }
     if ($intCourierId) {
         $intCourierId = QApplication::$Database[1]->SqlVariable($intCourierId, true);
         $arrSearchSql['strCourierSql'] = "AND `shipment` . `courier_id` {$intCourierId}";
     }
     if ($strNote) {
         $strNote = QApplication::$Database[1]->SqlVariable("%" . $strNote . "%", false);
         $arrSearchSql['strNoteSql'] = "AND `note` LIKE {$strNote}";
     }
     if ($strShipmentDate instanceof DateTime) {
         $strShipmentDate = QApplication::$Database[1]->SqlVariable($strShipmentDate, false);
         $arrSearchSql['strShipmentDateSql'] = sprintf("AND `shipment`.`ship_date` = %s", $strShipmentDate);
     }
     if ($strDateModified) {
         if ($strDateModified == "before" && $strDateModifiedFirst instanceof QDateTime) {
             $strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
             $arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`shipment`.`modified_date`) < %s", $strDateModifiedFirst);
         } elseif ($strDateModified == "after" && $strDateModifiedFirst instanceof QDateTime) {
             $strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
             $arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`shipment`.`modified_date`) > %s", $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(`shipment`.`modified_date`) > %s", $strDateModifiedFirst);
             $arrSearchSql['strDateModifiedSql'] .= sprintf("\nAND UNIX_TIMESTAMP(`shipment`.`modified_date`) < %s", $strDateModifiedLast);
         }
     }
     if ($blnAttachment) {
         $arrSearchSql['strAttachmentSql'] = sprintf("AND attachment.attachment_id IS NOT NULL");
     }
     if ($arrCustomFields) {
         $arrSearchSql['strCustomFieldsSql'] = CustomField::GenerateSearchHelperSql($arrCustomFields, EntityQtype::Shipment);
     }
     // Generate Authorization SQL based on the QApplication::$objRoleModule
     $arrSearchSql['strAuthorizationSql'] = QApplication::AuthorizationSql('shipment');
     return $arrSearchSql;
 }
 /**
  * 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 InventoryModel::LoadArrayBySearch() and InventoryModel::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 $strInventoryModelCode
  * @param int $intLocationId
  * @param int $intInventoryModelId
  * @param int $intCategoryId
  * @param int $intManufacturerId
  * @param string $strShortDescription
  * @return array with seven keys, strInventoryModelCodeSql, strLocationSql, strInventoryModelSql, strCategorySql, strManufacturerSql, strShortDescriptionSql
  */
 protected static function GenerateSearchSql($strInventoryModelCode = null, $intLocationId = null, $intInventoryModelId = null, $intCategoryId = null, $intManufacturerId = null, $strShortDescription = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null)
 {
     // Define all indexes for the array to be returned
     $arrSearchSql = array("strInventoryModelCodeSql" => "", "strLocationSql" => "", "strLocationsFromSql" => "", "strInventoryModelSql" => "", "strCategorySql" => "", "strManufacturerSql" => "", "strShortDescriptionSql" => "", "strCustomFieldsSql" => "", "strDateModifiedSql" => "", "strAttachmentSql" => "", "strAuthorizationSql" => "");
     if ($strInventoryModelCode) {
         // Properly Escape All Input Parameters using Database->SqlVariable()
         $strInventoryModelCode = QApplication::$Database[1]->SqlVariable("%" . $strInventoryModelCode . "%", false);
         $arrSearchSql['strInventoryModelCodeSql'] = "AND `inventory_model` . `inventory_model_code` LIKE {$strInventoryModelCode}";
     }
     if ($intLocationId) {
         $intLocationId = QApplication::$Database[1]->SqlVariable($intLocationId, true);
         $arrSearchSql['strLocationsFromSql'] = ", inventory_location";
         $arrSearchSql['strLocationSql'] = "AND `inventory_model` . `inventory_model_id` = `inventory_location` . `inventory_model_id`";
         $arrSearchSql['strLocationSql'] = sprintf("\nAND `inventory_location` . `location_id`%s", $intLocationId);
     }
     if ($intInventoryModelId) {
         $intInventoryModelId = QApplication::$Database[1]->SqlVariable($intInventoryModelId, true);
         $arrSearchSql['strInventoryModelSql'] = sprintf("AND `inventory_model` . `inventory_model_id`%s", $intInventoryModelId);
     }
     if ($intCategoryId) {
         $intCategoryId = QApplication::$Database[1]->SqlVariable($intCategoryId, true);
         $arrSearchSql['strCategorySql'] = sprintf("AND `inventory_model`.`category_id`%s", $intCategoryId);
     }
     if ($intManufacturerId) {
         $intManufacturerId = QApplication::$Database[1]->SqlVariable($intManufacturerId, true);
         $arrSearchSql['strManufacturerSql'] = sprintf("AND `inventory_model`.`manufacturer_id`%s", $intManufacturerId);
     }
     if ($strShortDescription) {
         $strShortDescription = QApplication::$Database[1]->SqlVariable("%" . $strShortDescription . "%", false);
         $arrSearchSql['strShortDescriptionSql'] = "AND `inventory_model`.`short_description` LIKE {$strShortDescription}";
     }
     if ($strDateModified) {
         if ($strDateModified == "before" && $strDateModifiedFirst instanceof QDateTime) {
             $strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
             $arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`inventory_model`.`modified_date`) < %s", $strDateModifiedFirst);
         } elseif ($strDateModified == "after" && $strDateModifiedFirst instanceof QDateTime) {
             $strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
             $arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`inventory_model`.`modified_date`) > %s", $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(`inventory_model`.`modified_date`) > %s", $strDateModifiedFirst);
             $arrSearchSql['strDateModifiedSql'] .= sprintf("\nAND UNIX_TIMESTAMP(`inventory_model`.`modified_date`) < %s", $strDateModifiedLast);
         }
     }
     if ($blnAttachment) {
         $arrSearchSql['strAttachmentSql'] = sprintf("AND attachment.attachment_id IS NOT NULL");
     }
     if ($arrCustomFields) {
         $arrSearchSql['strCustomFieldsSql'] = CustomField::GenerateSearchHelperSql($arrCustomFields, EntityQtype::Inventory);
     }
     // Generate Authorization SQL based on the QApplication::$objRoleModule
     $arrSearchSql['strAuthorizationSql'] = QApplication::AuthorizationSql('inventory_model');
     return $arrSearchSql;
 }
Exemple #4
0
 /**
  * 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
     			*/
 }