#items in work space
ls()

# remove all
rm(list=ls())

#intall_packages("knitr")

knitr::opts_chunk$set(echo = TRUE)

knitr::opts_chunk$set(warning = FALSE, message = FALSE)

knitr::opts_knit$set(root.dir = "C:\\0. R\\MBA\\DS&S\\M5 - Fundamentos de SQL")

## Load Packages
load.pks = c(
  "readr",
  "ggplot2",
  "plotly",
#  "e1071",
#  "Hmisc",
#  "DescTools",
#  "nortest",
#  "esquisse",
  "gridExtra",
  "kableExtra",
  "DBI",
  "RMySQL",
  "dplyr"
)

# lapply(load.pks, install.packages, character.only = TRUE) #instalar
lapply(load.pks, require, character.only = TRUE)  #carregar

# drive e conexão
drv <- dbDriver("MySQL")
con <- dbConnect(drv, username="root", password="senha", dbname ="ERP", host='127.0.0.1')

censo2020_inep <- read_csv2('https://raw.githubusercontent.com/RakellM/MBA_DS-S_FundSQL_banco-de-dados-para-analistas-e-cientistas-de-dados/main/sales.z/Censo2020_inep.csv')

write.csv2( censo2020_inep,
            "C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\Censo2020_inep.csv", row.names=FALSE)

# dbWriteTable(con, "erp.censo2000_inep", censo2020_inep, overwrite=TRUE)

-- Quantidade de dependentes
select ("Dependentes") as Total, 
    count(distinct(CdDep)) as Quantidade 
    from erp.tbdependente 
    group by 1;

-- Quantidade de dependentes por sexo
select ("Dependentes") as Total, 
    trim(SxDep) as Sexo, 
    count(distinct(CdDep)) as Quantidade 
    from erp.tbdependente 
    group by 1,2;

-- Quantidade de clientes da região sul
select ("Clientes") as Total,
    ("Sul") as `Região`,
    count(distinct(CdCli)) as Quantidade
    from erp.tbvendas
    where trim(Estado) in ('Paraná', 'Santa Catarina', 'Rio Grande do Sul')
    group by 1;

-- Uma descrição breve dos produtos da empresa (codigo, nome, tipo)
select distinct CdPro as `Código Produto`,
    trim(NmPro) as `Nome Produto`,
    trim(TpPro) as `Tipo Produto`
    from erp.tbvendas;

-- Quais os 5 produtos mais vendidos de 2021?
select CdPro as `Código Produto`,
    trim(NmPro) as `Nome Produto`,
    trim(TpPro) as `Tipo Produto`,
    sum(Qtd) as Quantidade
    from erp.tbvendas
    where year(DtVen) = 2021
    group by 1,2,3
    order by Quantidade desc
    limit 5;

-- Nome, Nome em Maiúsculo e Nome em Minúsculo, dos vendedores do sexo feminino
select trim(NmVdd) as `Nome Vendedor`,
    upper(trim(NmVdd)) as `Nome Vendedor (maiúsculo)`,
    lower(trim(NmVdd)) as `Nome Vendedor (minúsculo)`
    from erp.tbvendedor
    where SxVdd = 0 -- 1=Masc e 0=Fem
    ;

-- Nome e idade de todos os dependentes, ordenados do mais velho para o mais novo
select trim(NmDep) as `Nome Dependente`,
    floor( datediff( date(localtimestamp()) , date(DtNasc) ) / 365 ) as Idade
    from erp.tbdependente 
    order by Idade desc;

-- Somatório do Valor Total de Vendas (concluídas e não deletadas) por Estado
select trim(Estado) as `Estado`,
--    status,
--    deletado,
--    sum(Qtd) as Quantidade,
    sum(Qtd * VrUnt) as `Valor Vendido`
    from erp.tbvendas
    where status like "%Concluído%" and deletado = 0  -- variáveis binarias 0 é falso e 1 é verdadeiro
    group by 1 -- ,2,3
    order by `Valor Vendido` desc;

-- Somatório de Unidades Vendidas (concluídas e não deletadas) por Produto
select trim(NmPro) as Produto,
--    status,
--    deletado,
    sum(Qtd) as Quantidade
