Manuais
PHC GO Vários exemplos da utilização do QueryVO
 

Cenário

O queryVO é a possibilidade de na Framework poder efetuar Queries à base de dados em VB.NET, seja nas Análises, Regras de Negócio, Autocomplete ou construção de Parâmetros.
Este artigo tem como objetivo mostrar a construção de um QueryVO em vários exemplos práticos.

 

Como configurar

Toda a programação de seguida apresentada foi efetuada numa Análise com a opção "É um programa VB.NET"

 

Select de todos os registos


Código em VB.NET

Dim query As QueryVO = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("*")
Dim result = SDK.Query.getEntityData(query)
return result

 


Resultado em SQL

 

Select * from [phcgokit].[dbo].[CL]

 

Select definindo algumas colunas:


Código em VB.NET

Dim query As QueryVO = New QueryVO() 
query.entityName = "CL" 
query.SelectItems.add("nome") 
query.SelectItems.add("no") 

Dim result = SDK.Query.getEntityData(query) 
return result


Resultado em SQL

Select nome,no from [phcgokit].[dbo].[CL]

 

Select utilizando um sub select


Código em VB.NET

Dim query As QueryVO = New QueryVO()
query.entityName = "CL" query.SelectItems.add("nome") 
query.SelectItems.add("no") 

Dim result = SDK.Query.getEntityData(query) 
return result


Resultado em SQL

Select (Select Count(*) From ft (Nolock) 
Where ft.no = cl.no) Total,* from [phcgokit].[dbo].[CL]

 

Select com condição Where simples - Possibilidade 1


Código em VB.NET

Dim query = New queryVO() 
query.entityName = "CL" 
query.SelectItems.add("nome") 
query.SelectItems.add("no") 
query.filterItems.add(new FilterItem("inactivo", Comparison.Equal, 0)) 

Dim result = SDK.query.getEntityData(query) 
return result


Resultado em SQL

Select nome,no from [phcgokit].[dbo].[CL] where IsNull([Cl].[inactivo],0)=0

 

Select com condição Where simples - Possibilidade 2


Código em VB.NET

Dim query = New QueryVO() 
query.entityName = "CL"
query.SelectItems.add("nome") 
query.SelectItems.add("no") 

Dim filtroCL = New FilterItem() 
filtroCL.filterItem = "inactivo" 
filtroCL.comparison = Comparison.Equal 
filtroCL.valueItem = 0 

query.filterItems.add(filtroCL) 

Dim result = SDK.Query.getEntityData(query) 
return result


Resultado em SQL

Select nome,no,esaldo from [phcgokit].[dbo].[CL] 
where IsNull([Cl].[inactivo],0)=0 and IsNull([Cl].[esaldo],0)>0

 

Select com condição Where, com And entre dois campos


Código em VB.NET

Dim query = New queryVO() 
query.entityName = "CL" 
query.SelectItems.add("nome") 
query.SelectItems.add("no") 
query.SelectItems.add("esaldo") 

Dim filtroCL = New FilterItem() 
filtroCL.filterItem = "inactivo" 
filtroCL.comparison = Comparison.Equal 
filtroCL.valueItem = 0

Dim filtroCL2 = New FilterItem() 
filtroCL2.filterItem = "esaldo" 
filtroCL2.comparison = Comparison.Greater 
filtroCL2.valueItem = 0 

query.filterItems.add(filtroCL) 
query.filterItems.add(filtroCL2) 

Dim result = SDK.query.getEntityData(query) 
return result


Resultado em SQL

Select nome,no,esaldo from [phcgokit].[dbo].[CL] 
where IsNull([Cl].[inactivo],0)=0 and IsNull([Cl].[esaldo],0)>0

 

Select com condição Where, com Or entre dois campos


Código em VB.NET

Dim query = New queryVO() 
query.entityName = "CL" 
query.SelectItems.add("nome") 
query.SelectItems.add("no") 
query.SelectItems.add("esaldo") 

Dim filtroCL = New FilterItem() 
filtroCL.filterItem = "naoencomenda" 
filtroCL.comparison = Comparison.Equal 
filtroCL.valueItem = 1 
filtroCL.groupItem = FilterGroupItem.Or 

Dim filtroCL2 = New FilterItem() 
filtroCL2.filterItem = "nocredit" 
filtroCL2.comparison = Comparison.Equal 
filtroCL2.valueItem = 1 

