404

D7net
:/ var / www / html / crsys_ant /
Filename : novas_tabelas.sql
back
-- funcionários
use crsys;
set sql_safe_updates = 0;
truncate table crsys.gre_funcionarios;
insert into crsys.gre_funcionarios (SELECT codigo,
	  empregador,
       case when empregador = 'M BLANC COMÉRCIO DE PEDRA EIRELI – EPP' then 1
           when empregador = 'MB comércio de pedras e materiais p/ construção Ltda' then 2 
           when empregador = 'Mont Blanc comércio de pedras Ltda' then 1
           when empregador = 'SR Marmoraria' then 1
           else
             1
             
             end empregador,
 
	  nome,
      case when sexo = 'M' then 1
           when sexo = 'F' then 2
      else
           null
      end
      sexo_id,
      admissao,
      demissao,
      rg,
      REGEXP_REPLACE(cpf, '[^0-9]', '') cpf,
      ctps,
      pis,
      reservista,
      t_eleitor,
      DataNascimento,
      case when estadocivil = 'Casado' then 1
           when estadocivil = 'Casada' then 1 
           when estadocivil = 'Divorciada' then 2
           when estadocivil = 'Divorciado' then 2 
           when estadocivil = 'Divorcido' then 2 
           when estadocivil = 'Solteira' then 3
           when estadocivil = 'Solteiro' then 3
           when estadocivil = 'Soltero' then 3
           when estadocivil = 'Soltetiro' then 3
           when estadocivil = 'Soteiro' then 3
           when estadocivil = 'Souteiro' then 3
           when estadocivil = 'amasiado' then 4
           when estadocivil = 'União Estável' then 4
		   when estadocivil = 'Viúva' then 5
		   when estadocivil = 'Viuvo' then 5 
           when estadocivil = 'Outro' then 6 
           when estadocivil =  'Outros' then 6 
           else
            null
            end
            estado_civil,
            conjuge,
            filhomenores,
            filhomaiores,
            Naturalidade,
            null,
      case when funcao LIKE '%Vendedora%' then REPLACE(funcao, 'Vendedora', 'Vendedor')
           when funcao LIKE '%Medidora%' then REPLACE(funcao, 'Medidora', 'Medidor')
      else     
      funcao
      end funcao,
      null,
       REGEXP_REPLACE(cep, '[^0-9]', '') cep,
        REGEXP_REPLACE(telres, '[^0-9]', '') fone_fixo,
        REGEXP_REPLACE(celular, '[^0-9]', '') celular,
        endereco,
        numero,
        bairro,
        complemento,
        cidade,
        null,
        round((salario/100),2),
        encarregado,
        null,
        Alimentacao,
        null,
        vt,
        detalhevt,
        null,
        indicacao,
        null,
        DadosBancario,
        1,
        case when departamento = 'Admin' then 11
           when departamento = 'Apoio' then 11
           when departamento = 'Administrativo' then 11
           when departamento = 'Colocação' then 6
           when departamento = 'Compras' then 7
           when departamento = 'Controle de Acesso'  then 11
           when departamento = 'Cozinha' then 8
           when departamento = 'Diretoria' then 1
           when departamento = 'Expedição' then 9
           when departamento = 'Financeiro' then 10
           when departamento = 'Limpeza' then 12
           when departamento = 'Produção' then 5
           when departamento = 'Projetos' then 4
           when departamento = 'Recepção' then 11
		   when departamento = 'RH' then 10
		   when departamento = 'Vendas' then 2         
      else
           null
      end
        area_id,
          case when Status = 'Ativo' then 1
           when Status = 'Inativo' then 2
      else
           null
      end
      gre_sit_id,
      now(),
      1, 
      null,
      null,
      email,
      null,
      null,
      ltrim(obs)
FROM dados_mont_blanc.tbllogin
where tipo = 'Funcionario');
-- após comandos acima rodar o http://localhost/crsys/checa_cpf_func.php  para detectar cpf errado
-- resultado Validação concluída. # id, nome, datainicio, demissão, cpf_errado '2', 'Arivaldo Soares da Silva', '2013-05-06', NULL, '208.127.235-72'