--    sum(Qtd * VrUnt) as `Valor Vendido`
    from erp.tbvendas
    where status like "%Concluído%" and deletado = 0 -- variáveis binarias 0 é falso e 1 é verdadeiro
    group by 1 -- ,2,3
    order by Quantidade desc;

-- Média do Valor Total de Vendas por Estado
select trim(Estado) as Estado,
--    status,
--    deletado,
--    sum(Qtd) as Quantidade,
    round(sum(Qtd * VrUnt), 2) as `Valor vendido`,
    count(*) as n,
    round(avg(Qtd * VrUnt), 2) as `Valor Médio`
    from erp.tbvendas
--    where status like "%Concluído%" and deletado = 0  -- variáveis binarias 0 é falso e 1 é verdadeiro
    group by 1 -- ,2,3
    order by `Valor Médio` desc;

-- Nome dos clientes que compram o produto 1
select distinct 
    CdPro as `Código Produto`,
    trim(NmPro) as `Nome Produto`,
    trim(NmCli) as `Nome Cliente`
    from erp.tbvendas
    where CdPro = 1 ;

-- Quantidade mínima e qual o respectivo produto
select 
      CdPro as `Código Produto`,
    trim(NmPro) as `Nome Produto`,
    min(Qtd) as `Quantidade Mínima`
    from erp.tbvendas
    group by 1,2
    order by `Quantidade Mínima`
    limit 1;

-- Uma descrição detalhada dos produtos da empresa (codigo, nome, tipo, Qtd em Estoque)
select
      t2.CdPro as `Código Produto`,
    trim(t1.NmPro) as `Nome Produto`,
    t1.TpPro as `Tipo Produto`,
    t1.Und as `Unidade de Medida`, 
    t2.QtdPro_sum as `Estoque Quantidade`
    from (
        select distinct CdPro, NmPro, TpPro, Und 
        from erp.tbvendas 
        ) t1
    right join (
        select CdPro, sum(QtdPro) as QtdPro_sum 
        from erp.tbestoqueproduto 
        group by 1
        ) t2
    on t1.CdPro = t2.CdPro 
    order by `Estoque Quantidade`;

-- Nome dos Vendedores que realizaram determinadas Vendas (Codigo da Venda, Data da Venda, Produto e nome do vendedor)
select
      t1.CdVen as `Código de Vendas`,
    date(t1.DtVen) as `Data da Venda`,
    t1.NmPro as `Produto Nome`,
    trim(t2.NmVdd) as `Vendendor Nome`
from (
    select
        CdVen, 
        DtVen,
        trim(NmPro) as NmPro,
        CdVdd,
        sum(Qtd) as Qtd
    from erp.tbvendas
    group by 1,2,3,4 ) t1
left join erp.tbvendedor t2
on t1.CdVdd = t2.CdVdd 
order by `Data da Venda`;

drop view if exists erp.vwDependentes ;

-- Relação com o nome dos vendedores e seus respectivos filhos (dependentes - nome e data de nascimento) -- montar uma view com estes dados
create view erp.vwDependentes as
select
      t1.NmVdd as `Vendedor Nome`,
    t2.NmDep as `Dependente Nome`,
    date(t2.DtNasc) as `Data de Nascimento`
from erp.tbvendedor t1
left join erp.tbdependente t2
on t1.CdVdd = t2.CdVdd
order by t1.CdVdd;

select * from erp.vwDependentes ;

drop view if exists erp.vwVendas ;