query.filterItems.add(filtroCL) 
query.filterItems.add(filtroCL2) 

Dim result = SDK.query.getEntityData(query) 
return result


Resultado em SQL

Select nome,no,esaldo from [phcgokit].[dbo].[CL] 
where IsNull([Cl].[naoencomenda],0)=1 or IsNull([Cl].[nocredit],0)=1

 

Select com condição Where com 1 campo And e 2 campos OR


Código em VB.NET

Dim query = New queryVO() 
query.entityName = "CL" 
query.SelectItems.add("nome") 
query.SelectItems.add("no") 
query.SelectItems.add("esaldo") 

Dim filtroCL = New FilterItem() 
filtroCL.filterItem = "inactivo" 
filtroCL.comparison = Comparison.Equal 
filtroCL.valueItem = 0 
filtroCL.groupItem = FilterGroupItem.AndBkt 

Dim filtroCL2 = New FilterItem() 
filtroCL2.filterItem = "naoencomenda" 
filtroCL2.comparison = Comparison.Equal 
filtroCL2.valueItem = 1 
filtroCL2.groupItem = FilterGroupItem.Or 

Dim filtroCL3 = New FilterItem() 
filtroCL3.filterItem = "nocredit" 
filtroCL3.comparison = Comparison.Equal 
filtroCL3.valueItem = 1 
filtroCL3.groupItem = FilterGroupItem.CloseBkt 

query.filterItems.add(filtroCL) 
query.filterItems.add(filtroCL2) 
query.filterItems.add(filtroCL3) 

Dim result = SDK.query.getEntityData(query) 
return result


Resultado em SQL

Select nome,no,esaldo from [phcgokit].[dbo].[CL] 
where IsNull([Cl].[inactivo],0)=0 
and (IsNull([Cl].[naoencomenda],0)=1 or IsNull([Cl].[nocredit],0)=1)

 

Select com condição Where com 2 campo2 And e 2 campos OR


Código em VB.NET

Dim query = New QueryVO() 
query.entityName = "CL" 
query.SelectItems.add("nome") 
query.SelectItems.add("no") 
query.SelectItems.add("esaldo") 

Dim filtroCL0 = New FilterItem() 
filtroCL0.groupItem = FilterGroupItem.OpenBkt

Dim filtroCL1 = New FilterItem() 
filtroCL1.filterItem = "inactivo" 
filtroCL1.comparison = Comparison.Equal 
filtroCL1.valueItem = 0 

Dim filtroCL2 = New FilterItem() 
filtroCL2.filterItem = "esaldo" 
filtroCL2.comparison = Comparison.Greater 
filtroCL2.valueItem = 0 
filtroCL2.groupItem = FilterGroupItem.BktAndBkt 

Dim filtroCL3 = New FilterItem() 
filtroCL3.filterItem = "naoencomenda" 
filtroCL3.comparison = Comparison.Equal 
filtroCL3.valueItem = 1 
filtroCL3.groupItem = FilterGroupItem.Or 

Dim filtroCL4 = New FilterItem() 
filtroCL4.filterItem = "nocredit" 
filtroCL4.comparison = Comparison.Equal 
filtroCL4.valueItem = 1 
filtroCL4.groupItem = FilterGroupItem.CloseBkt 

query.filterItems.add(filtroCL0) 
query.filterItems.add(filtroCL1) 
query.filterItems.add(filtroCL2) 
query.filterItems.add(filtroCL3) 
query.filterItems.add(filtroCL4) 

Dim result = SDK.Query.getEntityData(query) 
return result


Resultado em SQL

Select nome,no,esaldo from [phcgokit].[dbo].[CL] 
where (IsNull([Cl].[inactivo],0)=0 and IsNull([Cl].[esaldo],0)>0) 
and (IsNull([Cl].[naoencomenda],0)=1 or IsNull([Cl].[nocredit],0)=1)

 

Select com 1 inner Join


Código em VB.NET

Dim query = New QueryVO() 
query.entityName = "CL" 
query.SelectItems.add("cl.nome") 
query.SelectItems.add("cl.no") 
query.SelectItems.add("cl.esaldo") 
query.SelectItems.add("ft.nmdoc") 
query.SelectItems.add("ft.fdata") 
query.SelectItems.add("ft.etotal") 

