/**
* Generate the SQL for a list page to include attachments as virtual attributes (add __ before an alias to make a virutal attribute)
* The virtual attributes can then be accessed by $objAsset->GetVirtualAttribute('name_of_attribute') where the name doesn't include the __
* This method was added so that attachments can be added to the customizable datagrids
*
* @param integer $intEntityQtypeId
* @return array $arrAttachmentSql - with three elements: strSelect, strFrom, and strGroupBy which are to be included in a SQL statement
*/
public static function GenerateSql($intEntityQtypeId)
{
$arrAttachmentSql = array();
$arrAttachmentSql['strSelect'] = ', COUNT(DISTINCT `attachment`.`attachment_id`) AS `__attachment_count`';
$arrAttachmentSql['strFrom'] = sprintf('LEFT JOIN `attachment` ON (`attachment`.`entity_qtype_id` = %s AND `attachment`.`entity_id` = %s)', $intEntityQtypeId, EntityQtype::ToStringPrimaryKeySql($intEntityQtypeId));
$arrAttachmentSql['strGroupBy'] = sprintf('GROUP BY %s', EntityQtype::ToStringPrimaryKeySql($intEntityQtypeId));
return $arrAttachmentSql;
}
/**
* 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);
$arrHelperTable = CustomField::ToStringHelperTable($objEntityQtypeCustomField->EntityQtypeId);
// This query returns entities which do not have a custom_field_selection for this specific Custom Field/Entity QType combination
/*$strQuery = sprintf("
SELECT %s AS entity_id
FROM %s
LEFT 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
WHERE custom_field_selection.custom_field_selection_id IS NULL"
, $strEntity, $strEntityTable, $this->CustomFieldId, $strEntity, $objEntityQtypeCustomField->EntityQtypeId);*/
$strQuery = sprintf("\n\t\t\t\t\tSELECT %s AS entity_id\n\t\t\t\t\tFROM %s\n\t\t\t\t\tWHERE cfv_%s IS NULL OR cfv_%s = ''", $arrHelperTable['strPrimaryKey'], $arrHelperTable['strHelperTable'], $this->CustomFieldId, $this->CustomFieldId);
$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();
}
}
}
}