Hello,
I need execute one "complex" query in Django, but I don't know how I can do
that.
This is the query:
select
case
WHEN NetSales_.ORGV = 'OVE2' THEN 'COC'
WHEN NetSales_.ORGV = 'OVE5' THEN 'DOM BOSCO'
WHEN NetSales_.ORGV = 'OVE8' THEN 'ATITUDE'
ELSE 'Outra'
end as 'Marca',
NetSales_.CLIENTE,
Pedidos_.CodigoRecebedor,
NetSales_.CONTACREDITO,
NetSales_.RAZAOSOCIAL,
ClusterizacaoEscolas_.Relacionamento,
Carteira_.GerenteBase,
Carteira_.ConsultorBase,
Sum(NetSales_.QTD * -1) as 'Alunos',
Sum(NetSales_.VALOR_TOTAL) as 'Faturamento'
from NetSales_
inner join Pedidos_
on NetSales_.DOCVENDA = Pedidos_.OrdemVenda
inner join ClusterizacaoEscolas_
on NetSales_.CONTACREDITO = ClusterizacaoEscolas_.ContaCredito
inner join Carteira_
on Pedidos_.CodigoRecebedor = Carteira_.CodigoRecebedor
inner join DeParaMateriais_
on NetSales_.MATERIAL = DeParaMateriais_.Material
where NetSales_.MARCA_CANAL in ('Atitude', 'COC', 'Dom Bosco', 'Clinical',
'Cia das Letras')
and EXERCNEW = '2020'
and DeParaMateriais_.Publico = 'Aluno'
and DeParaMateriais_.Bimestre = '001'
group by
NetSales_.ORGV,
NetSales_.CLIENTE,
Pedidos_.CodigoRecebedor,
NetSales_.CONTACREDITO,
NetSales_.RAZAOSOCIAL,
ClusterizacaoEscolas_.Relacionamento,
Carteira_.GerenteBase,
Carteira_.ConsultorBase
order by 1
This is my models
from django.db import models
# Create your models here.
class ClusterizacaoEscolas(models.Model):
ContaCredito = models.CharField(max_length=10, db_column='ContaCredito'
, primary_key=True) # Field name made lowercase.
Relacionamento = models.CharField(max_length=30, db_column=
'Relacionamento') # Field name made lowercase.
Box = models.CharField(max_length=10, db_column='Box', null=True, blank=
True) # Field name made lowercase.
class Meta:
managed = False
db_table = 'ClusterizacaoEscolas_'
def __str__(self):
return self.ContaCredito
class NetSales(models.Model):
id = models.IntegerField(null=False, primary_key=True)
CLIENTE = models.CharField(max_length=10, null=True, blank=True)
RAZAOSOCIAL = models.CharField(max_length=35, null=True, blank=True)
CONTACREDITO = models.CharField(max_length=10, null=True, blank=True)
CNPJ = models.CharField(max_length=14, null=True, blank=True)
UF = models.CharField(max_length=2, null=True, blank=True)
CEP = models.CharField(max_length=10, null=True, blank=True)
CIDADE = models.CharField(max_length=50, null=True, blank=True)
BAIRRO = models.CharField(max_length=50, null=True, blank=True)
TELEFONE = models.CharField(max_length=20, null=True, blank=True)
PERIODO = models.IntegerField(null=True, blank=True)
EXERCNEW = models.IntegerField(null=True, blank=True)
MATERIAL = models.CharField(max_length=15, null=True, blank=True)
DESCRICAOMATERIAL = models.CharField(max_length=50, null=True, blank=
True)
ORGV = models.CharField(max_length=4, null=True, blank=True)
QTD = models.IntegerField(null=True, blank=True)
VALOR_TOTAL = models.IntegerField(null=True, blank=True)
LASTYEAR = models.IntegerField(null=True, blank=True)
DISCOUNTS = models.IntegerField(null=True, blank=True)
NPV = models.IntegerField(null=True, blank=True)
RETURNS = models.IntegerField(null=True, blank=True)
TAXES = models.IntegerField(null=True, blank=True)
DTLCTO = models.DateField(null=True, blank=True)
CLASSIFICACAO_SKU = models.CharField(max_length=15, null=True, blank=
True)
SEGMENTO_CATEGORIA = models.CharField(max_length=15, null=True, blank=
True)
DENOMINACAO_CONTA = models.CharField(max_length=15, null=True, blank=
True)
TIPOFAT = models.CharField(max_length=15, null=True, blank=True)
CENLUCRO = models.CharField(max_length=15, null=True, blank=True)
DESCRICAOCENTRODELUCRO = models.CharField(max_length=15, null=True,
blank=True)
DOCREF = models.CharField(max_length=15, null=True, blank=True)
DOCVENDA = models.CharField(max_length=15, null=True, blank=True)
CLASSIF_CONT_DAILY_SALES_GPS = models.CharField(max_length=15, null=True
, blank=True)
CLASSIF_CONT_OFICIAL = models.CharField(max_length=15, null=True, blank=
True)
LOB_CANAL = models.CharField(max_length=15, null=True, blank=True)
MARCA_CANAL = models.CharField(max_length=15, null=True, blank=True)
LOB_PRODUTO = models.CharField(max_length=15, null=True, blank=True)
MARCA_PRODUTO = models.CharField(max_length=15, null=True, blank=True)
class Meta:
managed = False
db_table = 'NetSales_'
def __str__(self):
return self.CLIENTE
class Pedidos(models.Model):
Chave = models.CharField(max_length=45, null=False, blank=False,
primary_key=True)
DocFaturamento = models.CharField(max_length=10, null=True, blank=True)
OrdemVenda = models.CharField(max_length=10, null=True, blank=True)
TipoDocVenda = models.CharField(max_length=5, null=True, blank=True)
OrgVenda = models.CharField(max_length=5, null=True, blank=True)
DataPedido = models.DateField(null=True, blank=True)
DataDocumento = models.DateField(null=True, blank=True)
DataRemessa = models.DateField(null=True, blank=True)
DataNF = models.DateField(null=True, blank=True)
CodigoCliente = models.CharField(max_length=10, null=True, blank=True)
NomeCliente = models.CharField(max_length=45, null=True, blank=True)
CNPJCliente = models.CharField(max_length=14, null=True, blank=True)
CodigoRecebedor = models.CharField(max_length=10, null=True, blank=True)
NomeRecebedor = models.CharField(max_length=45, null=True, blank=True)
BairroRecebedor = models.CharField(max_length=45, null=True, blank=True)
CidadeRecebedor = models.CharField(max_length=45, null=True, blank=True)
UF = models.CharField(max_length=2, null=True, blank=True)
CNPJRecebedor = models.CharField(max_length=14, null=True, blank=True)
Item = models.CharField(max_length=8, null=True, blank=True)
Material = models.CharField(max_length=20, null=True, blank=True)
DescricaoMaterial = models.CharField(max_length=60, null=True, blank=
True)
Bimestre = models.CharField(max_length=5, null=True, blank=True)
Ano = models.CharField(max_length=5, null=True, blank=True)
SerieManesoft = models.CharField(max_length=60, null=True, blank=True)
QtdeFaturada = models.IntegerField(null=True, blank=True)
PrecoLiquidoNF = models.DecimalField(decimal_places=2, max_digits=10)
ValorLiquidoNF = models.DecimalField(decimal_places=2, max_digits=10)
ValorDesconto = models.DecimalField(decimal_places=2, max_digits=10)
MotivoOrdemVenda = models.CharField(max_length=60, null=True, blank=True
)
ConfirmacaoPicking = models.CharField(max_length=20, null=True, blank=
True)
StatusPicking = models.CharField(max_length=20, null=True, blank=True)
StatusSaidaMercadoria = models.CharField(max_length=20, null=True, blank
=True)
StatusFaturamento = models.CharField(max_length=20, null=True, blank=
True)
MotivoRecusa = models.CharField(max_length=20, null=True, blank=True)
NumPedido = models.CharField(max_length=20, null=True, blank=True)
class Meta:
managed = False
db_table = 'Pedidos_'
def __str__(self):
return self.OrdemVenda
class Carteira(models.Model):
CodigoRecebedor = models.CharField(max_length=10, null=False, blank=
False, primary_key=True)
Nursery = models.BinaryField()
GerenteBase = models.CharField(max_length=90, null=True, blank=True)
ConsultorBase = models.CharField(max_length=90, null=True, blank=True)
ConsultorAtivacao = models.CharField(max_length=90, null=True, blank=
True)
GerenteNursery = models.CharField(max_length=90, null=True, blank=True)
ConsultorNursery = models.CharField(max_length=90, null=True, blank=True
)
GerentePedagogico = models.CharField(max_length=90, null=True, blank=
True)
CoordenadorPedagogico = models.CharField(max_length=90, null=True, blank
=True)
ConsultorPedagogico = models.CharField(max_length=90, null=True, blank=
True)
class Meta:
managed = False
db_table = 'Carteira_'
def __str__(self):
return self.CodigoRecebedor
class Materiais(models.Model):
Material = models.CharField(max_length=90, null=False, blank=False,
primary_key=True)
DescricaoMaterial = models.CharField(max_length=90, null=True, blank=
True)
Publico = models.CharField(max_length=90, null=True, blank=True)
Frequencia = models.CharField(max_length=90, null=True, blank=True)
Bimestre = models.CharField(max_length=90, null=True, blank=True)
Mentes = models.BinaryField()
Segmento = models.CharField(max_length=90, null=True, blank=True)
class Meta:
managed = False
db_table = 'DeParaMateriais_'
def __str__(self):
return self.Material
--
*Giovanni Silva*
(31) 9 9532-1877
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CABO2r9cfS%2B1TGFNK%2BRS3EkHhYVaR_9es7tjytARYahBKQnhs%3Dg%40mail.gmail.com.