-- Beneficiamento
truncate crsys.gre_beneficiamentos;
 insert into crsys.gre_beneficiamentos 
  SELECT codigo, beneficiamento,(valor/100),1,1,1,now(),1,null,null FROM dados_mont_blanc.tblbeneficiamento;
-- 2. criar tabela funcoes
UPDATE gre_funcionarios
SET funcao = REPLACE(funcao, 'Aj.', 'Ajudante')
WHERE funcao LIKE '%Aj.%';
truncate table gre_funcao;
insert into gre_funcao (select 0, Case when funcao LIKE '%Vendedora%' then REPLACE(funcao, 'Vendedora', 'Vendedor')
										   when funcao LIKE '%Medidora%' then REPLACE(funcao, 'Medidora', 'Medidor')
									  else     
									  funcao
									  end  
									  funcao,  
									  null, 1, now(), 1, null, null, 1 from gre_funcionarios 
where funcao <> ''
group by funcao order by funcao);
UPDATE gre_funcionarios f
JOIN gre_funcao fu ON f.funcao = f.nome
SET f.funcao_id = fu.id;
select datad from gre_funcionarios where datad >= '2025-08-05';
-- ajusta municipio no gre_funcionarios e datad
set sql_safe_updates = 0;
update gre_funcionarios set datad = null where datad >= curdate();
update gre_funcionarios set gre_sit_id = 1 where datad is null;
update gre_funcionarios f 
inner join adms_municipios mu
on trim(mu.nome) = trim(f.municipio)
set f.municipio_id = mu.id;
-- criar o funcao_id no gre_funcionarios
UPDATE gre_funcionarios f
inner JOIN gre_funcao g 
ON trim(f.funcao) = trim(g.nome)
SET f.funcao_id = g.id;
select f.id, f.nome, f.funcao, f.funcao_id, fu.nome
from gre_funcionarios f
inner join gre_funcao fu
on fu.id = f.funcao_id;
DROP TABLE IF EXISTS gre_alimentacao; 
create table gre_alimentacao(
id int auto_increment,
nome varchar(30) default null,
created datetime default null,
usu_created int default null,
modified datetime default null,
usu_modified int default null,
primary key(id)
); 
-- completa tabela de funcoes
SET @next_id = (SELECT MAX(id) FROM gre_funcao) + 1;
INSERT INTO gre_funcao
(SELECT
    @next_id := @next_id + 1,
    fu.funcao,
    NULL,
    1,
    NOW(),
    1,
    NULL,
    NULL,
    1
FROM gre_funcionarios fu
LEFT JOIN gre_funcao f ON TRIM(fu.funcao) = TRIM(f.nome)
WHERE f.id IS NULL
  AND fu.funcao IS NOT NULL
  AND TRIM(fu.funcao) <> ''
GROUP BY fu.funcao);
-- corrigir area_id do gre_funcao
INSERT INTO gre_alimentacao (id, nome, created, usu_created, modified, usu_modified)
SELECT 0, alimento, NOW(), 1, NULL, NULL
FROM gre_funcionarios
where alimento <> '0'
GROUP BY alimento;
-- criar alimentacao_id no gre_funcionarios
UPDATE gre_funcionarios f
JOIN gre_alimentacao g ON f.alimento = g.nome
SET f.alimentacao_id = g.id;
select f.id, f.nome, f.alimento, f.alimentacao_id, fu.nome
from gre_funcionarios f
inner join gre_alimentacao fu
on fu.id = f.alimentacao_id;
-- ajusta demitidos e inativos
update gre_funcionarios set datad = null where datad = '9000-09-09';
update gre_funcionarios set gre_sit_id = 2 where datad is not null and datad > datainicio;
-- ajustar vale transporte
select * from gre_funcionarios WHERE vt LIKE '%por dia%';
update gre_funcionarios set valor_vt = 
  REPLACE(TRIM(SUBSTRING_INDEX(vt, 'por dia', 1)), ',', '.')
 WHERE vt LIKE '%por dia%';
 
 
