Integrating Spatial Data Types with SuiteCrm

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

Hello All,

I am trying to integrate new Spatial Data types with suiteCrm such as “Geometry”
I am using SuiteCrm 7.1.5 with MS SQL Server 2012

I added a new Field at “custom/Modules/DynamicFields”
Set the DbType to “Geometry” at the Template Class.

The database successfully creates a new field type Geometry when manage the fields from “ModuleBuilder & Studios”.

I created a new sugar field at “custom/include/SugarFields”

I override the Edit & Detail functions to suit my needs.

The only problem i am facing here is when saving the variable to the database using the save function.

The valid way to set a value at the SQL query is to write it as the following

UPDATE TABLE set COLUMN = geometry::STGeomFromText('POINT (100 100)', 0)

now when i use the $bean at the save function for example to set the data like the following :

$bean->$field = "geometry::STGeomFromText('POINT (100 100)', 0)";

I get an error message

at my browser and no fatal or errors at my logs file.

I tried to do another attempt using the native $DB field like the following .

$GLOBALS['db']->query("UPDATE " . $tableField . " set " . $columnField . " = geometry::STGeomFromText('POINT (100 100)', 0)   where id_c='" . $idValue . "';");

It worked, The value is successfully updated at the database -ofcourse i am aware this will only work if i am updating the record and will fail if i try to add a new record since the new id won’t be created yet-

but it made me think the problem is that $bean variable automatically wrap my value in to single Quotes & do text escaping which i believe it will break the code.

i even tried to change the field template class to extend TemplateInt once & TemplateBool .
Asuming that the prev. attempt would make the values not wrapped in quotes,but i still gets the same “No data received” page.

is there any way where i can create a custom type at sugarBean where handle the field “geometry” in a completely different way ? Like no escaping and no wrap value between quotes?

Or do you think there is a better practice /better fix than what i am thinking to do ?

Thank you for your time.

2

Hello again,

I managed to make the $beans not quote or escape the value by going to the
DynamicFields.php , at the save() function i added a new type field

if($field['type'] == 'geometry')
                    {
                        
                        $quote = '';
                    }

and at the save line


 if($isUpdate){
                        if($first){
                            $query .= " $name=$quote".$field['type'] == 'geomap' ?$val :$GLOBALS['db']->quote($val)."$quote";

                        }else{
                            $query .= " ,$name=$quote".$field['type'] == 'geomap' ?$val :$GLOBALS['db']->quote($val)."$quote";
                        }
                    }
                    $first = false;
                    $queryInsert .= " ,$name";
                    $values .= " ,$quote". $field['type'] == 'geomap' ?$val :$GLOBALS['db']->quote($val). "$quote";

I am not sure if this is the best practice or if there is a better way to do that.

but its working.

Important : Pls be informed that this is not upgrade safe and u should remember this modification if you used it and wants to upgrade the suitecrm later.