Dim join = New JoinEntity() 'Create the Join object 
Dim joinFilter = New FilterItem() 'Create the Join object filter 
join.TableName = "FT" 
joinFilter.filterItem = ft.no 
joinFilter.comparison = Comparison.Equal 
joinFilter.valueItem = cl.no 
join.joinExp.Add(joinFilter) 
query.joinEntities.Add(join) 

Dim result = SDK.Query.getEntityData(query) 
return result



Resultado em SQL

Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL] 
inner join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no]

 

Select com 1 inner Join e um And no mesmo Join


Código em VB.NET

Dim query = New QueryVO() 
query.entityName = "CL" 
query.SelectItems.add("cl.nome") 
query.SelectItems.add("cl.no") 
query.SelectItems.add("cl.esaldo") 
query.SelectItems.add("ft.nmdoc") 
query.SelectItems.add("ft.fdata") 
query.SelectItems.add("ft.etotal") 

Dim join = New JoinEntity() 'Create the Join object 
join.TableName = "FT" 

Dim joinFilter = New FilterItem() 'Create the Join object filter 
joinFilter.filterItem = ft.no 
joinFilter.comparison = Comparison.Equal 
joinFilter.valueItem = cl.no 
join.joinExp.Add(joinFilter)

Dim joinFilter2 = New FilterItem() 'Create the Join object filter 
joinFilter2.filterItem = ft.estab 
joinFilter2.comparison = Comparison.Equal 
joinFilter2.valueItem = cl.estab 
join.joinExp.Add(joinFilter2) 
query.joinEntities.Add(join) 

Dim result = SDK.Query.getEntityData(query) 
return result


Resultado em SQL

Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL] 
inner join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no] and [Ft].[estab]=[Cl].[estab]

 

Select com 2 inner Join


Código em VB.NET

Dim query = New QueryVO() 
query.entityName = "CL" 
query.SelectItems.add("cl.nome") 
query.SelectItems.add("cl.no") 
query.SelectItems.add("cl.esaldo") 
query.SelectItems.add("ft.nmdoc") 
query.SelectItems.add("ft.fdata") 
query.SelectItems.add("ft.etotal") 

Dim join = New JoinEntity() 'Create the Join object 
join.TableName = "FT" 
Dim joinFilter = New FilterItem() 'Create the Join object filter 
joinFilter.filterItem = ft.no 
joinFilter.comparison = Comparison.Equal 
joinFilter.valueItem = cl.no 
join.joinExp.Add(joinFilter) 

Dim joinFilter2 = New FilterItem() 'Create the Join object filter 
joinFilter2.filterItem = ft.estab 
joinFilter2.comparison = Comparison.Equal 
joinFilter2.valueItem = cl.estab 
join.joinExp.Add(joinFilter2) 
query.joinEntities.Add(join) 

Dim join2 = New JoinEntity() 'Create the Join object 
join2.TableName = "CC" 
Dim joinFilter3 = New FilterItem() 'Create the Join object filter 
joinFilter3.filterItem = cc.ftstamp 
joinFilter3.comparison = Comparison.Equal 
joinFilter3.valueItem = "<#var>ft.ftstamp" 

join2.joinExp.Add(joinFilter3) 

query.joinEntities.Add(join2) 

Dim result = SDK.Query.getEntityData(query) 
return result


Resultado em SQL

Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL] 
inner join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no] and [Ft].[estab]=[Cl].[estab] 
inner join phcgokit.dbo.Cc on [Cc].[ftstamp]=ft.ftstamp

 

Select com 1 left Join


Código em VB.NET

Dim query = New QueryVO() 
query.entityName = "CL" 
query.SelectItems.add("cl.nome") 
query.SelectItems.add("cl.no") 
query.SelectItems.add("cl.esaldo") 
query.SelectItems.add("ft.nmdoc") 
query.SelectItems.add("ft.fdata") 
query.SelectItems.add("ft.etotal") 

Dim join = New JoinEntity() 'Create the Join object 
Dim joinFilter = New FilterItem() 'Create the Join object filter 
join.TableName = "FT" 
join.joinType = joinsType.Left 
joinFilter.filterItem = ft.no 
joinFilter.comparison = Comparison.Equal 
joinFilter.valueItem = cl.no 
join.joinExp.Add(joinFilter) 