use dados_mont_blanc;
insert into dados_mont_blanc.trabproduto (
 select p.* from  dados_mont_blanc.trabproduto p
 left join dados_mont_blanc.tblbeneficiamento b
 on p.id_beneficiamento = b.codigo
 where  p.id_beneficiamento in (14,16,17))
 order by p.preco_vendas desc;
 select material, count(*) itens from dados_mont_blanc.trabproduto group by material order by itens desc;
 truncate table crsys.gre_materiais;
insert into crsys.gre_materiais SELECT
    0,
    REPLACE(TRIM(p.material), TRIM(b.beneficiamento), ''),
    1,
    (p.preco_custo / 1000),
    (p.preco_vendas / 1000 ),
    CASE 
        WHEN Moeda = 'Reais' THEN 1
        WHEN Moeda = 'Dólar' THEN 2
        WHEN Moeda = 'Euro' THEN 3
        ELSE 0
    END moeda,
    1, 1, NOW(), 1, NULL, NULL,
    p.codigo
FROM 
    dados_mont_blanc.trabproduto p
INNER JOIN 
    tblbeneficiamento b ON b.codigo = p.id_beneficiamento; 
select * from crsys.gre_materiais where tipo_moeda_id = 0;
select * from tblproduto where codigo =  12998;
set sql_safe_updates = 0;
update crsys.gre_materiais set tipo_moeda_id = 1 where tipo_moeda_id = 0;    

use crsys;
-- acabamentos
truncate table gre_acabamentos;
insert into gre_acabamentos (SELECT 0, MO, preco,  c.id, u.id, 1,1, now(),1,null,null FROM dados_mont_blanc.tblmo 
inner join gre_unidades u
on u.sigla = unid
inner join gre_categorias c
on c.nome = Categoria
where categoria = 'acabamento');
-- Serviços
truncate table gre_servicos;
insert into gre_servicos (SELECT 0, MO, preco,  c.id, u.id, 1,1, now(),1,null,null FROM dados_mont_blanc.tblmo 
inner join gre_unidades u
on u.sigla = unid
inner join gre_categorias c
on c.nome = Categoria
where categoria <> 'acabamento');
-- tabela materiais/beneficiamento
create table gre_mater_benef(
id int auto_increment,
mater_id int default null,
benef_id int default null,
padrao int default 0,
primary key(id));
-- criar index mat_benef; 

drop table trabprodbenef;
CREATE TABLE `trabprodbenef` (
  `Codigo` int NOT NULL AUTO_INCREMENT,
  `Material` varchar(90) DEFAULT NULL,
  `Categoria` varchar(45) DEFAULT 'Rocha',
  `Preco_Vendas` double DEFAULT '0',
  `Preco_custo` double DEFAULT '0',
  `Fornecedor` varchar(60) DEFAULT NULL,
  `Estoque_Minimo` double DEFAULT '0',
  `Unid` varchar(10) DEFAULT NULL,
  `Quant_Estoque` double DEFAULT '0',
  `Ultima_Compra` date DEFAULT '1900-01-01',
  `Ultima_Venda` date DEFAULT '1900-01-01',
  `Criterio_De_Compra` varchar(45) DEFAULT NULL,
  `Data` date DEFAULT '1900-01-01',
  `Ponto_de_Ressuprimento` double DEFAULT '0',
  `Beneficiamento` varchar(45) DEFAULT NULL,
  `OBS` varchar(250) DEFAULT NULL,
  `Moeda` varchar(20) DEFAULT NULL,
  `Usuario` varchar(45) DEFAULT NULL,
  `PrazoDeEntrega` int DEFAULT '0',
  `ConsumoDiario` double DEFAULT '0',
  `Nivel_Dificuldade` double DEFAULT '1000',
  `ID_Origem` int DEFAULT '0',
  `ID_Beneficiamento` int DEFAULT '0',
  PRIMARY KEY (`Codigo`)
);
insert into trabprodbenef (
select p.* from dados_mont_blanc.tblproduto p
where p.codigo not in (select t.codigo from dados_mont_blanc.trabproduto t)
and p.categoria = 'Rocha');
ALTER TABLE `dados_mont_blanc`.`trabprodbenef` 
ADD COLUMN `nome_reduzido` VARCHAR(200) NULL DEFAULT 'null' AFTER `ID_Beneficiamento`;
select t.material, t.nome_reduzido, t.id_beneficiamento, b.nome nome_benef, g.codigo_ant, g.nome from trabprodbenef t
inner join crsys.gre_materiais g
on g.nome = t.nome_reduzido
inner join crsys.gre_beneficiamentos b
on b.id = t.ID_Beneficiamento;

