Manuais
PHC GO Funções SDK - Query
 

Query

This class contains functions that allow you to get information about the data stored in the database.
 
 

getEntityByStamp

 

Description

Get a specific record by it´s unique identifier
 

Return Function

The entity of type T if found (VO), otherwise Nothing
 

Parameters


value Description Type
stamp the string identifier which is always 25 characters wide. String
 

Example


'The following example returns an entity(VO) with all data from Data Base from a specific client ( with stamp = "4e7-46a8-935a-463f5f39c55" )

'Store the result in variable client (if find a client with stamp = "4e7-46a8-935a-463f5f39c55" )

Dim client as CLVO = SDK.Query.getEntityByStamp(of CLVO)("4e7-46a8-935a-463f5f39c55")

 

getEntityData (of T)

 

Description

Get a list of records for an entity
 

Return Function

A list of records.
 

Parameters

 
value Description Type
filter The filter to apply, the type is FilterItem or FilterItems.

This parameter is optional and the default value is Nothing which causes all records to be returned, so it is not advisable to execute this method without a defined filter.
PHCDataLayer.FilterItem
or
PHCDataLayer.FilterItems
 

Example


'The following example returns a list of entity(VO) with all data from Data Base (in this case all data from table "cl"

Dim clientList as List(of CLVO) = SDK.Query.getEntityData(of CLVO)

Example 2


' Get a specific client from CL table 

Dim client as ClVO = SDK.Query.getEntityData(of ClVO)(New FilterItem(CL.nome, Comparison.Equal, "'Bernardo Santiago'")).firstOrDefault()

 

getOneValue

 

Description

Get a value or its default.
 

Return Function

The result of the type applied to the field or a default valur for the type of the field, for example if T is String then "" is returned, if Integer or Decimal then 0 is returned, if Boolean then False is returned, and finally if Date then 01.01.1900 is returned.
 

Parameters

 
value Description Type
entityName The name of the entity, for example Clients is CL, Invoicing Documents is FT, Suppliers is FL, Purchases is FO String
field The name of the field that belongs to the specified entity and from which we want to return the values. String
aggregateType Its the aggregate type applied to the values. Can be one of the following types ENUM PHCDataLayer.Aggregate (None,Avg,Count,Min,Max,Sum,NullMin,Accumulated)
filter The filter to apply, the type is FilterItem or FilterItems.

This parameter is optional and the default value is Nothing which causes all records to be returned, so it is not advisable to execute this method without a defined filter.
PHCDataLayer.FilterItem
or
PHCDataLayer.FilterItems
 

Example


'The following example returns the max series number of Invoicing Documents that launch stock movements and control credit issues, where td is the entityName for "TdVO" and the series number is stored in the field "TdVO.ndoc". 

myFilter.Add(New FilterItem("lancacc", Comparison.Equal, True))

 

getValues

 

Description

Get a list of values all of the same type.
 

Return Function

A list of values all of the same type, or an empty list if no records exist.
 

Parameters

 
value Description Type
entityName The name of the entity, for example Clients is CL, Invoicing Documents is FT, Suppliers is FL, Purchases is FO String
field The name of the field that belongs to the specified entity and from which we want to return the values. String
filter The filter to apply, the type is FilterItem or FilterItems.

This parameter is optional and the default value is Nothing which causes all records to be returned, so it is not advisable to execute this method without a defined filter.
PHCDataLayer.FilterItem
or
PHCDataLayer.FilterItems
 

Example


'The following example returns a list with all the series number of Invoicing Documents that launch stock movements, where td is the entityName for "TdVO" and the series number is stored in the field "TdVO.ndoc".  

Dim allConfigNumbers as List(Of Decimal) = SDK.Query.getValues(Of Decimal)("td", "ndoc", New FilterItem("lancacc", Comparison.Equal, True))


getNumRecords

 

Description

Gets the number of record from a sql table.
 

Return Function

A single value that indicates the number of record of a sql table.
 

Parameters

 
value Description Type
entityName The name of the entity, for example Clients is CL, Invoicing Documents is FT, Suppliers is FL, Purchases is FO String
filter The filter to apply, the type is FilterItem or FilterItems.

This parameter is optional and the default value is Nothing. When filter is nothing, this function returns the number of all record of the table.
PHCDataLayer.FilterItem
or
PHCDataLayer.FilterItems
 

Example


'Gets the number of customers in the center zone 

Dim numberOfClRecordsFromCenter as integer = SDK.Query.GetNumRecords(CL.getTableName,New FilterItem(CL.zona, Comparison.Equal, "'Centro'"))


getEntityData

 

Description

Gets a list of records that can combine field values from different entities.
 

Return Function

A list of records.
 

Parameters

 
value Description Type
queryItem The query to apply QueryVO
 

Example


' The following example returns a list of all products in warehouse 

Dim query As QueryVO = New QueryVO()
query.entityName = Sa.getTableName 'Select main table
query.SelectItems.Add(sa.armazem) 'Select the column we want
query.SelectItems.Add(sa.ref)
query.SelectItems.Add(st.design)
query.SelectItems.Add(sa.stock)


Dim join = New JoinEntity() 'Create the Join object

Dim joinFilter = New FilterItem() 'Create the Join object filter

join.TableName = St.getTableName
joinFilter.filterItem = sa.ref
joinFilter.comparison = Comparison.Equal
joinFilter.valueItem = st.ref

join.joinExp.Add(joinFilter)
query.joinEntities.Add(join)

query.OrderByItems.Add(New OrderByItem(sa.armazem, OrderTypes.Descending))

Dim result = SDK.Query.getEntityData(query)


getEntityData

 

Description

Gets a list of records that can combine field values from different entities.
 

Return Function

A list of records.
 

Parameters

 
value Description Type
queryItem The query to apply String
 

Example


' The following example returns a list of all products in warehouse 

Dim query As String = "select sa.armazem, sa.ref, st.design, sa.stock from sa inner join st on sa.ref = st.ref order by sa.armazem desc"

Dim result = SDK.Query.getEntityData(query)


ExistRecord

 

Description

Checks if a table has records.
 

Return Function

A boolean value that indicates if the table has records or not.
 

Parameters

 
value Description Type
entityName The name of the entity, for example Clients is CL, Invoicing Documents is FT, Suppliers is FL, Purchases is FO String
filter The filter to apply, the type is FilterItem or FilterItems.

This parameter is optional and the default value is Nothing. When filter is nothing, this function returns the number of all record of the table.
PHCDataLayer.FilterItem
or
PHCDataLayer.FilterItems
 

Example


' Checks if exist customers from center zone in Cl table 

Dim existCustomersFromCenter as Boolean = SDK.Query.ExistRecord(CL.getTableName,New FilterItem(CL.zona, Comparison.Equal, "'Centro'"))