Using the Web

streakconvertingΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 4 χρόνια και 7 μήνες)

394 εμφανίσεις

Sage CRM Developers Course

Using the Web
Services API (2)

Looking ahead to the classes

DP01: Introduction to the Development
Partner Program

DP02: Entities and the Data Model (Part 1 of
2)

DP03: Entities and the Data Model (Part 2 of
2)

DP04: Implementing Screen Based Rules
(Part 1 of 2)

DP05: Implementing Screen Based Rules
(Part 2 of 2)

DP06: Screen and User Independent
Business Rules

DP07: Workflow (Part 1 of 2)

DP08: Workflow (Part 2 of 2)

DP09: Using the API Objects in ASP Pages
(Part 1 of 2)

DP10 : Using the API Objects in ASP Pages
(Part 2 of 2)

DP11: Using the Component Manager

DP12: Programming for the Advanced Email
Manager

DP13: Using the Web Services API

DP14: Using the Web Services API (Part 2 of
2)

DP15: Coding the Web Self Service COM API
(Part 1 of 2)

DP16: Coding the Web Self Service COM API
(Part 2 of 2)

DP17: Using the .NET API (Part 1 of 2)

DP18: Using the .NET API (Part 2 of 2)


Agenda

Web Services Data Tasks in Detail

Find

Update

Delete

Insert

Development and Integration Issues

Usage of Objects
and Functions

Data Objects

Methods

Query

queryentity

queryrecord

Query function

Arguments:

queryString: a SQL WHERE clause

entityname: the name of the target table


Returns a queryresult object


private void buttonSearchCompanyName_Click_1(object sender,
EventArgs

e)


{


queryresult

CRMQueryResult
;


ewarebase
[]
CRMBase
;


try


{


string
strSQL

= "
comp_name

like '" +
textBoxSearch.Text

+ "%'";


MessageBox.Show
(
strSQL
);



CRMQueryResult

=
CRMService.query
(
strSQL
, "Company");


CRMBase

=
CRMQueryResult.records
;



MessageBox.Show
(
CRMBase.Length.ToString
());



for (
int

intCount

= 0;
intCount

<
CRMBase.Length
;
intCount
++)


{


CRMCompany

= (company)
CRMBase
[
intCount
];


listBoxCompanyNames.Items.Add
(CRMCompany.name);


}


}


catch (Exception
exc
)


{


MessageBox.Show
(
exc.Message
);


}


}




Usage: next()

queryresult

myQueryResult

=
CRMService.query
(“
comp_name

like ‘A%’
“,"company");

MessageBox.Show
(
myQueryResult.more.ToString
());

if(
myQueryResult.more
)

{

myQueryResult

=
CRMService.next
();

}


Usage: queryentity()

Arguments:

id: integer, the ID value of the record being sought

entityname
: the name of the target table


Returns a
queryentityresult

object


private void
buttonSearchCompanyID_Click
(object sender,
EventArgs

e)