insert into crsys.gre_mater_benef (select 0, g.codigo_ant, t.id_beneficiamento from trabprodbenef t
inner join crsys.gre_materiais g
on g.nome = t.nome_reduzido
inner join crsys.gre_beneficiamentos b
on b.id = t.ID_Beneficiamento);
insert into crsys.gre_mater_benef Select 0, p.codigo_ant, 14 from crsys.gre_materiais p;
select * from crsys.gre_mater_benef;
select
 m.codigo_ant codigo_material, 
 concat(trim(m.nome),' ',b.nome) nome_com_beneficiamento, m.preco_venda, b.preco preco_beneficiamento,
(m.preco_venda + b.preco) preco_com_beneficiamento
 from crsys.gre_materiais m
inner join crsys.gre_mater_benef mmb
on mmb.mater_id = m.codigo_ant
inner join crsys.gre_beneficiamentos b
on mmb.benef_id = b.id
where trim(m.nome) <> ''
and m.nome like '%Granito%' 
order by m.nome, b.ordem;
-- tabela Insumos
truncate table gre_insumos;
insert into gre_insumos (select 0, 
   material,
   case when unid = 'Lata' then 5
       when unid = 'Peça' then 8
       when unid = 'Kg' then 6
       when unid = 'Litro' then 7
       when unid = '0' then 8
       when unid = 'caixa' then 10       
       when unid = 'Saco' then  9     
	else
       null
    end   
   unid,
   0,
   round((preco_custo/100),2) custo,
   round((preco_vendas/100),2) venda,
   1,
   case when categoria = 'Cuba' then  1
        when categoria = 'Insumo' then  2 
        when categoria = 'Ferramenta' then  3
   else 
      null
   end
   cate_id,
   1,
   now(),
   1,
   null,
   null,
   codigo
 from  dados_mont_blanc.tblproduto  where categoria <> "Rocha");
 UPDATE crsys.gre_insumos ins
JOIN dados_mont_blanc.tblproduto p
  ON p.codigo = ins.codigo_ant AND p.categoria <> "Rocha"
JOIN crsys.gre_fornecedores f
  ON f.nome COLLATE utf8mb3_general_ci = p.fornecedor COLLATE utf8mb3_general_ci
SET ins.forn_id = f.id;
 -- tabela Mão de Obra
 truncate table crsys.gre_maodeobra;
insert into crsys.gre_maodeobra (select codigo,
	   mo,
       preco,
       case when categoria = 'Cuba' then 1
            when categoria = 'Serviço' then 4
            when categoria = 'Acabamento' then 5
       else
           null
      end     
      cat_id,
             case when unid = 'ml' then 1
            when unid = 'unid' then 4
            when unid = 'Diaria' then 11
       else
           null
       end    
       unid_id,
      1 empr_id,
      1,
      now(),
      1,
      null,
      null
 from dados_mont_blanc.tblmo);     
 select * from crsys.gre_maodeobra;
 -- tabela fornecedores
 truncate table crsys.gre_fornecedores;
