Procedure para templates de clientes

procedure_templates.sql

-- ***************************************************************************** 
-- PLATAFORMAIC-READER
-- Insercao de templates para formularios e feedbacks
-- *****************************************************************************

DECLARE

  type ClienteArray is varray(1) of NUMBER(10,0);
  type TemplateArray is varray(8) of VARCHAR2(100);
  clientes ClienteArray;
  templates TemplateArray;
  
  cdTemplateAreaCabecalho       NUMBER(10,0);
  cdTemplateAreaRodape          NUMBER(10,0);
  cdTemplateCliente             NUMBER(10,0);
  cdTemplateAreaTemplateCliente NUMBER(10,0);
  nomeCliente                   VARCHAR2(50);
  
BEGIN

  -- TEMPLATES
  templates := TemplateArray('DADOS_PESSOAIS_FORM','DADOS_PESSOAIS_RESULT',
                'INDIQUE_AMIGOS_FORM','INDIQUE_AMIGOS_RESULT', 'INDIQUE_AMIGOS_EMAIL',
                'EMAIL_BOAS_VINDAS_FORM','EMAIL_BOAS_VINDAS_RESULT',
                'EMAIL_BOAS_VINDAS_MENSAGEM');
  
  -- CLIENTES
  clientes := ClienteArray(41); -- Alterar de acordo com o cliente a ser migrado
      
  -- ***************************************************************************  
  FOR idx in 1 .. clientes.count
  LOOP
  
    -- CLIENTE
    SELECT c.NOME INTO nomeCliente FROM CLIENTE c WHERE idcliente = clientes(idx);
  
    -- CABECALHO
    SELECT SEQ_TEMPLATEAREA.NEXTVAL INTO cdTemplateAreaCabecalho FROM DUAL;
    INSERT INTO TEMPLATEAREA (IDTEMPLATEAREA, NOME, DESCRICAO, BATIVO, 
                              IDUSUARIOCRIACAO, DHCRIACAO, IDUSUARIOALTERACAO, 
                              DHALTERACAO, TEMPLATEAREA)
           VALUES (cdTemplateAreaCabecalho, 'CABECALHO', 
                   'Cabecalho do cliente ' || nomeCliente, 'S', 
                    1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'), 
                    1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'), '');   
    COMMIT;
    
    -- RODAPE
    SELECT SEQ_TEMPLATEAREA.NEXTVAL INTO cdTemplateAreaRodape FROM DUAL;
    INSERT INTO TEMPLATEAREA (IDTEMPLATEAREA, NOME, DESCRICAO, BATIVO, 
                              IDUSUARIOCRIACAO, DHCRIACAO, IDUSUARIOALTERACAO, 
                              DHALTERACAO, TEMPLATEAREA)
           VALUES (cdTemplateAreaRodape, 'RODAPE', 
                   'Rodape do cliente ' || nomeCliente, 'S', 
                    1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'),
                    1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'), '');   
    COMMIT; 
    
    -- *************************************************************************
    FOR idxx in 1 .. templates.count
    LOOP
    
      -- TEMPLATE CLIENTE
      SELECT SEQ_TEMPLATECLIENTE.NEXTVAL INTO cdTemplateCliente FROM DUAL;
      INSERT INTO TEMPLATECLIENTE (IDTEMPLATECLIENTE, IDCLIENTE, 
                                   IDTEMPLATECLIENTE_TEMPLATEAREA, NOME,
                                   DESCRICAO, DEFAULT_SUBJECT, BATIVO,
                                   IDUSUARIOCRIACAO, DHCRIACAO,
                                   IDUSUARIOALTERACAO, DHALTERACAO, TEMPLATE,
                                   FROM_NOME, FROM_EMAIL, TPTEMPLATE)
             VALUES (cdTemplateCliente, clientes(idx), null, templates(idxx),
                     'Template ' || templates(idxx) || ' para cliente ' || nomeCliente, 
                     null, 'S', 1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'),   
                      1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'), '', null, null, 2);    
      COMMIT;
      
      -- TEMPLATECLIENTE_TEMPLATEAREA => CABECALHO
      SELECT SEQ_TEMPLATECLIENTE_TEMPLATEAR.NEXTVAL INTO cdTemplateAreaTemplateCliente FROM DUAL;
      INSERT INTO TEMPLATECLIENTE_TEMPLATEAREA (IDTEMPLATECLIENTE_TEMPLATEAREA,
                  IDTEMPLATECLIENTE, IDTEMPLATEAREA, NOME, DESCRICAO, BATIVO,
                  IDUSUARIOCRIACAO, DHCRIACAO, IDUSUARIOALTERACAO, DHALTERACAO)
             VALUES (cdTemplateAreaTemplateCliente, cdTemplateCliente, cdTemplateAreaCabecalho,
                    'Cabecalho para template ' || templates(idxx) || ' de cliente ' || nomeCliente,
                    'Cabecalho para template ' || templates(idxx) || ' de cliente ' || nomeCliente, 'S',
                     1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'),   
                     1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'));   
      COMMIT;
      
      -- TEMPLATECLIENTE_TEMPLATEAREA => RODAPE
      SELECT SEQ_TEMPLATECLIENTE_TEMPLATEAR.NEXTVAL INTO cdTemplateAreaTemplateCliente FROM DUAL;
      INSERT INTO TEMPLATECLIENTE_TEMPLATEAREA (IDTEMPLATECLIENTE_TEMPLATEAREA,
                  IDTEMPLATECLIENTE, IDTEMPLATEAREA, NOME, DESCRICAO, BATIVO,
                  IDUSUARIOCRIACAO, DHCRIACAO, IDUSUARIOALTERACAO, DHALTERACAO)
             VALUES (cdTemplateAreaTemplateCliente, cdTemplateCliente, cdTemplateAreaRodape,
                    'Rodape para template ' || templates(idxx) || ' de cliente ' || nomeCliente,
                    'Rodape para template ' || templates(idxx) || ' de cliente ' || nomeCliente, 'S',
                     1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'),   
                     1, TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS'));   
      COMMIT;
    
    END LOOP;
    -- *************************************************************************
  
  END LOOP;
  -- ***************************************************************************
    
END;

-- *****************************************************************************
-- ATUALIZACAO DE SEQUENCE
alter sequence SEQ_TEMPLATECLIENTE_TEMPLATEAR increment by 13;
select SEQ_TEMPLATECLIENTE_TEMPLATEAR.nextval from dual;
alter sequence SEQ_TEMPLATECLIENTE_TEMPLATEAR increment by 1;

alter sequence SEQ_TEMPLATEAREA increment by 2;
select SEQ_TEMPLATEAREA.nextval from dual;
alter sequence SEQ_TEMPLATEAREA increment by 1;

alter sequence SEQ_TEMPLATECLIENTE increment by 169;
select SEQ_TEMPLATECLIENTE.nextval from dual;
alter sequence SEQ_TEMPLATECLIENTE increment by 1;

-- *****************************************************************************
-- ATUALIZACAO DO CONTEUDO DO CABECALHO E RODAPE

-- Cliente: KNOWTEC
-- CABECALHO
UPDATE TEMPLATEAREA t
   SET t.TEMPLATEAREA = '<img width="600" height="99" src="http://si.knowtec.com/GLOBAL_SI/PAINEL_KNOWTEC/imagens/novo_topo_knowtec.jpg" align="top" />' 
 WHERE t.IDTEMPLATEAREA = 98; -- Identificador do TA do cabecalho

-- Cliente: KNOWTEC
-- RODAPE
UPDATE TEMPLATEAREA t
   SET t.TEMPLATEAREA = 
   '<table width="600" cellspacing="0" cellpadding="0" bgcolor="#ffffff" class="rodape">  
        <tbody>  
            <tr>  
                <td width="300" align="center" valign="middle">  
                    <img src="http://si.knowtec.com/GLOBAL_SI/PAINEL_KNOWTEC/imagens/marca_carbonozero.jpg" style="border:0 none">  
                </td>  
                <td width="300" align="center" valign="middle">  
                    <a href="http://www.knowtec.com/"><img src="http://si.knowtec.com/GLOBAL_SI/PAINEL_KNOWTEC/imagens/novo_marca_knowtec.jpg" style="border:0 none"></a>  
                </td>  
            </tr>  
        </tbody>  
    </table>
    <table width="600" cellspacing="0" cellpadding="0"> 
        <tbody> 
            <tr> 
                <td align="center"><a style="font-size:10px" href="$DESCADASTRO_URL$">Clique aqui para não receber mais emails deste cliente</a></td> 
            </tr> 
        </tbody> 
    </table>' 
 WHERE t.IDTEMPLATEAREA = 99; -- Identificador do TA do rodape
 
-- Cliente: IEA
-- CABECALHO
 UPDATE TEMPLATEAREA t
   SET t.TEMPLATEAREA = '<img src="$FILE_PATH_URL$/templates/paineliciea/images/topo.jpg" alt="Painel de Inteligência Competitiva - IEA" width="700" height="98" />' 
 WHERE t.IDTEMPLATEAREA = 109; -- Identificador do TA do cabecalho

-- Cliente: IEA
-- RODAPE
UPDATE TEMPLATEAREA t
   SET t.TEMPLATEAREA = 
   '<table width="600" cellspacing="0" cellpadding="0" bgcolor="#ffffff" class="rodape"> 
        <tbody> 
            <tr> 
                <td width="300" align="center" valign="middle"> 
                    <img src="http://si.knowtec.com/GLOBAL_SI/PAINEL_IEA/imagens/marca_carbonozero.jpg" style="border:0 none"> 
                </td> 
                <td width="300" align="center" valign="middle"> 
                    <a href="http://www.iea.org.br/"><img src="http://si.knowtec.com/GLOBAL_SI/PAINEL_IEA/imagens/marca_iea.jpg" style="border:0 none"></a> 
                </td> 
            </tr> 
        </tbody> 
    </table>
    <table width="600" cellspacing="0" cellpadding="0"> 
        <tbody> 
            <tr> 
                <td align="center"><a style="font-size:10px" href="$DESCADASTRO_URL$">Clique aqui para não receber mais emails deste cliente</a></td> 
            </tr> 
        </tbody> 
    </table>' 
 WHERE t.IDTEMPLATEAREA = 110; -- Identificador do TA do rodape

-- *****************************************************************************
-- INCLUSÃO DE CAMPO em TEMPLATEAREA
ALTER TABLE TEMPLATEAREA ADD TEMPLATEAREA CLOB NULL;
COMMIT;

-- ATUALIZACAO DOS DELIMITADORES [[ => $ e ]] => $

-- TEMPLATE AREA
SELECT ta.templatearea, REPLACE(REPLACE(ta.templatearea, '[[', '$'), ']]', '$') as replaced FROM TEMPLATEAREA ta;
UPDATE TEMPLATEAREA ta SET ta.templatearea = REPLACE(REPLACE(ta.templatearea, '[[', '$'), ']]', '$');
COMMIT;

-- TEMPLATE CLIENTE
SELECT tc.template, REPLACE(REPLACE(tc.template, '[[', '$'), ']]', '$') as replaced FROM TEMPLATECLIENTE tc;
UPDATE TEMPLATECLIENTE tc SET tc.template = REPLACE(REPLACE(tc.template, '[[', '$'), ']]', '$') WHERE IDTEMPLATECLIENTE >= 1638;
COMMIT;