-- Criar uma view com informações de vendas, produto, estoque, cliente, vendedores (apenas concluídas e não deletadas)
create view erp.vwVendas as
select 
      a.CdVen as `Código da Venda`,
      date(a.DtVen) as `Data da Venda`,
    a.CdCli as `Código do Cliente`,
    trim(a.NmCli) as `Nome do Cliente`,
    a.CdVdd as `Código do Vendedor`,
    trim(b.NmVdd) as `Vendedor Nome`,
      case
            when b.SxVdd = 1 then "Masculino"
        when b.SxVdd = 0 then "Feminino"
        else NULL
      end as `Vendedor Sexo`,
      b.PercComissao as `Percentual Comissão do Vendedor`,
    trim(a.Cidade) as `Cidade`,
    trim(a.Estado) as `Estado`,
    trim(a.Pais) as `País`,
      a.CdPro as `Código do Produto`,
    trim(a.NmPro) as `Nome do Produto`,
    trim(a.TpPro) as `Tipo do Produto`,
    trim(a.Und) as `Unidade de Medida`,
    a.VrUnt as `Valor Unitário`,
    a.Qtd as `Quantidade Vendida`, 
    (a.Qtd * a.VrUnt) as `Valor vendido`,
    a.QtdPro_sum as `Quantidade em Estoque`
from (
    select
        t1.*,
        t2.QtdPro_sum
        from (
            select *
            from erp.tbvendas
            where status like "%Concluído%" and deletado = 0  -- variáveis binarias 0 é falso e 1 é verdadeiro
            ) t1
        left join (
            select CdPro, sum(QtdPro) as QtdPro_sum 
            from erp.tbestoqueproduto 
            group by 1
            ) t2
        on t1.CdPro = t2.CdPro 
    
    ) a
left join erp.tbvendedor b
on a.CdVdd = b.CdVdd ;

select * from erp.vwVendas ;

drop view if exists erp.vwVendasPorCanal ;

-- View de quantidade de vendas por canal
create view erp.vwVendasPorCanal as
select 
    CdCanalVendas as `Códido Canal`,
    trim(NmCanalVendas) as `Canal de Vendas`,
    count(distinct(CdVen)) as `Quantidade Vendas`
from erp.tbvendas
group by 1,2;

select * from erp.vwVendasPorCanal ;

-- Filtrar a tabela de vendedores pelo vendedor de nome: Vendedor 6
select 
    CdVdd as `Vendedor Código`,
    NmVdd as `Vendedor Nome`,
    case
        when SxVdd = 1 then 'Masculino'
        when SxVdd = 0 then 'Feminino'
        else NULL
    end as `Vendedor Sexo`,
    PercComissao as `Vendedor Percentual Comissão`
from erp.tbvendedor
where trim(NmVdd) like "% 6" ;

--  Uma consulta que retorne o nome dos dependentes, mas quando for o 
-- dependente de código 5, retorne o seu nome. (Usando IF ou CASE)
select
    CdDep as `Dependente Código`,
    case
        when CdDep = 5 then 'Raquel'
        else NmDep
    end as `Dependente Nome`,
    date(DtNasc) as `Data de Nascimento`,
    SxDep as `Dependente Sexo`,
    CdVdd as `Vendedor Código`,
    InepEscola
from erp.tbdependente;

--  Retornar todas as vendas entre os dias 07/05/2019 a 03/03/2021 unidas 
-- com as todas as vendas entre os dias 11/09/2011 a 03/09/2012
select * 
from erp.tbvendas
where date(DtVen) between '2019-05-07' and '2021-03-03'
union
select * 
from erp.tbvendas
where date(DtVen) between '2011-09-11' and '2012-09-03' ;

select  
    CdVen as `Código de Vendas`,
    date(DtVen) as `Data da Venda`,
    CdCli as `Cliente Código`,
    trim(NmCli) as `Cliente Nome`, 
    trim(Cidade) as `Cidade`,
    trim(Estado) as `Estado`,
    trim(Pais) as `País`, 
    CdPro as `Produto Código`,
    trim(NmPro) as `Produto Nome`,
    trim(TpPro) as `Produto Tipo`,
    Qtd as `Quantidade Vendida`,
    trim(Und) as `Unidade de Medida`,
    VrUnt as `Valor Unitário`,
    CdVdd as `Vendedor Código`,
    trim(NmCanalVendas) as `Canal de Vendas`,
    status,
    deletado 
from erp.tbvendas
where (date(DtVen) between '2019-05-07' and '2021-03-03') or (date(DtVen) between '2011-09-11' and '2012-09-03') ;