insert into crsys.gre_fornecedores(
SELECT 0,
        REGEXP_REPLACE(cnpj, '[^0-9]', '') cnpj,
        ie insc_estadual,
        null,
       rs nome,
       SUBSTRING_INDEX(rs, ' ', 2)  nome_fantasia,
        
        REGEXP_REPLACE(cep, '[^0-9]', '') cep,
        endereco,
        null numero,
        bairro,
        null complemento,
        contato,
          REGEXP_REPLACE(telcom, '[^0-9]', '') fone,
          email,
          REGEXP_REPLACE(celular, '[^0-9]', '') celular_contato,
          mun.id municio_id,
          case when formapagamento = 'Boleto' then 1
               when formapagamento = 'Cheque' then 3
           else
           null
           end
           gre_forma_id,
           1 gre_tipo_id,
           1 gre_sit_id,
           now(),
           1,
           null,
           N_Fornecedor,
           cidade
FROM dados_mont_blanc.tblfornecedor
left join crsys.adms_municipios mun
on mun.nome = cidade);
 -- ajuste gre_fornecedores
 set sql_safe_updates = 0;
 UPDATE gre_fornecedores
SET nome = UPPER(nome);     
UPDATE crsys.gre_fornecedores
SET celular_contato = CONCAT('9', celular_contato)
WHERE CHAR_LENGTH(celular_contato) = 8
  AND celular_contato > 60000000;
UPDATE crsys.gre_fornecedores
SET celular_contato = CONCAT('11', celular_contato)
WHERE CHAR_LENGTH(celular_contato) = 9
  AND LEFT(celular_contato, 2) <> '11'
  AND celular_contato > 60000000;
-- Ambientes
truncate table crsys.gre_ambientes;
insert into crsys.gre_ambientes (SELECT 0, ambiente,1,1,now(),1,null,null
 FROM dados_mont_blanc.tblambiente);
SELECT * FROM crsys.gre_ambientes;
-- Tabela PRODUTOS
use dados_mont_blanc;
truncate table crsys.gre_produtos;
insert into crsys.gre_produtos (SELECT p.codigo,
       ltrim(p.produto) produto,
       u.id unid_id,
       (p.CustoColocacao/100) CustoColocacao,
       (p.PrecoColocacao/100) precoColocacao,
       grupo,
       1 empr_id,
       1 gre_sit_id,
       now(),
       1,
       null,
       null
       FROM tblprodutosaida p
 inner join crsys.gre_unidades u
 on u.sigla = p.unid);
 -- parceiros (escritorios)
 truncate table crsys.gre_parceiros;
insert into crsys.gre_parceiros (id,nome, empr_id,gre_tipo_id, gre_sit_id, created, usu_created)
(SELECT 0,trim(escritorio),1,1,1,now(), 1 FROM tblarquitetos
where Escritorio <> ''
group by escritorio order by escritorio);
-- indicacao parceiros (arquitetos)
-- verificar RT > 10% e nome igual a numero de telefone
truncate table crsys.gre_indicacao_parceiros;
insert into crsys.gre_indicacao_parceiros (id,cto_empr_id,cto_parc_id,cto_nome,cto_celular, cto_email, cto_rt,cto_sit_id,cto_created, cto_usu_created)
(select 0, 1, p.id,a.contato,
   case when REGEXP_REPLACE(a.telefone, '[^0-9]', '') = '' then
   null
   else
    REGEXP_REPLACE(a.telefone, '[^0-9]', '') 
   end 
    telefone_limpo,
     a.email, 
     round(a.rt/1000,2) rt,
     1, now(), 1
 FROM tblarquitetos a
inner join crsys.gre_parceiros p
on p.nome = trim(a.escritorio)
where a.contato <> '');