public static function updateValueByDataIdAndOldValueAndNewValue($customFieldDataId, $oldValue, $newValue)
{
$quote = DatabaseCompatibilityUtil::getQuote();
$customFieldTableName = MultipleValuesCustomField::getTableName();
$baseCustomFieldTableName = BaseCustomField::getTableName();
$customFieldValueTableName = CustomFieldValue::getTableName();
$baseCustomFieldJoinColumnName = $baseCustomFieldTableName . '_id';
$valueAttributeColumnName = 'value';
$dataAttributeColumnName = static::getForeignKeyName('BaseCustomField', 'data');
$sql = "update {$quote}{$customFieldValueTableName}{$quote}, {$quote}{$customFieldTableName}{$quote}, ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote} ";
$sql .= "set {$quote}{$customFieldValueTableName}{$quote}.{$valueAttributeColumnName} = '{$newValue}' ";
$sql .= "where {$quote}{$customFieldTableName}{$quote}.{$baseCustomFieldJoinColumnName} = ";
// Not Coding Standard
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id ";
$sql .= "AND {$quote}{$dataAttributeColumnName}{$quote} = {$customFieldDataId} ";
$sql .= "AND {$quote}{$customFieldTableName}{$quote}.id = {$quote}{$customFieldValueTableName}{$quote}.{$customFieldTableName}_id ";
$sql .= "AND {$quote}{$customFieldValueTableName}{$quote}.{$valueAttributeColumnName} = '{$oldValue}'";
ZurmoRedBean::exec($sql);
}
/**
* @depends testSetAttributesWithPostForMultipleValuesCustomField
*/
public function testUpdateValueOnCustomFieldRows()
{
$values = array('A', 'B', 'C');
$customFieldData = CustomFieldData::getByName('updateItems');
$customFieldData->serializedData = serialize($values);
$this->assertTrue($customFieldData->save());
$id = $customFieldData->id;
$customField = new MultipleValuesCustomField();
$customFieldValue = new CustomFieldValue();
$customFieldValue->value = 'A';
$customField->values->add($customFieldValue);
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$customField = new MultipleValuesCustomField();
$customFieldValue = new CustomFieldValue();
$customFieldValue->value = 'B';
$customField->values->add($customFieldValue);
$customFieldValue = new CustomFieldValue();
$customFieldValue->value = 'C';
$customField->values->add($customFieldValue);
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$customField = new MultipleValuesCustomField();
$customFieldValue = new CustomFieldValue();
$customFieldValue->value = 'C';
$customField->values->add($customFieldValue);
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$customField = new MultipleValuesCustomField();
$customFieldValue = new CustomFieldValue();
$customFieldValue->value = 'D';
$customField->values->add($customFieldValue);
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$quote = DatabaseCompatibilityUtil::getQuote();
$customFieldTableName = MultipleValuesCustomField::getTableName();
$baseCustomFieldTableName = BaseCustomField::getTableName();
$customFieldValueTableName = CustomFieldValue::getTableName();
$valueAttributeColumnName = 'value';
$dataAttributeColumnName = RedBeanModel::getForeignKeyName('MultipleValuesCustomField', 'data');
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id}";
$ids = ZurmoRedBean::getCol($sql);
$beans = ZurmoRedBean::batch($customFieldTableName, $ids);
$customFields = RedBeanModel::makeModels($beans, 'CustomField');
$this->assertEquals(4, count($customFields));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and (select count(*) from {$quote}{$customFieldValueTableName}{$quote} ";
$sql .= "where {$quote}{$valueAttributeColumnName}{$quote} IN('B','C') ";
// Not Coding Standard
$sql .= "and {$quote}{$customFieldTableName}{$quote}.id = {$customFieldValueTableName}.{$customFieldTableName}_id)";
$sql .= " = 2";
$this->assertEquals(1, count(ZurmoRedBean::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and (select count(*) from {$quote}{$customFieldValueTableName}{$quote} ";
$sql .= "where {$quote}{$valueAttributeColumnName}{$quote} IN('C') ";
$sql .= "and {$quote}{$customFieldTableName}{$quote}.id = {$customFieldValueTableName}.{$customFieldTableName}_id)";
$sql .= " = 1";
$this->assertEquals(2, count(ZurmoRedBean::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and (select count(*) from {$quote}{$customFieldValueTableName}{$quote} ";
$sql .= "where {$quote}{$valueAttributeColumnName}{$quote} IN('E') ";
$sql .= "and {$quote}{$customFieldTableName}{$quote}.id = {$customFieldValueTableName}.{$customFieldTableName}_id)";
$sql .= " = 1";
$this->assertEquals(0, count(ZurmoRedBean::getCol($sql)));
MultipleValuesCustomField::updateValueByDataIdAndOldValueAndNewValue($id, 'C', 'E');
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and (select count(*) from {$quote}{$customFieldValueTableName}{$quote} ";
$sql .= "where {$quote}{$valueAttributeColumnName}{$quote} IN('B') ";
$sql .= "and {$quote}{$customFieldTableName}{$quote}.id = {$customFieldValueTableName}.{$customFieldTableName}_id)";
$sql .= " = 1";
$this->assertEquals(1, count(ZurmoRedBean::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and (select count(*) from {$quote}{$customFieldValueTableName}{$quote} ";
$sql .= "where {$quote}{$valueAttributeColumnName}{$quote} IN('C') ";
$sql .= "and {$quote}{$customFieldTableName}{$quote}.id = {$customFieldValueTableName}.{$customFieldTableName}_id)";
$sql .= " = 1";
$this->assertEquals(0, count(ZurmoRedBean::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and (select count(*) from {$quote}{$customFieldValueTableName}{$quote} ";
$sql .= "where {$quote}{$valueAttributeColumnName}{$quote} IN('E') ";
$sql .= "and {$quote}{$customFieldTableName}{$quote}.id = {$customFieldValueTableName}.{$customFieldTableName}_id)";
$sql .= " = 1";
$this->assertEquals(2, count(ZurmoRedBean::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and (select count(*) from {$quote}{$customFieldValueTableName}{$quote} ";
$sql .= "where {$quote}{$valueAttributeColumnName}{$quote} IN('B', 'E') ";
$sql .= "and {$quote}{$customFieldTableName}{$quote}.id = {$customFieldValueTableName}.{$customFieldTableName}_id)";
$sql .= " = 2";
$this->assertEquals(1, count(ZurmoRedBean::getCol($sql)));
}