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::GenerateSearchHelperSql($arrCustomFields, EntityQtype::Receipt);
}
// Generate Authorization SQL based on the QApplication::$objRoleModule
$arrSearchSql['strAuthorizationSql'] = QApplication::AuthorizationSql(EntityQtype::ToStringTable(EntityQtype::Receipt));
return $arrSearchSql;
}
/**
* This function returns the SQL necessary to select an entitie's primary key based on the EntityQtypeId
* This will not work for the GenerateSql function in CustomField.class.php because of the difference in the ``
*
* @param integer $intEntityQtypeId
* @return sting $strPrimaryKeySql
*/
public static function ToStringPrimaryKeySql($intEntityQtypeId)
{
$strTable = EntityQtype::ToStringTable($intEntityQtypeId);
$strToReturn = sprintf('`%s`.`%s_id`', $strTable, $strTable);
return $strToReturn;
}
/**
* This method will update the CustomFieldSelections for one Required Custom Field
*
*/
public function UpdateRequiredFieldSelections()
{
$objEntityQtypeCustomFieldArray = EntityQtypeCustomField::LoadArrayByCustomFieldId($this->CustomFieldId);
if ($objEntityQtypeCustomFieldArray) {
foreach ($objEntityQtypeCustomFieldArray as $objEntityQtypeCustomField) {
$strEntity = EntityQtype::ToStringPrimaryKeySql($objEntityQtypeCustomField->EntityQtypeId);
$strEntityTable = EntityQtype::ToStringTable($objEntityQtypeCustomField->EntityQtypeId);
// This query returns entities which do not have a custom_field_selection for this specific Custom Field/Entity QType combination
$strQuery = sprintf("\n\t\t\t\t\tSELECT %s AS entity_id\n\t\t\t\t\tFROM %s\n\t\t\t\t\tLEFT JOIN (custom_field_selection JOIN custom_field_value ON custom_field_selection.custom_field_value_id = custom_field_value.custom_field_value_id AND custom_field_value.custom_field_id = %s) ON %s = custom_field_selection.entity_id AND custom_field_selection.entity_qtype_id = %s\n\t\t\t\t\tWHERE custom_field_selection.custom_field_selection_id IS NULL", $strEntity, $strEntityTable, $this->CustomFieldId, $strEntity, $objEntityQtypeCustomField->EntityQtypeId);
$objDatabase = QApplication::$Database[1];
$objDbResult = $objDatabase->Query($strQuery);
while ($mixRow = $objDbResult->FetchArray()) {
// If it is not a SELECT custom field, then create a new CustomFieldValue
if ($this->CustomFieldQtypeId != 2) {
$objCustomFieldValue = new CustomFieldValue();
$objCustomFieldValue->CustomFieldId = $this->CustomFieldId;
$objCustomFieldValue->ShortDescription = $this->DefaultCustomFieldValue->ShortDescription;
$objCustomFieldValue->Save();
$intCustomFieldValueId = $objCustomFieldValue->CustomFieldValueId;
} else {
$intCustomFieldValueId = $this->DefaultCustomFieldValueId;
}
// Create the new CustomFieldSelection for this Entity Qtype/Entity Id/Custom Field Id
$objCustomFieldSelection = new CustomFieldSelection();
$objCustomFieldSelection->CustomFieldValueId = $intCustomFieldValueId;
$objCustomFieldSelection->EntityQtypeId = $objEntityQtypeCustomField->EntityQtypeId;
$objCustomFieldSelection->EntityId = $mixRow['entity_id'];
$objCustomFieldSelection->Save();
}
}
}
}