{

queryentityresult

CRMEntityResult
;

ewarebase

CRMBase
;

ewarebase
[] CRMBase2;

ewarebaselist

CRMCompanyPeople

= new
ewarebaselist
();

person
CRMPerson
;

try

{

CRMEntityResult

=
CRMService.queryentity
(
int.Parse
(
textBoxSearch.Text
), "Company");

CRMBase

=
CRMEntityResult.records
;

CRMCompany

= (company)
CRMBase
;

CRMCompanyPeople

=
CRMCompany.people
;

CRMBase2 =
CRMCompanyPeople.records
;

for (
int

intCount

= 0;
intCount

< CRMBase2.Length;
intCount
++)

{

CRMPerson

= (person)CRMBase2[
intCount
];


if (
CRMPerson.personid

==
CRMCompany.primarypersonid
)

{

labelPers_LastName.Text

=
CRMPerson.lastname
;

break;

}

}

textBoxComp_Name.Text

= CRMCompany.name;

textBoxComp_WebSite.Text

=
CRMCompany.website
;

textboxComp_Companyid.Text

=
CRMCompany.companyid.ToString
();

}

catch (Exception
exc
)

{

MessageBox.Show
(
exc.Message
);

}


queryrecord method

Syntax

queryrecord

(
fieldlist

As string ,
queryString

As string ,
entityname

As string ,
orderby

As string ) As
queryrecordresult


Specify what fields are to be selected, a where clause and order
by, returns a list of
CRMRecord

objects


Notes:

Instead of querying an entity (for example, a company) and
getting back a strongly typed (company) object, using the
flexibility afforded by the
crmrecordtype

object, it is possible to
query an entity and get back a list of fields that you can iterate
through.

private void
buttonQueryRecord_Click
(object sender,
EventArgs

e)

{

queryrecordresult

myQueryRecordResult

=
CRMService.queryrecord
("
comp_companyid
,
comp_name
",
"
comp_type
='Customer'", "company", "
comp_name
");

crmrecord
[]
myRecordList

=
myQueryRecordResult.records
;

for (
int

intCount

= 0;
intCount

<
myRecordList.Length
;
intCount
++)

{

recordfield
[]
myFieldList

=
myRecordList
[
intCount
].records;

for (
int

intCount2 = 0; intCount2 <
myFieldList.Length
;
intCount2++)

{

recordfield

myField

= (
recordfield
)
myFieldList
[intCount2];

listBoxCompanyNames.Items.Add
(myField.name + ": =" +
myField.value
);

}

}

TabControl.SelectedTab

= tabPage1;

}


Note: Fields list must include primary key.


Usage: nextqueryrecord()

queryrecordresult myQueryRecordResult =
CRMService.queryrecord
("comp_companyid, comp_name",
"comp_type='Customer'", "company", "comp_name");

MessageBox.Show(myQueryRecordResult.more.ToString());

if(myQueryRecordResult.more)

{

myQueryRecordResult =
CRMService.nextqueryrecord
();

}

Working with Selection Lists

Syntax

getdropdownvalues ( entityname As string ) As dropdownvaluesresult

Returns the list of the drop
-
down fields in a table and the list of values that CRM expects for that field.


CRM expects a given set of values for drop
-
down fields


Must get these values programmatically.

Usage

dropdownvalues[] compDropDowns = CRMService.getdropdownvalues("company");

comboBox1.Items.Clear();

for (int i = 1; i < compDropDowns.Length; i++)

{


// MessageBox.Show(compDropDowns[i].fieldname);


if (compDropDowns[i].fieldname.ToString() == "source")


{


for (int x = 1; x < compDropDowns[i].records.Length; x++)


{



comboBox1.Items.Add(compDropDowns[i].records[x].ToString());



if (CRMCompany.source == compDropDowns[i].records[x].ToString())



{



comboBox1.SelectedIndex = x
-
1;



}



//MessageBox.Show(compDropDowns[i].records[x].ToString());


}


}

}



Update Method

Arguments:

entityname: string, the name of the target table

records: an ewarebase array instance


Returns an updateresult object


private void buttonSave_Click(object sender, EventArgs e)


{


ewarebase[] CRMBase;


updateresult CRMUpdateResult;


try


{



CRMBase = new ewarebase[1];


CRMCompany.companyid =
int.Parse(textboxComp_Companyid.Text);


CRMCompany.companyidSpecified = true;


CRMCompany.name = textBoxComp_Name.Text;


CRMCompany.website = textBoxComp_WebSite.Text;


CRMCompany.source = comboBox1.Text;


CRMBase[0] = CRMCompany;


CRMUpdateResult = CRMService.update("company", CRMBase);


MessageBox.Show("Record Updated");


}


catch (Exception exc)


{


MessageBox.Show(exc.Message);


}


}


Inserting Records

All inserts should typically be performed on an entity basis.

Can update a company (or person) with


Address


Phone


E
-
mail information.

Facilitate integration


In 3
rd

Party Systems, a single contact record may represent company, person, phone, e
-
mail, and address information.

Types of Insert Task

Insert a Single Entity

Insert a Set of Entities

Insert a Single of Row in a Table

Insert a Set of Records in a Table


Migration Considerations

Insertion of new/unknown users


addresource function


Adds a user as a resource. This user is not a fully enabled user. The functionality exists purely to
facilitate data migration.

Differing Data Lengths


altercolumnwidth function


Used to resize a column width to ensure compatibility with third
-
party databases


Development and
Integration Issues

Integration Development Issues

Version Change

Has the implementation of Sage CRM been upgraded since the last synchronization
?


getversionstring

Handling Data Change

What data has changed since the last time synchronisation took place?

How do I match users from one system to another
?


queryid
()


queryidnodate
()


addresource
()

Handling Application Change

What has changed about the data structures since last time synchronisation took
place?


What if tables have been added?


What if columns have been added?


Do data field lengths match?


Handling Application Change

What has changed about the data structures since last time synchronisation took
place?

What if tables have been added?

What if columns have been added?

Do data field lengths match
?


altercolumnwidth
()


WSDL Types

WSDL defines the Types and Methods exposed

In .NET the objects exposed by WSDL are strongly typed representations

Example

Company entity has


Name


Status


Etc

In referencing WSDL the Company’s properties are fixed.

Problems may occur post compilation if columns are removed or added to Entity or new
Entities included

Significant Issues

facing
Developers
are…

Any Live CRM Implementation is likely to change

Strongly Type representations cause issues for 3
rd

party applications

Especially true for Hosted Environment

Requirement to have access to dynamic information about data model, available
entities and columns

Metadata exposed via getmetadata and getallmetadata methods

Using
SData

New features depend on Tomcat

Sage CRM v7.2

Tomcat is Used By

The
ReadOnly

SData

Provider

The Interactive Dashboard

Exchange Integration

Sage E
-
marketing

Data Import and Export

Document drop panel

Server Side Mail Merge

Other Java based features
include the spell checker and the
printing to PDF.


These requires a set of
supporting technologies.

Google Web Tool Kit


http://code.google.com/webtoolkit/


Apache Tomcat


http://tomcat.apache.org/


Spring Framework


http://www.springsource.org/


Hibernate


https://www.hibernate.org/


What is SData?

http
://sdata.sage.com

SData (Sage Standard)

Enables desktop, server, and web
-
based Sage applications to communicate with each
other as well as third
-
party applications and the Web.

SData is built on top of leading industry standards including HTTP, XML, REST, and
Atom/RSS


Representational state transfer (REST)

The full standard covers basic reading, writing, updating and deleting of data between and
across products as well as more complex functions such as synchronisation of data,
security, discoverability of services, single sign
-
on, error handling, and paging and batching
of information for increased performance.

SData Provider within Sage CRM

SDATA is based on RESTful principles.

Sage CRM can consume SData feeds
within the Interactive Dashboard

https://sdata.showcase.sage.com/sdata/acc
ounts50/SDO/
-
/$schema

Sage CRM’s implementation of SData
Provider

Certain entities and views from Sage CRM
will be exposed using the SData standard


An
XSD
Schema definition is available


lists the available entities, fields, and views


It will be read
-
only and will get updated /
refreshed if custom entities are added or
deleted or if other changes are made to
exposed entities

In version Sage CRM v7.x only GET is
available for 3rd Party Developers

HTTP Method

Implied Data
Interaction

GET

Query the state

POST

Modify

the state

PUT

Create Data

DELETE

Delete Data

SQL

REST

Create

Insert

PUT

Read

Select

GET

Update

Update

POST

Delete

Delete/Drop

DELETE

Sage CRM SData plays “Nice”

Structure of a
RESTful

URI

https:
//
server.domain.com
/
rest/resource/search
?q=rest&start=10
#1


URI scheme


Authority


Path


Query


Fragment

Sage CRM


http://[servername]/sdata/[installname]j/sagecrm/
-
/person?where=lower(pers_firstname) like '
william
%'

“Nice” URI are

Short

Use Nouns rather than Verbs


Example Read Only Sage CRM SData
Requests

SData Queries

Queries support “order by” and “filtering”

All fields are returned by default

Pattern

http://
myserver
/sdata/
installname
j
/sagecrm/
-
/
entity

Examples

http://myserver/sdata/crmj/sagecrm/
-
/company/$schema

http://myserver/sdata/crmj/sagecrm/
-
/company('43')

http://myserver/sdata/crmj/sagecrm/
-
/company?where=comp_companyid in ('43', '45')

http://myserver/sdata/crmj/sagecrm/
-
/company('43')?include=person

http://myserver/sdata/crmj/sagecrm/
-
/company(comp_companyid
eq

'962')

http://myserver/sdata/crmj/sagecrm/
-
/person?where=lower(pers_firstname) like '
william
%'


Security and Firewalls

SData

Access to data is controlled by the user's security profile and
territory rights.

Access does not use the eware.dll to create the session and
access is NOT logged in the user activity tables within CRM.

Read Only requests use HTTP GET Method

This works well with network firewalls.

Some firewalls of HTTP proxies may drop PUT/DELETE requests therefore not
all VERBS may be used as CRUD capability is developed. Restriction will be
overcome using the override call. See:

http://interop.sage.com/daisy/sdata/UpdateOperation/Example.html

Web Services

Access to data is controlled by the user's security
profile and territory rights.

Web Services access because it requires a user
session to be created it automatically logged in the
user activity tables within CRM.

Requests use HTTP POST Method.

This works well with network firewalls.

This means that the web service client accessing CRM can be
beyond the corporate Firewall.





Relative Performance

SData

It’s fast!

http://[servername]/sdata/[installname]j/sagecr
m/
-
/person?where=lower(pers_firstname) like
'
william
%'

Request routed to Tomcat. Single SQL
statement. And XML sent back.


Web Services

A message is posted to
http://[servername]/[installname]/eware.dll/Web
Services/SOAP

Message has to be parsed and ‘unpacked’

Need to think hard about methods used.

Then is turned by DLL into a series of SQL statements,
the return XML is assembled and a response is issued.

Interaction with Security

SData

User security profile rights apply. This limits
access to rows within entities and views.

Field Level security applies. This limits
columns returned.

Within external applications the dynamically
constructed feed will need to be handled.

Can not assume column returned or data source available.

Information contained in Schema


http://[servername]/sdata/[installname]j/sagecrm/
-
/$schema


http://[servername]/sdata/[installname]j/sagecrm/
-
/company/$schema

Web Services

User security profile rights apply. This limits
access to rows within entities and views.

Field Level security applies. This limits
columns returned.

Within external applications strongly typed
objects can create its own issues. Can use
getmetadata
(),
getallmetadata
() and record()
methods to discover dynamic information.

Creating Sessions

SData

Any User can access SData. What they see is subject
to rights.

All requests must be authenticated

using HTTP Basic authentication.

Access controlled by security profiles and field

Sage
CRM 7.x
does not advertise supported
authentication methods via the usual

WWW
-
Authenticate

response header. You need to perform a
pre
-
emptive authentication.

Web Services

Only Users enabled can access via Web Service.

Web Services are Session based similar to interactive
user logons

Web Service usage shows in Activity Reports

Activity table

SysAdmin

Locking


The SData in the
Interactive Dashboard

Sage CRM 7.x & SData

Sage CRM consumes SData feeds within the Interactive Dashboard

http
://[server]:[port]/sdata/X32CRM/GCRM_1_1_1/X3DEMO
/$
schema

http
://[server]/SDataServlet/sdata/sageERP/accpac/SAMINC
/$
schema

Sage CRM’s implementation of SData Provider

Entities and views from Sage CRM will be exposed using the SData standard


An
XSD
Schema definition is available


lists the available entities, fields, and views


It will be read
-
only and will get updated / refreshed if custom entities are added or deleted
or if other changes are made to exposed entities

Making a ‘User View’ Available for SData Access

Only User views.

Core and System can not be changed

The View can then be accessed like a table.

http://myserver/sdata/crmj/sagecrm/
-
/vOppoDatesOpen

Internal SData Feeds

SData capabilities using external
ERP data

Create a Pre
-
defined

SData feed Template

Create a Pre
-
defined
Sdata

feed Template

Storing the User name and password for
the feed in the SData Feed Template
definition means that there isn't a
requirement anymore for an Admin to
provide Users with complicated SData
schema URLs and authentication details
every time they create a new SData
Gadget.





SData Gadget Filters




SData Record Summary

Can add a SData Record Summary and link to existing SData feed.

SData Gadget Restrictions

Limited use within company dashboard

Will not filter by
comp_companyid

Can not link between SData and Non SData Gadgets

EXTERNAL USE OF
SDATA

FEEDS

Sage CRM v7.1 SData Authentication

Schema requests do not require authentication, so they will show all possible data
fields.

http://[servername]/sdata/[installname]j/sagecrm/
-
/$schema

http://[servername]/sdata/[installname]j/sagecrm/
-
/company/$schema

To make an actual data request Sage CRM 7.x does not advertise supported
authentication methods via the usual WWW
-
Authenticate response header. The
programmer needs to perform a pre
-
emptive authentication.

This javascript code snippet shows how authentication can be carried out.




SID (Session ID) can be used in SData
Requests from Sage CRM v7.1

<script>

function
GetKeyValue
(
querystringname
)

{

var

strPath

=
window.location.search.substring
(1);

var

arrayKeys

=
strPath.split
("&");

for (
var

i
=0;i<
arrayKeys.length;i
++)

{

var

arrayValue

=
arrayKeys
[
i
].split("=");

if (
arrayValue
[0].
toLowerCase
()==
querystringname.toLowerCase
())

{

return
arrayValue
[1];

}

}

return "";

}

window.alert
("start:"+
GetKeyValue
("SID"));

XmlHttp

= new
XMLHttpRequest
();

var

strURL

= "http://richardsj
-
lt/sdata/crm71j/sagecrm/
-
/company?where=comp_companyid in ('43',
'45')&SID="+
GetKeyValue
("SID");

XmlHttp.open
('GET',
strURL,false
);

window.alert
("open");

XmlHttp.send
(null);

window.alert
("send");

var

strHtml

=
XmlHttp.responseText
;

XmlHttp
=null; // always clear the
XmlHttp

object when you are done to
avoid memory leaks

window.alert
("test:"+
strHtml
);

window.alert
("end");

</script>

http://[servername]/sdata/[installname]j/s
agecrm/
-
/company('43')&SID=
61546204736053

Fast SData requests can now be used for
AJAX in Custom Content and
onChange

scripts

Simple SData Ajax
ReadOnly

Fetches in Sage CRM v7.2

var personID = crm.fields("oppo_primarypersonid").value();

var accountID = crm.fields("oppo_primaryaccountid").value();

var successPerson = function (crmRecord) {

crm.infoMessage("This opportunity belongs to :" + crmRecord.pers_firstname + " " + crmRecord.pers_lastname);

}

var successAccount = function (crmRecord) {

crm.infoMessage("This opportunity belongs to :" + crmRecord.acc_name);

}

crm.sdata({

entity: "person",

id: personID,

success: successPerson

});

crm.sdata({

entity: "account",

id: accountID,

success: successAccount

});



External Development

Web Self Service Extensions

Self Service can use both SOAP and
SData feeds but this integration has to
be managed by the Developer.


Visitor
vs

User


Using SData in 3
rd

Party Access

Excel can consume XML

http://stl00539/sdata/crmj/sagecrm/
-
/opportunity?SID=161980816526651

Q&A

Looking ahead to the classes

DP01: Introduction to the Development
Partner Program

DP02: Entities and the Data Model (Part 1 of
2)

DP03: Entities and the Data Model (Part 2 of
2)

DP04: Implementing Screen Based Rules
(Part 1 of 2)

DP05: Implementing Screen Based Rules
(Part 2 of 2)

DP06: Screen and User Independent
Business Rules

DP07: Workflow (Part 1 of 2)

DP08: Workflow (Part 2 of 2)

DP09: Using the API Objects in ASP Pages
(Part 1 of 2)

DP10 : Using the API Objects in ASP Pages
(Part 2 of 2)

DP11: Using the Component Manager

DP12: Programming for the Advanced Email
Manager

DP13: Using the Web Services API

DP14: Using the Web Services API (Part 2 of
2)

DP15: Coding the Web Self Service COM API
(Part 1 of 2)

DP16: Coding the
Web Self Service
COM API
(Part 2 of 2)

DP17: Using the .NET API (Part 1 of 2)

DP18: Using the .NET API (Part 2 of 2)