protected static function GenerateSearchSql($intCategoryId = null, $intManufacturerId = null, $strDescription = null, $strAssetModelCode = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null)
{
$arrSearchSql = array("strCategorySql" => "", "strManufacturerSql" => "", "strDescriptionSql" => "", "strAssetModelCodeSql" => "", "strCustomFieldsSql" => "", "strDateModifiedSql" => "", "strAttachmentSql" => "", "strAuthorizationSql" => "");
if ($intCategoryId) {
// Properly Escape All Input Parameters using Database->SqlVariable()
$intCategoryId = QApplication::$Database[1]->SqlVariable($intCategoryId, true);
$arrSearchSql['strCategorySql'] = sprintf("AND `asset_model__category_id`.`category_id`%s", $intCategoryId);
}
if ($intManufacturerId) {
$intManufacturerId = QApplication::$Database[1]->SqlVariable($intManufacturerId, true);
$arrSearchSql['strManufacturerSql'] = sprintf("AND `asset_model__manufacturer_id`.`manufacturer_id`%s", $intManufacturerId);
}
if ($strDescription) {
$strDescription = QApplication::$Database[1]->SqlVariable("%" . $strDescription . "%", false);
$arrSearchSql['strDescriptionSql'] = "AND ( `asset_model`.`short_description` LIKE {$strDescription} OR `asset_model`.`long_description` LIKE {$strDescription} )";
}
if ($strAssetModelCode) {
$strAssetModelCode = QApplication::$Database[1]->SqlVariable("%" . $strAssetModelCode . "%", false);
$arrSearchSql['strAssetModelCodeSql'] = "AND `asset_model`.`asset_model_code` LIKE {$strAssetModelCode}";
}
if ($blnAttachment) {
$arrSearchSql['strAttachmentSql'] = sprintf("AND attachment.attachment_id IS NOT NULL");
}
if ($arrCustomFields) {
$arrSearchSql['strCustomFieldsSql'] = CustomField::GenerateSearchSql($arrCustomFields);
}
if ($strDateModified) {
if ($strDateModified == "before" && $strDateModifiedFirst instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
$arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`asset_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(`asset_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(`asset_model`.`modified_date`) > %s", $strDateModifiedFirst);
$arrSearchSql['strDateModifiedSql'] .= sprintf("\nAND UNIX_TIMESTAMP(`asset_model`.`modified_date`) < %s", $strDateModifiedLast);
}
}
// Generate Authorization SQL based on the QApplication::$objRoleModule
$arrSearchSql['strAuthorizationSql'] = QApplication::AuthorizationSql('asset_model');
return $arrSearchSql;
}
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::GenerateSearchSql($arrCustomFields);
}
// Generate Authorization SQL based on the QApplication::$objRoleModule
$arrSearchSql['strAuthorizationSql'] = QApplication::AuthorizationSql('REPLACE!!!!');
return $arrSearchSql;
}
protected static function GenerateSearchSql($strFromCompany = null, $strFromContact = null, $strReceiptNumber = null, $strAssetCode = null, $strInventoryModelCode = null, $intStatus = null, $strNote = null, $strDueDate = null, $strReceiptDate = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null)
{
$arrSearchSql = array("strFromCompanySql" => "", "strFromContactSql" => "", "strReceiptNumberSql" => "", "strAssetCodeFromSql" => "", "strAssetCodeSql" => "", "strInventoryModelCodeFromSql" => "", "strInventoryModelCodeSql" => "", "strStatusSql" => "", "strNoteSql" => "", "strDueDateSql" => "", "strReceiptDateSql" => "", "strCustomFieldsSql" => "", "strDateModifiedSql" => "", "strAttachmentSql" => "", "strAuthorizationSql" => "");
if ($strFromCompany) {
// Properly Escape All Input Parameters using Database->SqlVariable()
$strFromCompany = QApplication::$Database[1]->SqlVariable("%" . $strFromCompany . "%", false);
$arrSearchSql['strFromCompanySql'] = "AND `receipt__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 (`receipt__from_contact_id` . `first_name` LIKE {$strFromContact}";
$arrSearchSql['strFromContactSql'] .= " OR `receipt__from_contact_id` . `last_name` LIKE {$strFromContact}";
$arrSearchSql['strFromContactSql'] .= " OR CONCAT(`receipt__from_contact_id` . `first_name`, ' ', `receipt__from_contact_id` . `last_name`) LIKE {$strFromContact})";
}
if ($strReceiptNumber) {
// Properly Escape All Input Parameters using Database->SqlVariable()
$strReceiptNumber = QApplication::$Database[1]->SqlVariable("%" . $strReceiptNumber . "%", false);
$arrSearchSql['strReceiptNumberSql'] = "AND `receipt` . `receipt_number` LIKE {$strReceiptNumber}";
}
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 `receipt` . `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 `receipt` . `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 `receipt` . `received_flag` = false";
} elseif ($intStatus == 2) {
$intStatus = QApplication::$Database[1]->SqlVariable($intStatus, true);
$arrSearchSql['strStatusSql'] = "AND `receipt` . `received_flag` = true";
}
}
if ($strNote) {
$strNote = QApplication::$Database[1]->SqlVariable("%" . $strNote . "%", false);
$arrSearchSql['strNoteSql'] = "AND `note` LIKE {$strNote}";
}
if ($strDueDate) {
$strDueDate = QApplication::$Database[1]->SqlVariable($strDueDate, false);
$arrSearchSql['strDueDateSql'] = sprintf("AND `receipt`.`due_date` = %s", $strDueDate);
}
if ($strReceiptDate) {
$strReceiptDate = QApplication::$Database[1]->SqlVariable($strReceiptDate, false);
$arrSearchSql['strReceiptDateSql'] = sprintf("AND `receipt`.`receipt_date` = %s", $strReceiptDate);
}
if ($strDateModified) {
if ($strDateModified == "before" && $strDateModifiedFirst instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
$arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`receipt`.`modified_date`) < %s", $strDateModifiedFirst);
} elseif ($strDateModified == "after" && $strDateModifiedFirst instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
$arrSearchSql['strDateModifiedSql'] = sprintf("AND UNIX_TIMESTAMP(`receipt`.`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(`receipt`.`modified_date`) > %s", $strDateModifiedFirst);
$arrSearchSql['strDateModifiedSql'] .= sprintf("\nAND UNIX_TIMESTAMP(`receipt`.`modified_date`) < %s", $strDateModifiedLast);
}
}
if ($blnAttachment) {
$arrSearchSql['strAttachmentSql'] = sprintf("AND attachment.attachment_id IS NOT NULL");
}
if ($arrCustomFields) {
$arrSearchSql['strCustomFieldsSql'] = CustomField::GenerateSearchSql($arrCustomFields);
}
// Generate Authorization SQL based on the QApplication::$objRoleModule
$arrSearchSql['strAuthorizationSql'] = QApplication::AuthorizationSql(6);
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()
if (strpos($strInventoryModelCode, ",") != false) {
$tmp = explode(",", $strInventoryModelCode);
$tmp2 = "";
foreach ($tmp as $tmp1) {
$strInventoryModelCode = QApplication::$Database[1]->SqlVariable("%" . $tmp1 . "%", false);
$tmp2 .= "`inventory_model` . `inventory_model_code` LIKE {$strInventoryModelCode} OR ";
}
$arrSearchSql['strInventoryModelCodeSql'] = "AND (" . substr($tmp2, 0, -4) . ")";
} elseif (strpos($strInventoryModelCode, " ") != false) {
$tmp = explode(" ", $strInventoryModelCode);
$tmp2 = "";
foreach ($tmp as $tmp1) {
$strInventoryModelCode = QApplication::$Database[1]->SqlVariable("%" . $tmp1 . "%", false);
$tmp2 .= "`inventory_model` . `inventory_model_code` LIKE {$strInventoryModelCode} OR ";
}
$arrSearchSql['strInventoryModelCodeSql'] = "AND (" . substr($tmp2, 0, -4) . ")";
} else {
$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 (strpos($strShortDescription, " ") != false) {
$tmp = explode(" ", $strShortDescription);
$tmp2 = "";
foreach ($tmp as $tmp1) {
$strShortDescription = QApplication::$Database[1]->SqlVariable("%" . $tmp1 . "%", false);
$tmp2 .= "`inventory_model` . `short_description` LIKE {$strShortDescription} AND ";
}
$arrSearchSql['strShortDescriptionSql'] = "AND (" . substr($tmp2, 0, -4) . ")";
} elseif ($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::GenerateSearchSql($arrCustomFields);
}
// Generate Authorization SQL based on the QApplication::$objRoleModule
$arrSearchSql['strAuthorizationSql'] = QApplication::AuthorizationSql('inventory_model');
//print_r($arrSearchSql);
return $arrSearchSql;
}
