Query
This class contains functions that allow you to get information about the data stored in the database.
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")
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()
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
Dim maxNumber as Integer = SDK.Query.getOneValue(Of Integer)("td", "ndoc", Aggregate.Max, New FilterItem("lancacc", Comparison.Equal, True)) 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))
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'"))
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)
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)
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'"))