Dim joinFilter2 = New FilterItem() 'Create the Join object filter 
joinFilter2.filterItem = ft.estab 
joinFilter2.comparison = Comparison.Equal 
joinFilter2.valueItem = cl.estab join.joinExp.Add(joinFilter2) 

query.joinEntities.Add(join) 

Dim result = SDK.Query.getEntityData(query) 
return result


Resultado em SQL

Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL] 
left join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no] and [Ft].[estab]=[Cl].[estab]

 

Select com 1 right Join


Código em VB.NET

Dim query = New queryVO() 
query.entityName = "CL" 
query.SelectItems.add("cl.nome") 
query.SelectItems.add("cl.no") 
query.SelectItems.add("cl.esaldo") 
query.SelectItems.add("ft.nmdoc") 
query.SelectItems.add("ft.fdata") 
query.SelectItems.add("ft.etotal") 

Dim join = New JoinEntity() 'Create the Join object 
Dim joinFilter = New FilterItem() 'Create the Join object 
filter join.TableName = "FT" 
join.joinType = joinsType.Right 
joinFilter.filterItem = ft.no 
joinFilter.comparison = Comparison.Equal 
joinFilter.valueItem = cl.no join.joinExp.Add(joinFilter) 

Dim joinFilter2 = New FilterItem() 'Create the Join object 
filter joinFilter2.filterItem = ft.estab 
joinFilter2.comparison = Comparison.Equal 
joinFilter2.valueItem = cl.estab join.joinExp.Add(joinFilter2) 

query.joinEntities.Add(join) 

Dim result = SDK.query.getEntityData(query) 
return result


Resultado em SQL

Select cl.nome,cl.no,cl.esaldo,ft.nmdoc,ft.fdata,ft.etotal from [phcgokit].[dbo].[CL] 
right join phcgokit.dbo.Ft on [Ft].[no]=[Cl].[no] and [Ft].[estab]=[Cl].[estab]

 

Select com 1 Order by


Código em VB.NET

Dim query As QueryVO = New QueryVO() 
query.entityName = "CL" 
query.SelectItems.add("cl.nome") 
query.SelectItems.add("cl.no") 
query.SelectItems.add("cl.esaldo") 

Dim orderCL = new OrderByItem 
orderCL.OrderItem = "cl.esaldo" 
orderCL.OrderType = OrderTypes.Descending 
query.orderByItems.add(orderCL) 

Dim result = SDK.query.getEntityData(query)
return result


Resultado em SQL

Select cl.nome,cl.no,cl.esaldo from [phcgokit].[dbo].[CL] 
order by cl.esaldo desc

 

Select com 2 Order by


Código em VB.NET

Dim query As QueryVO = New QueryVO() 
query.entityName = "CL" 
query.SelectItems.add("cl.nome") 
query.SelectItems.add("cl.no") 
query.SelectItems.add("cl.esaldo") 

Dim orderCL = new OrderByItem 
orderCL.OrderItem = "cl.esaldo" 
orderCL.OrderType = OrderTypes.Descending 
query.orderByItems.add(orderCL) 

Dim orderCL2 = new OrderByItem 
orderCL2.OrderItem = "cl.nome" 
orderCL2.OrderType = OrderTypes.Ascending 
query.orderByItems.add(orderCL2) 

Dim result = SDK.Query.getEntityData(query) 
return result


Resultado em SQL

Select cl.nome,cl.no,cl.esaldo from [phcgokit].[dbo].[CL] 
order by cl.esaldo desc,cl.nome asc

 

Select com Top 10


Código em VB.NET

Dim query As QueryVO = New QueryVO() 
query.entityName = "CL" query.SelectItems.add("*") 
query.limit = 10

Dim result = SDK.Query.getEntityData(query) 
return result


Resultado em SQL

Select top 10 * from [phcgokit].[dbo].[CL]

 

Select com Distinct


Código em VB.NET

Dim query As QueryVO = New QueryVO()
query.entityName = "CL"
query.SelectItems.add("cl.nome")

query.distinct = true

Dim result = SDK.Query.getEntityData(query)
return result


Resultado em SQL

Select distinct cl.nome from [phcgokit].[dbo].[CL]

 

Resultado


Todos os exemplos permitem executar queries à base de dados, desde queries simples, a queries com condições where, joins e orders.