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; }