--  Retornar o nome do produto (apenas os 5 primeiros caracteres) e a quantidade 
-- de venda com 10 dígitos, completando com zeros a esquerda.
select 
    CdPro as `Produto Código`,
      trim(NmPro) as `Produto Nome`,
    left(trim(NmPro),5) as `Primeiras 5 letras do Produto Nome`,
    lpad(Qtd,10,0) as `LPad Quantidade Vendida` -- ,
--  Qtd as `Quantidade Vendida`    
from erp.tbvendas
;

--  Retornar o nome do produto (apenas os 5 primeiros caracteres) e a quantidade 
-- de venda com 10 dígitos, completando com zeros a esquerda.
select 
--  CdPro as `Produto Código`,
--  trim(NmPro) as `Produto Nome`,
    left(trim(NmPro),5) as `Primeiras 5 letras do Produto Nome`,
    lpad(Qtd,10,0) as `LPad Quantidade Vendida` -- ,
--  Qtd as `Quantidade Vendida`    
from erp.tbvendas
;

-- Qual o produto que tem a maior quantidade de vendas no canal: Ecommerce?
select 
--  CdCanalVendas, 
    trim(NmCanalVendas) as `Canal de Vendas`,
    NmPro as `Produto Nome`,
    sum(Qtd) as `Quantidade Vendas`
from erp.tbvendas
where CdCanalVendas=2
group by 1,2 -- ,3
order by sum(Qtd) desc 
limit 1;

-- Existiram vendas para produtos em MVP - validação? Quais foram?
select 
      t1.CdVen as `Código de Vendas`,
    date(t1.DtVen) as `Data da Venda`,
    t1.CdCli as `Cliente Código`,
    trim(t1.NmCli) as `Cliente Nome`, 
    trim(t1.Cidade) as `Cidade`,
    trim(t1.Estado) as `Estado`,
    trim(t1.Pais) as `País`, 
    t1.CdPro as `Produto Código`,
    trim(t1.NmPro) as `Produto Nome`,
    trim(t1.TpPro) as `Produto Tipo`,
    t1.Qtd as `Quantidade Vendida`,
    trim(t1.Und) as `Unidade de Medida`,
    t1.VrUnt as `Valor Unitário`,
    t1.CdVdd as `Vendedor Código`,
    trim(t1.NmCanalVendas) as `Canal de Vendas`,
    trim(t1.status),
    t1.deletado
from erp.tbvendas t1
inner join (
    select distinct CdPro from erp.tbestoqueproduto where trim(Status) = 'MVP - validação'
    )t2
on t1.CdPro = t2.CdPro ;

-- Quantas vendas encontram-se deletadas logicamente?
select
    case
        when deletado = 1 then 'Sim'
    when deletado = 0 then 'Não'
    end as `Deletado Logicamente`,
  count(distinct(CdVen)) as `Quantidade de Vendas`
from erp.tbVendas
group by 1 
order by `Deletado Logicamente` desc
limit 1;

-- Quantas vendas encontram-se canceladas?
select
      trim(status) as `Status da Venda`,
    count(distinct(CdVen)) as `Quantidade de Vendas`
from erp.tbVendas
where status like "%Cancelado%"
group by 1 ;

drop table if exists erp.censo2020_INEP ;

create table erp.censo2020_INEP ( 
    restricao_atendimento TEXT, 
    Escola TEXT, 
    CdINEP INT, 
    UF TEXT, 
    Municipio TEXT, 
    Localizacao TEXT, 
    CatAdm TEXT, 
    Porte TEXT 
) ;

load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\Censo2020_inep.csv' 
    into table erp.censo2020_INEP 
    fields terminated by ';' 
    enclosed by '"' 
    lines terminated by '\n' 
    ignore 1 rows; 

select 
  t1.CdDep as `Dependente Código`,
  trim(t1.NmDep) as `Dependente Nome`,
  date(t1.DtNasc) as `Data de Nascimento`,
  trim(t1.SxDep) as `Dependente Sexo`,
  t1.CdVdd as `Vendedor Código`,
  t1.INEPEscola as `Escola Código INEP`,
  trim(t2.Escola) as `Escola Nome`
from erp.tbDependente t1
left join erp.censo2020_INEP t2
on t1.INEPEscola = t2.CdINEP;